Tuesday, March 18, 2008

How to import a schema from a dump file and ignore all data in tables.

If you need to import schema without data from a dump file you should follow the following routine:
1. Extract the DDL script of this schema from the dump file:
imp file=exp_file.dmp indexfile=ddl_script.sql userid=system/xxxxxxxx@destination_db fromuser=dmp_user touser=dest_user
Where:
exp_file.dmp – the oracle dump file.
ddl_script.sql – the DDL script which will include all DDL commands of this schema.
xxxxxxxx – system password.
destination_db – an oracle tnsnames alias of the destination database. We could omit it if we are doing import on the same computer where the database is with proper environment variables.
dmp_user – user name in the dump file.
dest_user – user name in the destination database.
2. After the ddl_script.sql file is ready you should remove the REM comments from it.
3. Connect as dest_user to the destination database and execute the ddl_script.sql script. Now we have the non-populated schema without PL/SQL code.
4. Import the dump file on the dest_user schema using the following command:
imp file= exp_file.dmp IGNORE=N userid=system/xxxxxxxx fromuser=dmp_user touser=dest_user
Using IGNORE=N flag we skip all inserts and keep having empty tables however PL/SQL code will be imported.

That’s it.

Thanks,
Sergey.

2 comments:

Peter B said...

Thanks. Very useful.

Unknown said...

Amazing Article ! I have bookmarked this article page as i received good information from this. All the best for the upcoming articles. I will be waiting for your new articles. Thank You ! Kindly Visit Us @ Coimbatore Travels | Ooty Travels | Coimbatore Airport Taxi