DBMS_METADATA package to extract objects DDL

-----Always set below for getting full query result

set long 5000;

set pages 0;

-- 1. Get DDL for all Tables in the HR schema

SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME, 'HR')

FROM ALL_TABLES

WHERE OWNER = 'HR';

-- 2. Get DDL for all Indexes in the HR schema

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME, 'HR')

FROM ALL_INDEXES

WHERE OWNER = 'HR';

-- 3. Get DDL for all Constraints in the HR schema

(Primary Key, Unique, Foreign Key, Check)

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', CONSTRAINT_NAME, 'HR')

FROM ALL_CONSTRAINTS

WHERE OWNER = 'HR' AND CONSTRAINT_TYPE IN ('P', 'U', 'R', 'C');

-- 4. Get DDL for all Sequences in the HR schema

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', SEQUENCE_NAME, 'HR')

FROM ALL_SEQUENCES

WHERE SEQUENCE_OWNER = 'HR';

-- 5. Get DDL for all Views in the HR schema

SELECT DBMS_METADATA.GET_DDL('VIEW', VIEW_NAME, 'HR')

FROM ALL_VIEWS

WHERE OWNER = 'HR';

-- 6. Get DDL for all Synonyms in the HR schema

SELECT DBMS_METADATA.GET_DDL('SYNONYM', SYNONYM_NAME, 'HR')

FROM ALL_SYNONYMS

WHERE OWNER = 'HR';

-- 7. Get all Roles assigned to HR user

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

-- 8. Get all System Privileges granted to HR user

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

-- 9. Get all Object Privileges granted to HR user

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

-- 10. Remove storage, tablespace, and segment attributes from DDL output

BEGIN

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);

END;

/

-- DDL for Tables

SELECT DBMS_METADATA.GET_DDL('TABLE', u.table_name, 'SCHEMA')  

FROM dba_tables u  

WHERE owner = 'SCHEMA'

AND u.table_name = 'OBJECT';  

-- DDL for Indexes

SELECT DBMS_METADATA.GET_DDL('INDEX', u.index_name, 'SCHEMA')  

FROM dba_indexes u

WHERE owner = 'SCHEMA'

AND u.index_name = 'OBJECT';  

-- DDL for Views

SELECT DBMS_METADATA.GET_DDL('VIEW', u.view_name, 'SCHEMA')  

FROM dba_views u

WHERE owner = 'SCHEMA'

AND u.view_name = 'OBJECT';  

-- DDL for Sequences

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', u.sequence_name, 'SCHEMA')  

FROM dba_sequences u

WHERE owner = 'SCHEMA'

AND u.sequence_name = 'OBJECT';  

-- DDL for Procedures

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', u.object_name, 'SCHEMA')  

FROM dba_procedures u

WHERE owner = 'SCHEMA'  

AND object_type = 'PROCEDURE'

AND u.object_name = 'OBJECT';  

-- DDL for Functions

SELECT DBMS_METADATA.GET_DDL('FUNCTION', u.object_name, 'SCHEMA')  

FROM dba_procedures u

WHERE owner = 'SCHEMA'  

AND object_type = 'FUNCTION'

AND u.object_name = 'OBJECT';  

-- DDL for Packages

SELECT DBMS_METADATA.GET_DDL('PACKAGE', u.object_name, 'SCHEMA')  

FROM dba_procedures u

WHERE owner = 'SCHEMA'  

AND object_type = 'PACKAGE'

AND u.object_name = 'OBJECT';  

-- DDL for Triggers

SELECT DBMS_METADATA.GET_DDL('TRIGGER', u.trigger_name, 'SCHEMA')  

FROM dba_triggers u

WHERE owner = 'SCHEMA'

AND u.trigger_name = 'OBJECT';  

-- DDL for Synonyms

SELECT DBMS_METADATA.GET_DDL('SYNONYM', u.synonym_name, 'SCHEMA')  

FROM dba_synonyms u

WHERE owner = 'SCHEMA'

AND u.synonym_name = 'OBJECT';  

-- DDL for Materialized Views

SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', u.mview_name, 'SCHEMA')  

FROM dba_mviews u

WHERE owner = 'SCHEMA'

AND u.mview_name = 'OBJECT';  

-- DDL for Constraints

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', u.constraint_name, 'SCHEMA')  

FROM dba_constraints u

WHERE owner = 'SCHEMA'

AND u.constraint_name = 'OBJECT';  

--Get DDL commands for all objects in database for a Schema --

SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, 'SCHEMA') FROM

dba_tables WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, 'SCHEMA') FROM

dba_indexes WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('VIEW', view_name, 'SCHEMA') FROM

dba_views WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', sequence_name, 'SCHEMA') FROM

dba_sequences WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', object_name, 'SCHEMA') FROM

dba_procedures WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('FUNCTION', object_name, 'SCHEMA') FROM

dba_procedures WHERE owner = 'SCHEMA' AND object_type = 'FUNCTION'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('PACKAGE', object_name, 'SCHEMA') FROM

dba_procedures WHERE owner = 'SCHEMA' AND object_type = 'PACKAGE'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('TRIGGER', trigger_name, 'SCHEMA') FROM

dba_triggers WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('SYNONYM', synonym_name, 'SCHEMA') FROM

dba_synonyms WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', mview_name, 'SCHEMA')

FROM dba_mviews WHERE owner = 'SCHEMA';