!!!DDLの取得
!!DBMS_METADATA.GET_DDL
GET_DDLを使うとDDLを取得できる。

!!DDLの出力形式を変更する
https://siguniang.wordpress.com/2013/09/04/oracle-dbms_metadata-get_ddl-fetch-ddl-for-tables/
https://www.toadworld.com/platforms/oracle/b/weblog/archive/2015/11/25/get-ddl-and-set-transform-param-in-dbms-metadata
https://stackoverflow.com/questions/6782278/generating-ddl-script-for-object-without-schema-name-baked-in-using-dbms-metadat

!スキーマ名を省略する
 execute DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA', false);

!storageセクションを省略する
 execute dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );

!外部キーをalter tableにする
 execute dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true );

!セグメント属性を省く
 execute dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false );

!DDL 単位でセミコロンをつける
 execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

!!!各オブジェクトを出力するバッチ
!!getDDL.sh
  DBCONNECT=user/pass@host:port/service
  OWNER=取得スキーマ
  OUT_PATH=出力先
  
  OBJECTS="
  TABLE
  VIEW
  SEQUENCE
  FUNCTION
  PROCEDURE
  INDEX
  DATABASE LINK
  "
  
  WORKFILE=temp.tmp #拡張子は必ず付けること
  
  source setenvora.sh
  
  LINE_SEP='
  '
  OLD_IFS=$IFS
  IFS=$LINE_SEP
  
  # 各オブジェクト取得
  for TYPE in ${OBJECTS}
  do
    sqlplus64  ${DBCONNECT} @getName.sql "${TYPE}" ${OWNER} ${WORKFILE}
    
    mkdir -p "${OUT_PATH}/${TYPE/ /_}"
    rm -rf "${OUT_PATH}/${TYPE/ /_}"/*
    
    cat ${WORKFILE} | while read line
    do
      sqlplus64  ${DBCONNECT} @getObject.sql "${TYPE}" ${OWNER} "${line}" "${OUT_PATH}/${TYPE/ /_}/${line/ /_}.sql"
    done
  done
  
  # 表領域取得
  sqlplus64  ${DBCONNECT} @getTablespace.sql "${OUT_PATH}/TABLESPACE.sql"
  
  # ユーザ
  mkdir -p "${OUT_PATH}/USERS"
  rm -rf "${OUT_PATH}/USERS/*"
  
  sqlplus64  ${DBCONNECT} @getUsersName.sql ${WORKFILE}
  cat ${WORKFILE} | while read line
  do
    sqlplus64  ${DBCONNECT} @getUser.sql "${line}" "${OUT_PATH}/USERS/${line/ /_}.sql"
  done
  IFS=$OLD_IFS
  # find ${OUT_PATH}  -name "*.sql" -exec sed -i 's/ *$//g' {} \;
  rm -f ${WORKFILE}

!!getName.sql
  --1ページの行数 0の場合はページヘッダ等を全て非表示にする
  set pagesize 0 
  --1行のバイト数。これを超える場合は SET WRAPの設定に従う
  set linesize 32767 
  --1行の幅を超えた場合に折り返す
  set wrap on 
  --問合せ結果の件数を表示しない
  set feedback off 
  --LONG、CLOB、NCLOBのチャンクサイズ
  set LONGCHUNKSIZE 500
  --LONG、CLOB、NCLOB の表示
  set long 2000000 
  -- 行末のスペースを削除
  SET TRIMS ON
  
  VAR objecttype    VARCHAR2(30)
  exec :objecttype := '&1'
  VAR owner    VARCHAR2(30)
  exec :owner := '&2'
  
  spool &3
  SELECT 
     OBJECT_NAME
  FROM
     DBA_OBJECTS
  WHERE
          OWNER = :owner
     AND  OBJECT_TYPE = :objecttype
  ORDER BY OBJECT_NAME;
  spool off;
  
  exit;

!!getObject.sql
  --1ページの行数 0の場合はページヘッダ等を全て非表示にする
  set pagesize 0 
  --1行のバイト数。これを超える場合は SET WRAPの設定に従う
  set linesize 32767 
  --1行の幅を超えた場合に折り返す
  set wrap on 
  --問合せ結果の件数を表示しない
  set feedback off 
  --LONG、CLOB、NCLOBのチャンクサイズ
  set LONGCHUNKSIZE 2048
  --LONG、CLOB、NCLOB の表示
  set long 20000000
  -- 行末のスペースを削除
  SET TRIMS ON
  
  VAR objecttype    VARCHAR2(30)
  exec :objecttype := '&1'
  VAR owner    VARCHAR2(30)
  exec :owner := '&2'
  VAR objectname    VARCHAR2(50)
  exec :objectname := '&3'
  
  -- 最後にセミコロンを付ける
  EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
  -- STORAGE属性を省略
  EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE );
  -- セグメント属性を省略
  EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE );
  -- キー情報は出力しない
  -- EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS', FALSE );
  -- キー情報はalter tableにする
  EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE );
  -- スキーマ名を省略
  EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'EMIT_SCHEMA', FALSE);
  
  spool &4
  SELECT 
     DBMS_METADATA.GET_DDL(
     CASE 
     WHEN OBJECT_TYPE = 'DATABASE LINK' THEN 'DB_LINK'
     ELSE OBJECT_TYPE
     END ,
     OBJECT_NAME, OWNER) DDL
  FROM
     DBA_OBJECTS
  WHERE
          OWNER = :owner
     AND  OBJECT_TYPE = :objecttype
     AND  OBJECT_NAME = :objectname;
  spool off;
  
  exit;

!!getTablespace.sql
  --1ページの行数 0の場合はページヘッダ等を全て非表示にする
  set pagesize 0 
  --1行のバイト数。これを超える場合は SET WRAPの設定に従う
  set linesize 32767 
  --1行の幅を超えた場合に折り返す
  set wrap on 
  --問合せ結果の件数を表示しない
  set feedback off 
  --LONG、CLOB、NCLOBのチャンクサイズ
  set LONGCHUNKSIZE 2048
  --LONG、CLOB、NCLOB の表示
  set long 20000 
  -- 行末のスペースを削除
  SET TRIMS ON
  
  spool &1
  SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TABLESPACE_NAME) AS DDL FROM DBA_TABLESPACES
  ORDER BY TABLESPACE_NAME;
  
  spool off;
  
  exit;

!!getUser.sql
  --1ページの行数 0の場合はページヘッダ等を全て非表示にする
  set pagesize 0 
  --1行のバイト数。これを超える場合は SET WRAPの設定に従う
  set linesize 32767 
  --1行の幅を超えた場合に折り返す
  set wrap on 
  --問合せ結果の件数を表示しない
  set feedback off 
  --LONG、CLOB、NCLOBのチャンクサイズ
  set LONGCHUNKSIZE 2048
  --LONG、CLOB、NCLOB の表示
  set long 20000 
  -- 行末のスペースを削除
  SET TRIMS ON
  
  VAR objectname    VARCHAR2(50)
  exec :objectname := '&1'
  
  spool &2
  SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) AS DDL FROM dba_users
  where USERNAME = :objectname
  ORDER BY USERNAME;
  
  
  select 'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE || case when ADMIN_OPTION = 'YES' then ' WITH ADMIN OPTION' end case
  from dba_sys_privs
  where GRANTEE = :objectname
  order by GRANTEE;
  
  
  select 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || case when ADMIN_OPTION = 'YES' then ' WITH ADMIN OPTION' end case
  from dba_role_privs
  where GRANTEE = :objectname
  order by GRANTEE;
  
  spool off;
  
  exit;

!!getUsersName.sql
  --1ページの行数 0の場合はページヘッダ等を全て非表示にする
  set pagesize 0 
  --1行のバイト数。これを超える場合は SET WRAPの設定に従う
  set linesize 32767 
  --1行の幅を超えた場合に折り返す
  set wrap on 
  --問合せ結果の件数を表示しない
  set feedback off 
  --LONG、CLOB、NCLOBのチャンクサイズ
  set LONGCHUNKSIZE 2048
  --LONG、CLOB、NCLOB の表示
  set long 20000
  -- 行末のスペースを削除
  SET TRIMS ON
  
  spool &1
  SELECT USERNAME FROM dba_users
  ORDER BY USERNAME;
  spool off;
  
  exit;

!!setenvora.sh
  export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib/:${LD_LIBRARY_PATH}
  #export NLS_LANG=JAPANESE_JAPAN.JA16SJIS
  export NLS_LANG=Japanese_Japan.UTF8
  export ORACLE_SID=XE


!!!以下参考
!!各オブジェクト
  set pagesize 0
  set linesize 200
  set feedback off
  
  VAR objecttype    VARCHAR2(30)
  exec :objecttype := 'VIEW' --TABLEとか取得したいオブジェクトのタイプを指定する
  
  SELECT 
     DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME, OWNER) DDL
  FROM
     ALL_OBJECTS
  WHERE
          OWNER = :owner
     AND  OBJECT_TYPE = :objecttype
     --AND  OBJECT_NAME = :objectname;


!!表領域
  SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TABLESPACE_NAME) AS DDL FROM DBA_TABLESPACES
  ORDER BY TABLESPACE_NAME;

!!ユーザ情報
  SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) AS DDL FROM dba_users
  ORDER BY USERNAME;

!!ユーザの権限
 select * from dba_sys_privs
 select * from dba_role_privs
以下のような感じで権限付与のSQLを作成できる
 select 'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE || case when ADMIN_OPTION = 'YES' then ' WITH ADMIN OPTION' end case
 from dba_sys_privs
 where grantee = 'hoge'
 
 select 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || case when ADMIN_OPTION = 'YES' then ' WITH ADMIN OPTION' end case
 from dba_role_privs
 where grantee = 'hoge'

!!!以下、古い情報
!!テーブル
  set pagesize 0
  set linesize 200
  set feedback off
  
  VAR owner    VARCHAR2(10)
  exec :owner := 'USER'
  
  -- テーブル
  spool src/TABLE.sql
  SELECT 
     TO_CHAR(DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME, OWNER)) DDL
  FROM
     ALL_TABLES
  WHERE
     OWNER = :owner
  ORDER BY TABLE_NAME;
  spool off;
  
  exit;

!!インデックス
  set pagesize 0
  set linesize 200
  set feedback off
  
  VAR owner    VARCHAR2(10)
  exec :owner := 'USER'
  
  -- インデックス
  spool src/INDEX.sql
  SELECT 
     TO_CHAR(DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME, OWNER)) DDL
  FROM
     ALL_INDEXES
  WHERE
     OWNER = :owner
  ORDER BY INDEX_NAME;
  spool off;
  
  exit;

!!ビュー
  set pagesize 0
  set linesize 200
  set feedback off
  
  VAR owner    VARCHAR2(10)
  exec :owner := 'USER'
  
  -- ビュー
  spool src/VIEW.sql
  SELECT 
     TO_CHAR(DBMS_METADATA.GET_DDL('VIEW', VIEW_NAME, OWNER)) DDL
  FROM
     ALL_VIEWS
  WHERE
     OWNER = :owner
  ORDER BY VIEW_NAME;
  spool off;
  
  exit;

!!シーケンス
  set pagesize 0
  set linesize 200
  set feedback off
  
  VAR owner    VARCHAR2(10)
  exec :owner := 'USER'
  
  -- シーケンス
  spool src/SEQUENCE.sql
  SELECT 
     TO_CHAR(DBMS_METADATA.GET_DDL('SEQUENCE', SEQUENCE_NAME, SEQUENCE_OWNER)) DDL
  FROM
     ALL_SEQUENCES
  WHERE
     SEQUENCE_OWNER = :owner
  ORDER BY SEQUENCE_NAME;
  spool off;
  
  exit;

!!DB LINK
  set pagesize 0
  set linesize 200
  set feedback off
  
  VAR owner    VARCHAR2(10)
  exec :owner := 'USER'
  
  -- DB LINK
  spool src/DB_LINK.sql
  SELECT 
     TO_CHAR(DBMS_METADATA.GET_DDL('DB_LINK', DB_LINK, OWNER)) DDL
  FROM
     ALL_DB_LINKS
  WHERE
     OWNER = :owner
  ORDER BY DB_LINK;
  spool off;
  
  exit;

!!PROCEDURE
  set pagesize 0
  set linesize 200
  set feedback off
  set LONGCHUNKSIZE 200
  set long 20000
  
  VAR owner    VARCHAR2(10)
  exec :owner := 'USER'
  
  -- Procedure 
  spool src/PROCEDURE.sql
  SELECT 
     DBMS_METADATA.GET_DDL('PROCEDURE', OBJECT_NAME, OWNER) DDL
  FROM
     ALL_PROCEDURES
  WHERE
     OWNER = :owner
     and OBJECT_TYPE='PROCEDURE'
  ORDER BY PROCEDURE_NAME;
  spool off;
  
  exit;

!!FUNCTION
  set pagesize 0
  set linesize 200
  set feedback off
  set LONGCHUNKSIZE 200
  set long 20000
  
  VAR owner    VARCHAR2(10)
  exec :owner := 'USER'
  
  -- Function
  spool src/FUNCTION.sql
  SELECT 
     DBMS_METADATA.GET_DDL('FUNCTION', OBJECT_NAME, OWNER) DDL
  FROM
     ALL_PROCEDURES
  WHERE
     OWNER = :owner
     and OBJECT_TYPE='FUNCTION'
  ORDER BY PROCEDURE_NAME;
  spool off;
  
  exit;

!!!参考
http://oracle.se-free.com/ddl/A1_get_ddl.html

 DBMS_METADATA.GET_DDL( オブジェクトタイプ ,　オブジェクト名 , [ スキーマ ] )

SQL> set long 2000
SQL> set heading off
SQL>
SQL> select
　2　　 dbms_metadata.get_ddl('TABLE','EMP')
　3 　　from dual;

CREATE TABLE "TEST"."EMP"
( 　 "EMP_ID" CHAR(5),
　　"DEPT_ID" CHAR(5),
　　"EMP_NAME" VARCHAR2(10),
　　"EMP_DATE" DATE,
　　"SALARY" NUMBER(5,0),
PRIMARY KEY ("EMP_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

{{category2 データベース,Oracle}}