DDL スキーマの全テーブルをcsvに出力する Oracle11.2.0 以降

LOBなどcsvに落せないテーブルは考慮していない。
当SQLの結果をsqlplusにリダイレクトする。

--
--     ↓ あらかじめcsvを落とすディレクトリを指定する。ここにディレクトリを作ってディレクトリの中にテーブル毎のcsvを出力する
-- cd \log
-- sqlplus user1/pass1@orcl
--
-- history
--   2018/07/25  1行目に列名を入れる処理を追加
--
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS' ;
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SSXFF' ;
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY/MM/DD HH24:MI:SSXFF TZR' ;
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 32767
SET PAGESIZE 0
SET TERMOUT OFF
SET TIMING OFF
SET TRIMSPOOL ON
SET VERIFY OFF
COLUMN CSV  FORMAT A32765
-- SPOOL pass includes CONNECT_IDENTIFIER, USER_NAME and SYSDATE
COLUMN USER_NAME    NEW_VALUE USER_NAME
COLUMN SYSTEM_DATE  NEW_VALUE SYSTEM_DATE
SELECT USER || '_' AS USER_NAME
     , TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS SYSTEM_DATE
FROM DUAL ;
SPOOL table_to_csv_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE..sql
SELECT 'SET ECHO OFF' AS CSV FROM DUAL ;
SELECT 'SET FEEDBACK OFF' AS CSV FROM DUAL ;
SELECT 'SET HEADING OFF' AS CSV FROM DUAL ;
SELECT 'SET LINESIZE 32767' AS CSV FROM DUAL ;
SELECT 'SET PAGESIZE 0' AS CSV FROM DUAL ;
SELECT 'SET TERMOUT OFF' AS CSV FROM DUAL ;
SELECT 'SET TIMING OFF' AS CSV FROM DUAL ;
SELECT 'SET TRIMSPOOL ON' AS CSV FROM DUAL ;
SELECT 'SET VERIFY OFF' AS CSV FROM DUAL ;
SELECT 'SET DEFINE ON' AS CSV FROM DUAL ;
SELECT 'COLUMN CSV  FORMAT A32765' AS CSV FROM DUAL ;
          --  作成したディレクトリ下にcsvを作成する。
SELECT 'HOST mkdir table_to_csv_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE' AS CSV FROM DUAL ;
SELECT DISTINCT 'SPOOL table_to_csv_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE\' 
 || TC.TABLE_NAME || '.csv' || CONST.NL
 || '-- SELECT '  -- 1行目に列名を入れたい時はこの行を有効に
-- ↓ since Oracle11.2.0
 || '''' || LISTAGG(TC.COLUMN_NAME, ',')
        WITHIN GROUP(ORDER BY TC.COLUMN_ID)
        OVER(PARTITION BY TC.TABLE_NAME)
 || ''' FROM DUAL ;' || CONST.NL
 || 'SELECT '
-- ↓ since Oracle11.2.0
 || LISTAGG(CASE
                WHEN TC.DATA_TYPE IN('CHAR', 'VARCHAR', 'VARCHAR2') THEN
                    '''"'' || ' || TC.COLUMN_NAME || ' || ''"'''  -- 文字列はダブルクォーティングする
                WHEN TC.DATA_TYPE = 'NUMBER' THEN
                    'TO_CHAR(' || TC.COLUMN_NAME || ')'
                ELSE
                    TC.COLUMN_NAME
            END, ' || '','' || ')
        WITHIN GROUP(ORDER BY TC.COLUMN_ID)
        OVER(PARTITION BY TC.TABLE_NAME)
 || ' FROM ' || TC.TABLE_NAME || ' ;' || CONST.NL
 || 'SPOOL OFF' AS CSV
FROM USER_TABLES TA
INNER JOIN USER_TAB_COLUMNS TC
ON  TC.TABLE_NAME = TA.TABLE_NAME
WHERE
    TA.DROPPED = 'NO'  -- except trash box, since Oracle10.1.0
;
SELECT 'SET ECHO ON' AS CSV FROM DUAL ;
SELECT 'SET FEEDBACK ON' AS CSV FROM DUAL ;
SELECT 'SET HEADING ON' AS CSV FROM DUAL ;
SELECT 'SET LINESIZE 80' AS CSV FROM DUAL ;
SELECT 'SET PAGESIZE 20' AS CSV FROM DUAL ;
SELECT 'COLUMN CSV  CLEAR' AS CSV FROM DUAL ;
SPOOL OFF

参照Oracle 定数