oracle 全DDL取得

set linesize 32767
set long 100000000
set longchunksize 100000000
set pagesize 0
set heading off
set echo off
set trimspool on


--DDLの終わりにセミコロン

EXEC DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE );
-- PK情報はalter tableで表現
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE );
-- STORAGE属性を省略
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE );
-- --セグメント属性省略
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',TRUE);
-- 表領域を省略
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',TRUE );
-- パーティション省略
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'PARTITIONING',TRUE);
-- スキーマ名省略
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA', TRUE);


spool /tmp/arcsdbss_all_create_table.sql


select dbms_metadata.get_ddl('TABLE',a.table_name,a.owner) from dba_all_tables a where a.owner = 'ARCSDBSS';


spool off



set linesize 32767
set long 100000000
set longchunksize 100000000
set pagesize 0
set heading off
set echo off
set trimspool on

spool /tmp/arcsdbss_all_create_index.sql


select dbms_metadata.get_ddl('INDEX',t.INDEX_NAME,t.OWNER ) from dba_indexes tleft outer join DBA_CONSTRAINTS c
on t.OWNER =c.INDEX_OWNER and t.INDEX_NAME = c.INDEX_NAME
where t.OWNER = 'ARCSDBSS' and c.CONSTRAINT_TYPE is null;

spool off

※ PKはTABLE側にも存在するのでINDEXから外す。（ c.CONSTRAINT_TYPE is null;）

【参考】
select dbms_metadata.get_ddl('TABLE',a.table_name,a.owner) from dba_all_tables a where a.owner = 'ARCSDBCB' and a.table_name='CCP020';
select dbms_metadata.get_ddl('INDEX',index_name,owner) from dba_indexes where owner = 'ARCSDBCB';