This little SQL script is quite handy when constructing a parfile for an import from a dump file of a previous Oracle export

Suppose you want to import all tables beginning with "XY", and there are several hundreds of them.

You would first construct a parfile named my_parfile.par, with the following content:

USERID=USER/PASSWORD
BUFFER=40960
FILE=DUMP_FILE_NAME.dmp
LOG=IMPORT_LOG_FILE_NAME.log
INDEXES=Y
ROWS=Y
CONSTRAINTS=Y

Then, you would create the following sql script (that you've called select_xy_tables_parfile.sql), in order  to construct  the list of tables you want to import (supposing the tables still have the same names as at the moment of the initial export):

set head off 
set pages 0
set trims on
set echo off
set feedback off  
spool my_XY_tables.txt
select decode( rownum, 1, 'TABLES=(', ',' ), table_name
from user_tables
where table_name like 'XY%'
union all
select ')', null
from dual ;
spool off
quit

Then, just run the script against your database:

SQL> @select_xy_tables_parfile.sql

After that, just do a cat my_XY_tables.txt >> my_parfile.par

Finally, after setting the correct ORACLE_SID, do an imp parfile=./my_parfile.par

You're done.

Happy computing.

Drop me a comment if this post has been useful to you, or if you see any reason for add-on or modification.

Nixman