DBMS_METADATA package to retrieve metadata

--- Format

set heading off;

set echo off;

Set pages 999;

set long 90000;

BEGIN

dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE );

dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );

END;

/

--- Example 

select dbms_metadata.get_ddl('TRIGGER','&TRIGGER_NAME,'&OWNER') from dual;

select dbms_metadata.get_ddl('TYPE','&TYPE_NAME','&OWNER') from dual;

select dbms_metadata.get_ddl('INDEX','&INDEX_NAME','&OWNER') from dual;

select dbms_metadata.get_ddl('TABLE','&TABLE_NAME','&OWNER') from dual;

select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') from dual;

select dbms_metadata.get_ddl('PROCEDURE','&PROCEDURE_NAME','&OWNER') from dual;

select dbms_metadata.get_ddl('PROFILE','&PROFILE_NAME') from dual;

select dbms_metadata.get_ddl('PACKAGE','&PACKAGE_NAME','&OWNER') from dual;

select dbms_metadata.get_ddl('DB_LINK','&DBLINK_NAME','&OWNER') from dual;