Tutorial IT Point: DBMS_METADATA Package

Generating "create SQL" through dbms_metadata package

DBMS_METADATA:-This package is used to generate the create SQL for Table,Index,Package etc.

Syntax:-

SQL> SELECT DBMS_METADATA.GET_DDL('','

Example1:-Generate the CREATE SQL for EMP table OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') FROM DUAL;

Example2:-Generate the CREATE SQL for PK_EMP index OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','PK_EMP','SCOTT') FROM DUAL;

Example3:-Generate the CREATE SQL for EMP_PKG Package Specification OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','EMP_PKG','SCOTT') FROM DUAL;

Example4:-Generate the CREATE SQL for EMP_PKG Package Body OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','EMP_PKG','SCOTT') FROM DUAL;

Example5:-Generate the CREATE SQL for USER TABLESPACE

SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','USERS') FROM dual;

Example6:-Generate the ALTER SQL for FK_DEPTNO foreign key constraints of SCOTT SCHEMA.

SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','EMP','SCOTT') from dual;

Example7:-Generate GRANT SQL for the System privileges grants for a SCOTT schema.

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT') from dual;

Example8:-Generate the GRANT SQL for the Role grant for SCOTT schema,

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT') from dual;

Example9:-Generate the GRANT SQL for object grants for SCOTT schema

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT') from dual;

Example10:-Generate the CREATE SQL for all tables of SCOTT SCHEMA

SQL>spool tables.sql

SQL>select 'select dbms_metadata.get_ddl(''TABLE'', '''||TABLE_NAME||''',''SCOTT'') from dual;' FROM DBA_TABLES where owner='SCOTT';

Example11:-Generate the CREATE SQL for EMPV view OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('VIEW','EMPV','SCOTT') FROM DUAL;