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

Script for full Oracle schemas

How can I generate a script for a full Oracle database or for full Oracle schemas as in MS SQL Server?
There are some third party tools (like TOAD) that can do this for you. If you are using Oracle9i, then you can use the DBMS_METADATA package to generate the DDL statements for you as follows:

SELECT DBMS_METADATA.GET_DDL(object_type,object_name) FROM
user_objects;

Otherwise, your best bet is to use Oracle's export/import utilites. Export your schema with the exp utility using the OWNER parameter to specify the schema name and use ROWS=N so that no rows of data are actually exported. The resulting dump file will contain your DDL statements. You just need to get those statements out of that dump file. This can be done with the imp utility using the SHOW=Y and LOG=logfile parameters. The logfile will contain all of your schema's DDL statements.

Dig Deeper on Oracle database design and architecture

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.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close