You could do a couple of different methods. Both of which can be done while the production database is running.
1. You could set up a script file that would truncate the tables in the test environment and then load the tables from the production environment. (select * in the example assumes the tables are exactly alike in structure)
connect test/pwd@test (include @test if your test db is in a different instance- you would need to set up database links)
Truncate table test.table1;
Truncate table test.table2;
Insert into test.table1 select * from prod.table1@prod; (include @prod if your test and production dbs are in different instances)
Insert into test.table2 select * from prod.table2@prod;
2. Another option is to export the data from the production database with the export (EXP) program Then import (IMP) into your test environment. You would still need to either drop the tables in the test environment or truncate them. Use EXP help=y and IMP help=y to view the parameters that can be specified.
With EXP, you can specify the user to export and/or you could export specific tables.
Example parameters for EXP will export all of the schema PROD objects:
FILE=C:\oraback.dmp
LOG=C:\oraexp.log
owner=PROD
With IMP, if your schema is different from your production to your test dbs, you can use the FROMUSER and TOUSER parameters. Example IMP parameters:
FILE=C:\oraback.dmp
LOG=C:\oraimp.log
FROMUSER=PROD —> specify FROMUSER / TOUSER if the schemas are different
TOUSER=TEST
tables=(table1, table2, table3) —> you can specify specific tables if you don’t want all
GRANTS=n
INDEXES=y
ROWS=y
CONSTRAINTS=y
COMMIT=y
Discuss This Question: