Odi's astoundingly incomplete notes

Oracle is a great DB. But when it comes to tablespaces its management features suck. There is no easy way to move all objects (tables, indexes, sequences, LOBs etc.) between tablespaces at once for instance. (You need to generate SQL for every object type.) Another big problem is when you move data with export / import between different DB instances. Usually tablespace names are completely different between different DB instances. But Oracle's imp utility does not directly support import into a tablespace of a different name. So you have to do some nasty workaround:

1. Use the imp utility's INDEXFILE= option to create a script.
2. Edit this script: remove the REM from the CREATE TABLE statetments, get rid of the spurious "... 123 rows", replace all TABLESPACE XXX clauses with the appropriate ones, replace all schema names with yours if necessary.
   
3. Run this script to create the empty schema.
4. Disable all constraints.
5. Remove the UNLIMITED_TABLESPACE privilege from the user.
6. Give the user unlimited table space quota on the tablespaces you want.
7. Import the dump with IGNORE=y and FROMUSER and TOUSER options if necessary.
8. Enable all constraints.
9. Revert your tablespace quota settings.

If space allows you can alternatively create a new tablespace with the same name as in the export DB. Then import the dump. Then you can move all objects. To get the necessary statements you can execute:

SELECT 'ALTER TABLE '|| table_name ||' MOVE TABLESPACE USERS;' 
 FROM user_tables WHERE tablespace_name='xxx';

SELECT 'ALTER INDEX '|| index_name ||' REBUILD TABLESPACE USERS;'
 FROM user_indexes WHERE tablespace_name='xxx';

SELECT 'ALTER TABLE '|| table_name ||' MOVE LOB ('
    || column_name ||') STORE AS (TABLESPACE USERS);' 
 FROM user_lobs WHERE tablespace_name='xxx';

Execute the generated statements. After that the new tablespace should be empty and can be removed again.
posted on 2006-09-22 17:45 UTC in Code | 0 comments | permalink