DDL DBMS_METADATA

Schema:

set pagesize 0
set long 90000
set feedback off
set echo off 

spool scott_schema.sql 

connect scott/tiger;

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;


For GoldenGate use with ggadmin user:

set pagesize 0
set long 90000
set feedback off
set echo off
spool <owner_name>.sql
connect ggadmin/ggadmin;
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,'<owner_name>') FROM DBA_TABLES u WHERE OWNER = '<owner_name>';
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name,'<owner_name>') FROM DBA_INDEXES u WHERE OWNER = '<owner_name>';
spool off;

Table:

SELECT dbms_metadata.get_ddl('TABLE', '<TABLE_NAME>','<schema>') FROM dual;

SELECT dbms_metadata.get_ddl('TABLE', 'AP_BANK_ACCOUNTS_ALL','GGODS') FROM dual;

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,'SCOTT') FROM DBA_TABLES u WHERE OWNER = 'SCOTT';


 Formate
set long 200000 pages 0 lines 131
column txt format a121 word_wrapped
spo v.sql
select dbms_metadata.get_ddl( 'VIEW', 'HOSTS_ALL_INPUT','ITSMREP' ) txt from dual;
spo off