change tablespace for a table/index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: change tablespace for a table/index

  1. #1
    Join Date
    Dec 2000
    Posts
    22

    Smile

    Hi,

    For any given table/index, how do I change the
    tablespace in which it resides. We have got into
    a problem wherein some of the tables/objects
    are created in 'SYSTEM' tablespace which we want
    to migrate to another tablespace.

    Thanks for the help.

    - Hari.

  2. #2
    Join Date
    Jan 2001
    Posts
    3
    You can export the table then import to the new schema / tablespace and drop the old table.

    Or you can create a new table in the new tablespace using create table nnnn as select xxxx. And remember to drop the table in the system area when you are done.

    And you will have to deal with the system area fragmentation.

  3. #3
    Join Date
    Apr 2001
    Posts
    112
    You can also use the Alter table <> Move stmt to move the table to a different tablespace.

    Tansdot

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    And you can use:
    alter index xyz rebuild tablespace abc
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    May 2001
    Posts
    41
    Do one thing
    1). Export the database with system/manager user name and passwod While exporting Mention Grant=No then

    2). Drop all tables, Procedures, Functions,Views etc... The Things you can list like this ( select * from user_objects Which are not required in system tablespace...

    3). connect system/manager
    create tablespace user_data datafile 'C:\ORANT\ORADATA\USER_DATA.DBF' SIZE 200M;

    4). Create user identified by default tablespace USER_DATA temporary tablespace temp quota unlimited on USER_DATA;

    5). Import That dump file to this schema. For That user What you have created that userid/password and say Grant=No,
    Ignore=Y


    Enjoy..

    RAMESH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width