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

/

SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual;