exp using query and imp in another DB with different table & tablespace

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!!