Yesterday I was requested to export some records from production to test DB using a query. There are two options:
- Do it with and exported CSV fole and sqlloader but there were about 300 columns in that table.I dont like this plan (arranging 300 columns in control file and exporting CSV file).
- Export using emp and use "query" parameter to export.
I choose 2nd option. But thare are also some limitations:
- My source and target table have different names.
- Source & destination tablespace have different names.
After some experiment & googling I got below solutions:
- As the terget DB is a test DB then we can temporarily change the name of the target table.
- Use "ignore=y" parameter to eliminate tablespace problem.
Assume,
- Production DB name is PROD. Table name is tableA (having partition ZERO & NONEZERO) under user tst (source)
- Test DB name is TEST. Table name is tableB (having partition ZERO & NONEZERO) under user prd (target)
My export par file(exp.par) was as below:
tables=prd.tableA:NONZERO
query="where customer_id in (8, 2, 82, 994) and start_time_timestamp > to_date('24sep09235959', 'ddmonyyhh24miss')"
statistics=none
log=exp.log
file=exp.dmp
then I used below command to export:
exp prd@PROD parfile=exp.par
My impport par file(imp.par) was as below:
fromuser=prd
touser=tst
tables=tableA:NONZERO
ignore=y
log=imp.log
file=exp.dmp
Now the importent things are:
- using ignore=y in parfile and
- To change the target table name tableB to tableA
- Make a note about objects depending on tableB, may become invalid
Then,
$sqlplus tst@test
SQL>Alter table tableB rename to tableA;
Now execute below command to import from a privileged user (to import another schema's data):
imp privileged_user@TEST parfile=imp.par
Then,
sqlplus tst@test
Alter table tableA rename to tableB;
Recompile all the objects became invalid because of rename operation on table tableB in TEST DB.
done!!
