Goal

Solution

Well, this is quite popular question.

No doubt, that time to time you need to create a copy of Production Oracle DB at Demonstration/Training, QA/Acceptance or Development environment.

For sure you may use Oracle Export utility, but despite this approach works, it has a set of disadvantages that someday forced me to find another (faster) way to move Oracle data around.

Here we go with step-by-step how-to.

Note: you may want to change DB name while copying it to another destination. This case does not change the procedure much and steps required to do so will be specially highlighted in the article.

Also, you may want to change Oracle DBID of the Database. the reason is that Recovery Manager (RMAN) distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the same RMAN repository. For changing Oracle DBID for a database please refer to the corresponding HOWTO.

OK. Here we go. We will imagine a situation when you need top copy PRO (production) DB into TST (test) DB on test server...

To gather the statistic use this command:

execute dbms_stats.gather_schema_stats (ownname =>'PUT_HERE_SCHEMA_NAME',cascade => TRUE);