Tuesday, July 9, 2013

Oracle Get database DDL's with dbms_metadata.get_ddl function

Since Oracle 9i you can get database DDL clauses from sqlplus with dbms_metadata.get_ddl utility. Data is fetch from data dictionary. Here is the list of possible object_types to fetch with dbms_metadata.get_ddl:
For Oracle 9i:
http://docs.oracle.com/cd/B10501_01/appdev.920/a96612/d_metad2.htm#1031458

For Oracle 11:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_metada.htm#BGBIEDIA


To get all one schema tables and indexes creation clauses out of the database you can do it with following SQL:

SQL> set pages 120
SQL> set lines 120
 

SQL> set long 5000

SQL> spool test_schema_name_ddl.txt

SQL> select dbms_metadata.get_ddl('TABLE',dt.table_name,dt.owner)||';' from dba_tables dt where dt.owner='<TEST_SCHEMA_NAME>';

SQL> select dbms_metadata.get_ddl('INDEX',di.index_name,di.table_owner)||';' from dba_indexes di where di.table_owner='<TEST_SCHEMA_NAME>';

SQL> spool off;

Same way you can add more clauses inside spool. For example sequences, synonyms, db_links etc...

If you want to cleaner output you can get it with sqlplus settings (thought select clauses are added in the spool file still):
set heading off
set feedback off
set verify off



No comments:

Post a Comment