metadata

set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_list.sql
SELECT DBMS_METADATA.GET_DDL('USER', username) FROM DBA_users;
select dbms_metadata.get_ddl('TABLE','table_name','owner') from dual;
select dbms_metadata.get_ddl('INDEX','index_name','owner') from dual;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','tablespace_name') FROM dual;
select dbms_metadata.get_ddl('CLUSTER','C_MLOG#','SYS') from dual;
select dbms_metadata.get_ddl('CONTEXT','LT_CTX') from dual;
select dbms_metadata.get_ddl('DB_LINK','PROD.WORLD','ADAM') from dual;
select dbms_metadata.get_ddl('FUNCTION','TO_DATE_FUNC','SCOTT') from dual;
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','BS_TAB_MV','SYS') from dual;
select dbms_metadata.get_ddl('MATERIALIZED_VIEW_LOG','MLOG$_BS_TABLE','SYS') from dual;
select dbms_metadata.get_ddl('PACKAGE','XMLPARSER','SYS') from dual;
select dbms_metadata.get_ddl('PACKAGE_BODY','STATSPACK','PERFSTAT') from dual;
select dbms_metadata.get_ddl('PROCEDURE','ORA$_SYS_REP_AUTH','SYSTEM') from dual;
select dbms_metadata.get_ddl('SEQUENCE','STATS$SNAPSHOT_ID','PERFSTAT') from dual;
select dbms_metadata.get_ddl('SYNONYM','/2fddc42_paintARGB_PRE_ONTO_S5','PUBLIC') from dual;
select dbms_metadata.get_ddl('TRIGGER','DEF$_PROPAGATOR_TRIG','SYSTEM') from dual;
select dbms_metadata.get_ddl('TYPE','XMLSEQ_IMP_T','SYS') from dual;
select dbms_metadata.get_ddl('TYPE_BODY','ORACLE_LOADER','SYS') from dual;
select dbms_metadata.get_ddl('VIEW','DBA_PROPAGATION','SYS') from dual;
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('TRIGGER','tablename','schemaname') from dual;
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','indexname','schemaname') from dual;
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','tablename','schemaname') from dual;
select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','schemaname') from dual;
select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','schemaname') from dual;
select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','schemaname') from dual;
spool off


metadata for all the users
----------------------------------
set head off
set pages 0
set long 9999999
spool test.sql
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) '/' DDL FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) '/' DDL FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) '/' DDL FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) '/' DDL FROM DBA_USERS;
spool off;


metadata for complete tables and indexes
----------------------------------------------------------
set pagesize 0
set long 90000
set feedback off
set echo off
spool schema.sql
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)FROM USER_INDEXES u;
spool off;


metadata for all the tables in a schema
--------------------------------------------------------
set pagesize 0
set long 90000
spool tables_ddl.sql
SELECT dbms_metadata.get_ddl('TABLE', table_name, 'SCOTT') FROM ALL_TABLES WHERE OWNER = 'SCOTT';