Tools for creating a "test" like environment on a development database

Question

We have an Oracle Enterprise database in production and another instance that we use as both a QA and Development database. QA has requested that they be given separate schemas within the database so they can test applications isolated from changes made by developers. For an example, say the schema used in development and the one that will be used in production is called APP_OWNER and APP_OWNER could contain tables that have FK references to tables in other schemas, say in BASE_OWNER. The idea would be to create a QA_APP_OWNER schema and to pull over the production data into that schema as well as pulling any BASE_OWNER tables referenced into the QA_APP_OWNER schema as well. A simplified illustration would be:

Prod Setup:
----------------
BASE_OWNER.users
APP_OWNER.users (synonym to BASE_OWNER.users)
APP_OWNER.audit_users with FK to BASE_OWNER.users

QA Setup:
----------------
QA_APP_OWNER.users  (copied data from prod)
QA_APP_OWNER.audit_users (FK to APP_OWNER.users)

This should be possible as we do not write code/SQL including schemas. (i.e we create schema based synonyms for tables outside the schema the application is running in)

My question is, are there good tools for easily creating such a QA_APP_OWNER schema? I'm aware of the FROMUSER TOUSER options of export, but If I remember correctly this will move an entire schema to another schema but it won't get me all the way to where I want to be b/c I need to change the references on the FKs. I'm unaware of a way short of exporting the DDL, manually changing it, and then importing the data manually. This is not an attractive option as many references are to tables that also reference other tables and the APP_OWNER schema has a plethora of tables itself. My fear is the more manual this is, the more likely-hood of a mistake that will allow something being tested to break when moved to the production environment. A nice solution would be to have licenses for both a dev and a qa instance of Oracle, but I have been told "it isn't in the budget" to do so.

Answer 1

Bit of a long shot, but will the impdp REMAP_SCHEMA option handle foreign keys in other schemas? I know there are some things it doesn't attempt to deal with but don't recall this scenario being mentioned - possibly just because it's unusual though.

Potentially you could do a single expdp of all the schemas, and an impdp remapping them all to QA_APP_OWNER in one go. Clearly this isn't something I've ever tried...

Answer 2

Don't do it.. Setup separate QA and Development databases. What you are wanting just isn't worth the hassle.