Oracle exports SQL to database structure

Question

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

Anyway, if sqlplus exports the user database?

Answer 1

There are two main approaches.

The first is to export the dump file. This could be using 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.

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

To import a file, we use the appropriate utilities impdp(or imp).

. SQL, DBMS_METADATA, Oracle 9i. , . .

Answer 2

, , dbms_metadata, create table SQL. :

 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 3

You can also use SQL Developer to create SQL scripts, as described here .