Odi's astoundingly incomplete notesNew entries | Code
Import an oracle dump into any tablespace
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:
- Use the imp utility's INDEXFILE= option to create a script.
- 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.
- Run this script to create the empty schema.
- Disable all constraints.
- Remove the UNLIMITED_TABLESPACE privilege from the user.
- Give the user unlimited table space quota on the tablespaces you want.
- Import the dump with IGNORE=y and FROMUSER and TOUSER options if necessary.
- Enable all constraints.
- Revert your tablespace quota settings.
SELECT 'ALTER TABLE '|| table_name ||' MOVE TABLESPACE USERS;'Execute the generated statements. After that the new tablespace should be empty and can be removed again.
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';