DBMS_METADATA ....


https://anuj-singh.blogspot.com/2018/01/oracle-profile-metadata.html
https://anuj-singh.blogspot.com/2012/04/oracle-job-metadata.html

from 
https://github.com/oraclebase/dba/blob/master/

define O='ANUJ'
define sequence_name='S'


SET LONG 50000 LONGCHUNKSIZE 50000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT DBMS_METADATA.get_ddl ('SEQUENCE', sequence_name, sequence_owner)
FROM   all_sequences
WHERE  sequence_owner = UPPER('&O')
AND    sequence_name  = DECODE(UPPER('&sequence_name'), 'ALL', sequence_name, UPPER('&sequence_name'))
;

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON


===========

define O='ANUJ'
define SYNONYM='S'

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT DBMS_METADATA.get_ddl ('SYNONYM', synonym_name, owner)
FROM   all_synonyms
WHERE  owner = UPPER('&O')
--AND    synonym_name  = DECODE(UPPER('&SYNONYM'), 'ALL', synonym_name, UPPER('&SYNONYM'))
;

SET PAGESIZE 14 FEEDBACK ON VERIFY ON

=================


define O='ANUJ'
define table_name='EMP'


SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true)
;
END;
/

SELECT DBMS_METADATA.get_ddl ('CONSTRAINT', constraint_name, owner)
FROM   all_constraints
WHERE  owner      = UPPER('&O')
AND    table_name = DECODE(UPPER('&table_name'), 'ALL', table_name, UPPER('&table_name'))
AND    constraint_type IN ('U', 'P');

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON


============================

define O='ANUJ'
define table_name='EMP'


SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
   -- Uncomment the following lines if you need them.
   --DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', false);
   --DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', false);
END;
/

SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner)
FROM   all_tables
WHERE  owner      = UPPER('&O')
AND    table_name = DECODE(UPPER('&table_name'), 'ALL', table_name, UPPER('&table_name'))
;

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON

====================================

define O='ANUJ'
define OBJECT_GRANT='EMP'

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT', UPPER('&OBJECT_GRANT'), UPPER('&O')) from dual
;

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON

======================================


define O='ANUJ'
define table_name='EMP'


SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);

   -- Uncomment the following lines if you need them.
   --DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', false);
   --DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', false);

END;
/

SELECT DBMS_METADATA.get_ddl ('INDEX', index_name, owner)
FROM   all_indexes
WHERE  owner      = UPPER('&O')
AND    table_name = DECODE(UPPER('&table_name'), 'ALL', table_name, UPPER('&table_name'))
;

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON


===


define O='ANUJ'
define table_name='EMP'


SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT DBMS_METADATA.get_ddl ('TRIGGER', trigger_name, owner)
FROM   all_triggers
WHERE  table_owner = UPPER('&O')
AND    table_name  = UPPER('&table_name');

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON



==

define tablespace_name='SYSTEM'


SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name)
FROM   dba_tablespaces
WHERE  tablespace_name = DECODE(UPPER('&tablespace_name'), 'ALL', tablespace_name, UPPER('&tablespace_name'));

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON

====


define O='ANUJ'
define trigger_name='T'


SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT DBMS_METADATA.get_ddl ('TRIGGER', trigger_name, owner)
FROM   all_triggers
WHERE  owner        = UPPER('&O')
AND    trigger_name = DECODE(UPPER('&trigger_name'), 'ALL', trigger_name, UPPER('&trigger_name'));

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON

===




define O='ANUJ'
define view_name='T'



SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT DBMS_METADATA.get_ddl ('VIEW', view_name, owner)
FROM   all_views
WHERE  owner      = UPPER('&O')
AND    view_name = DECODE(UPPER('&view_name'), 'ALL', view_name, UPPER('&view_name'));

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON


====