Get Object DDL Definition

-- Get Object DDL Definition

-- Get Package Definition

SELECT

DBMS_METADATA.GET_DDL

('PACKAGE','<PACKAGE NAME>','<SCHEMA NAME>')

FROM dual;

-- Get Procedure Definition

SELECT

DBMS_METADATA.GET_DDL

('PROCEDURE','<PROCEDURE NAME>','<SCHEMA NAME>')

FROM dual;

-- Get Function Definition

SELECT

DBMS_METADATA.GET_DDL

('FUNCTION','<FUNCTION NAME>','<SCHEMA NAME>')

FROM dual;

-- Get View Definition

SELECT

DBMS_METADATA.GET_DDL

('VIEW','<VIEW NAME>','<SCHEMA NAME>')

FROM dual;

Or

SELECT owner,view_name, text FROM dba_views where owner='<SCHEMA NAME>';

-- View that start with v$

SELECT view_name, view_definition FROM v$fixed_view_definition;

-- Get Index Definition

SELECT

DBMS_METADATA.GET_DDL

('INDEX','<INDEX NAME>','<SCHEMA NAME>')

FROM dual;

-- Get Table Definition

SELECT

DBMS_METADATA.GET_DDL

('TABLE','<TABLE NAME>','<SCHEMA NAME>')

FROM dual;

-- Get Trigger Definition

SELECT

DBMS_METADATA.GET_DDL

('TRIGGER','<TRIGGER NAME>','<SCHEMA NAME>')

FROM dual;

-- Get Role Definition

SELECT

DBMS_METADATA.GET_DDL

('ROLE','<ROLE NAME>')

FROM dual;

-- Get Profile Definition

SELECT

DBMS_METADATA.GET_DDL

('PROFILE','<PROFILE NAME>')

FROM dual;

-- Get Tablespace Definition

SELECT

DBMS_METADATA.GET_DDL

('TABLESPACE','<TABLESPACE NAME>')

FROM dual;

-- Get Matterialized View Definition

select query from user_mviews where mview_name='<MATERIALIZED_VIEW_NAME>';