Script to extract the Metadata

How to Extract the DDL for oracle user,Profiles,Roles,Tables,Index,Tablespace,Package,Trigger and Db links.

SET HEADING OFF

SET ECHO OFF

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

SPOOL DDL_INFO.LOG

SELECT DBMS_METADATA.GET_DDL('USER','TIGER') FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('PROFILE','DEFAULT') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','TIGER') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOT') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOT') FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('ROLE','SUB') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','RESOURCE') FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','PKG_HADOOP','IDC6AMASK')FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('INDEX', 'IX2_MARS_MASTER', 'MARS') FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('INDEX', 'IDX1_TXN1', 'MARS') FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEE','SCOT') FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('TABLESPACE','&TABLESPACE_NAME') FROM dual;

SELECT DBMS_METADATA.GET_DDL('TRIGGER', trigger_name, owner)

FROM   all_triggers

WHERE  owner        = UPPER('&1')

AND    trigger_name = DECODE(UPPER('&2'), 'ALL', trigger_name, UPPER('&2'));

SELECT DBMS_METADATA.GET_DDL('DB_LINK','DEL_CITEAB_SOURCE','DEL_THIRDPARTY2')

FROM DUAL;

Here DEL_CITEAB_SOURCE is the name of the db link name and DEL_THIRDPARTY2 is the owner name.

SPOOL OFF;

Enjoy the work and do like me if you like this article!!!