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
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.