Skip to content

How to Duplicate a Schema from One to Another

  • by

Suppose you are planning to duplicate user01 schema into user02 schema. Tools like TOAD, SQL Developer can be helpful. But you may also like to know the formal way provided by Oracle to finish your job. Yes, it's Data Pump.

1. Estimate the size of dump file.

[oracle@test ~]$ expdp "/ as sysdba" schemas=user01 content=all estimate_only=y

As you may have noticed, estimate_only=y, which indicates data pump to perform a dummy run for estimating the overall size only. Therefore, you can't have dumpfile in the above command.

2. Export user01 to the dump file.

[oracle@test ~]$ expdp "/ as sysdba" schemas=user01 content=all directory=data_pump_dir dumpfile=user01.dmp logfile=user01-export-`date +'%Y%m%d'`.log

This is obviously a schema mode of data pump because schemas is specified in the statement. On the other hand, the directory and logfile are optional, you may ignore the options for short.

3. Import the dump file to the target schema by remapping the schema name from user01 to user02.

[oracle@test ~]$ impdp "/ as sysdba" remap_schema=user01:user02 directory=data_pump_dir dumpfile=user01.dmp logfile=user01-import-`date +'%Y%m%d'`.log content=all table_exists_action=skip

The format of remap_schema is as below:

remap_schema=<source_schema>:<target_schema>

In my opinion, using data pump is more flexible than using tools to do the duplication. For example, you can selectively import either metadata, data or both of the source by specifying content option when import.

Leave a Reply

Your email address will not be published.