Friday, October 05, 2007

Oracle Import Trick

If you ever need to import a select list of tables from a large export file, a good trick is to do a import with rows=n which will just create the tables without data

imp scott/tiger@orcl file=export.dmp rows=n

then you can simply delete the tables you wish to import from the schema and then run the import normally. Oracle will simply throw a create error and skip the table

you can get funky if you can access the orginal schema and do things like this (note the dblink)

select 'drop table ' || table_name ||';' drop_sql
from dba_tables
where owner ='SCOTT'
and secondary = 'N'
and table_name in
(select table_name
from dba_tables@srcdb_link
where owner ='SCOTT'
and secondary = 'N')
order by 1

which is useful if you have a big export which you only want to import a subset of the tables because you have dropped them already in the src database

No comments: