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>';

Comments