How to get DDLs of multiple objects in oracle database

 

This document describes how to get DDLs of multiple objects lik package, package body, table, index, procedure etc....

Syntax:

SQL>set lines 200

SQL>set pages 99999
SQL>set long 1000000

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

Use below query to get the DDL of Package specification from HR schema in oracle database.

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','DEPT.PKG','HR') FROM DUAL;

Use below query to get the DDL of Package body from HR schema in oracle database.

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','DEPT.PKG','HR') FROM DUAL;

Get the Index structure from HR schema using below query.

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','DEPT_IND_P1','HR') FROM DUAL;

Use below command to get the DDL of a tablespace.

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

Use below command to get the DDL of a table from HR schema.

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','HR') FROM DUAL;

Use below query to get the system grants of a schema.

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<schema name>') from dual;

Role grants of a schema:

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<schema name>') from dual;

Object grants of a schema:

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','<schema name>') from dual;

Thanks for going through the post.................