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?
1
- You want only a DDL export? I.e. the DB schema without the data in it?Aaron Digulla– Aaron Digulla2010-07-07 09:12:51 +00:00Commented Jul 7, 2010 at 9:12
Add a comment |
3 Answers
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; 
Comments
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 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.
Comments
You can also use SQL Developer to create sql scripts, as described here.
