Oracle exporting SQL of the Database structure

Question

I want to create an sql script that can recreate a DB that I already have. I want to recreate the DB without data inside.

So is there anyway with sqlplus of exporting a DB of a user?

Answer 1

From this blog post, it looks like there is a package called dbms_metadata that can generate create table SQL. Example:

 set pagesize 0
 set long 90000
 set feedback off

 set echo off 
 spool filename.sql 
 connect username/password;
 SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_TABLES u;
 SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
     FROM USER_INDEXES u;
 spool off;

Answer 2

There are two basic approaches.

The first is to export a dump file. This can be with the Datapump utility:

$ expdp apc/pw directory=data_dump_dir dumpfile=apc_20100707.dmp content=METADATA_ONLY  

Find out more.

Datapump was introduced in Oracle10g. In earlier versions of the database we could use the EXP utility to do the same thing.

$ exp apc/pw dumpfile=apc_20100707.dmp rows=N

To import the file we use the matching impdp (or imp) utilities.

That is the OS approach. To generate actual SQL scripts we can use the built-in DBMS_METADATA package, introduced in Oracle 9i. This is a bit more work but offers much finer control over the details of the exported objects. Find out more.

Answer 3

You can also use sql developer to create sql scripts. http://www.oracle.com/technology/products/database/sql_developer/index.html