Moving a table from one tablespace to another
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Moving a table from one tablespace to another

Hybrid View

  1. #1
    Join Date
    Nov 2001
    Location
    Chennai
    Posts
    22
    While trying to move a table from one tablespace to another, I had to follow these steps.

    ( Table is not going to be copied to another schema, just within the schema, the tablespace is to be changed )


    a. export the table
    b. drop the table from the schema with cascade option
    c. re-create the table in new tablespace along with
    constraints and foreign key constraints in other dependant
    tables.
    d. Using import utility, IGNORE=Y option, reload the table
    data from the dump file.
    e. re-created indexes that were available earlier.

    Performing the above steps doesn't take care of the existing user privileges on the table and how it will be re-created in the newly created table object.

    Is there any other way out to have the privileges also restored in the re-created table?




  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    If you are using Oracle 8i or above,
    Alter table table_name move tablespace tablespace_name;

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Nov 2001
    Location
    Chennai
    Posts
    22

    Unhappy

    I am using Oracle 8.0.5 only.

  4. #4
    Join Date
    Oct 2000
    Posts
    467
    create a role and give all the privileges to the role. Grant thye role to the user. A full or userlevel export will take the roles as well.
    Vinit

  5. #5
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81

    Wink

    Hi

    Try to edit the eport dump file using the text Editor like Notepad/Editpad or Vi (but not WordPad).
    Find and Replace all the occurrances of "old_tablespace" with "new_tablespace" and import it.

    Though you'll have to be carefull else it can render the whole dumpfile useless.

    Regards
    Shruti

  6. #6
    Join Date
    Aug 2000
    Posts
    236
    What will be the easiest way of changing storage parameters in 8.1.6?

    Thanks,
    Nizar

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