Problem solve Get help with specific problems with your technologies, process and projects.

Copying database structure without data

I want to take the structure of my database, but not the data. Is it possible?

I want to take the structure of my database, but not the data. Is it possible in any way?

I do this all the time in some of my systems. This is a great 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. I use Oracle's export and import utilities for this activity. The trick is to export without any data. The ROWS=N parameter will tell exp to not include any rows. So I have a nightly script which performs the following:

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

The resulting export dump file will contain all of the DDL to re-create your database objects, but the dump file will not be terribly large since no data was included in the export.

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_ddl.dmp show=y 
log=create_ddl.sql full=y

I log the output to a file and the file will contain all of the CREATE and ALTER statements necessary to re-create the database objects. If you do not want to see all objects in the database, then you can use other imp parameters to limit your output. For instance, to see just the SCOTT.EMP table, use the following:

imp userid=system file=full_ddl.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.

Dig Deeper on Oracle database backup and recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.