Moving a table from one tablespace to another

[sundaju]

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?

[Raminder]

If you are using Oracle 8i or above,
Alter table table_name move tablespace tablespace_name;

[sundaju]

I am using Oracle 8.0.5 only.

[vinit]

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.

[ShrutiM]

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

[nabaig]

What will be the easiest way of changing storage parameters in 8.1.6?

Thanks,
Nizar