Q
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.

This was last published in January 2007

Dig Deeper on Oracle database backup and recovery

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close