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
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?
If you are using Oracle 8i or above,
Alter table table_name move tablespace tablespace_name;
I am using Oracle 8.0.5 only.
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.
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.
What will be the easiest way of changing storage parameters in 8.1.6?
Click Here to Expand Forum to Full Width