Copying Database Structure Without Data Using exp/imp

This is a very good way to get the DDL of your database objects so that you can re-create a view or stored procedure without having to perform an incomplete restore.

exp userid=system file=full_03102013.dmp rows=n

When I need to see the contents, I use the import utility with the SHOW=Y parameter to see the DDL, similar to the following:

imp userid=system file=full_03102013.dmp show=y  log=create_ddl_03102013.sql full=y

For instance, to see just the SCOTT.EMP table, use the following:

imp userid=system file=full_03102013.dmp show=y  log=create_emp.sql tables=scott.emp

The imp utility will skip all other objects in the dump file. The resulting log file will need some slight text editing to get the correct command.

Finally, the INDEXFILE parameter is much more readable than SHOW=Y for the imp utility. However, the INDEXFILE parameter will only show the CREATE TABLE and CREATE INDEX statements. If you need to see CREATE VIEW or CREATE PROCEDURES statements, the only option is the SHOW=Y parameter.