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.