Problem solve Get help with specific problems with your technologies, process and projects.

Importing without affecting existing tablespace

I need to export from username 'X', tablespace 'T' from a production database 'PROD' and import it to test database 'TEST' with a different username 'Y' and a different tablespace. But, there exists a tablespace 'T' and user 'X' as in 'PROD' database. I don't want any changes in that. My questions are:

  1. Can you give me a detailed procedure how it can be done?
  2. How can I do the import without affecting the existing tablespace & user?
  3. Any precautionary steps to be taken?

Oracle's export utility lets you export the entire contents of a tablespace or an entire schema's objects, but not both at the same time, i.e. a specific schema's objects in a specific tablespace. If I had to perform such an operation, I would use the TABLES clause for the exp utility, but I would query DBA_TABLES to find out which tables in the specific tablespace for the specific owner to export. For example:

SELECT table_name FROM dba_tables
WHERE tablespace_name='ts01' AND OWNER='scott';
Once I know all of the tables that SCOTT has in tablespace TS01, I can then export just those tables as follows:
exp userid=scott file=my_dump.dmp tables=table1,table3,table4,......
Use the TABLES parameter with the list of tables you generated in the query above.

Before I would import the data, I would run imp with INDEXFILE=create.sql and FULL=Y to generate a SQL script (create.sql) which will contain all of the DDL commands to precreate your tables. Load this script into any text editor and then modify the CREATE TABLE commands to create the table in the new tablespace with the new owner. Then run the script. Your tables are now precreated in the correct location with the correct owner, but they are empty. To populate them with data from the dump file, import with parameters similar to the following:

imp userid=system/manager file=my_dump.dmp fromuser=scott
touser=newuser ignore=y
The FROMUSER/TOUSER combination will import SCOTT's tables into the NEWUSER schema. The IGNORE=Y parameter will cause the imp process to ingore that fact that your tables were precreated.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.