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