Generating create scripts through dbms_metadata package

 
 
Execute the following to get the create script of existing database objects
 
SQL>set lines 120
SQL>set pages 99999
SQL>set long 1000000
SQL> SELECT DBMS_METADATA.GET_DDL('<object_type','<object_name>','<object owner>') FROM DUAL;
For example
 
 
 
Following example to get table structure of EMP OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') FROM DUAL;
 
 
 
Following example to get index structure of PK_EMP OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','PK_EMP','SCOTT') FROM DUAL;
 
 
 
Following example to get Package Specification  EMP_PKG OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','EMP_PKG','SCOTT') FROM DUAL;
 
 
 
Following example to get Package Body   EMP_PKG OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','EMP_PKG','SCOTT') FROM DUAL;
 
 
 
Also we could get ddl for tablespace
 
SQL>  SELECT DBMS_METADATA.GET_DDL('TABLESPACE','USERS') FROM dual;
 
 
 
To get the defination of the foreign key constraints.
 
SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','<table_name>','<schema>') from dual;
 
 
 
To get the System privileges grants for a schema.
 
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<schema>') from dual;
 
 
 
To get the Role grant for a schema,
 
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<schema>') from dual;
 
 
 
To get the object grants for a schema
 
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','<schema>') from dual;
 
 
 
Following query to get the create scripts of all  tables of  a particular schema

SQL>spool tables.sql
SQL>select 'select dbms_metadata.get_ddl(''TABLE'', '''||TABLE_NAME||''',''<schema>'') from dual;' FROM DBA_TABLES
/


 
Comments