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:
- Can you give me a detailed procedure how it can be done?
- How can I do the import without affecting the existing tablespace & user?
- 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=yThe 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
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.