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?
11-27-2001, 01:17 AM
If you are using Oracle 8i or above,
Alter table table_name move tablespace tablespace_name;
11-27-2001, 01:22 AM
I am using Oracle 8.0.5 only.
11-27-2001, 01:48 AM
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.
11-27-2001, 02:10 AM
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.
11-27-2001, 05:07 AM
What will be the easiest way of changing storage parameters in 8.1.6?