Friday, 16 February 2018

Generating create SQL through dbms_metadata package

Generating "create SQL" through dbms_metadata package

DBMS_METADATA

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

Syntax:-

SQL> SELECT DBMS_METADATA.GET_DDL('<object_type','<object_name>','<object owner>') FROM DUAL;

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;

2 comments:

  1. Thanks for the given information you are providing content very usefull to users.
    Learn How to Work With Ref Cursor in ORACLE 12C For Beginner Actual cost of this course is 9,999/- But now Kuncham Software Solutions Pvt ltd is giving you Free access. Limited period offer Hurry-up Enroll Now at Udemy.


    Hurry-Up Enroll Now and get Free Access to the Entire Course


    ReplyDelete
  2. Thanku very much for the information on create SQL through dbms_metadata package. this are the examples are very clear understand for everyone.
    Oracle DBA with Cloud can be learnt by the RND Trainings with the reaonable cost and learn linux with the free of cost.
    .http://www.rndtrainings.com/oracle/oracle-dba-2/

    ReplyDelete

Cloning database with RMAN

Below are the steps to clone production database(primedb) to UAT database(clonedb)   STEP1 :- Check the both servers are able to connect ...