外部表(EXTERNAL TABLE) の ORACLE_DATAPUMPアクセス・ドライバーを使用して、CTAS(CREATE TABLE AS SELECT) でデータをファイル出力(アンロード)してみる。(Oracle Database)

Oracle Database の 外部表(EXTERNAL TABLE)ですが、一般的な用途としては CSV等のテキストファイルや
Oracle Database の Datapumpファイル を読み取る為に使用することが多いですが、
ORACLE_DATAPUMPアクセス・ドライバではファイル出力(アンロード)も可能です。

 > 16.3 ORACLE_DATAPUMPアクセス・ドライバを使用したデータのアンロードとロード
 > https://docs.oracle.com/cd/F19136_01/sutil/oracle_datapump-access-driver.html#GUID-0B2EC1B2-701D-42ED-874C-47F22F21D847
 > ORACLE_DATAPUMPアクセス・ドライバは、SQLのCREATE TABLE AS SELECT文を使用した
 > 外部表の作成の一環として、ダンプ・ファイルにデータを書き込むことができます。

今回の記事では、この機能を使って CTAS(CREATE TABLE AS SELECT) で dmpファイルを 出力してみるやで。
彡(ﾟ)(ﾟ)

# 1. 環境とデータ準備

VirtualBox の OTN 19c環境(Database Virtual Box Appliance) を使用しました。下記記事参照

 > OTN の VirtualBoxイメージ で Oracle DB 19c環境 を 楽々構築
 > https://qiita.com/ora_gonsuke777/items/b41f37637e59319796b4

データは Swingbench で SOEスキーマをサクっと作成。下記記事なんかが参考になります。

 > SwingBenchの OrderEntry Wizard （oewizard）をコマンドラインモードで実行する
 > https://qiita.com/mon_tu/items/9d890e789f1a71f183a5

```sql
SQL> --検証に使用するデータ
SQL> SELECT COUNT(*) FROM SOE.ORDER_ITEMS;

  COUNT(*)
----------
   4300510
```

# 2. ディレクトリ・オブジェクトの作成＆権限付与

SYSユーザーでPDBに接続して、ディレクトリ・オブジェクトの作成と権限付与を行います。

```sh
export ORACLE_HOME=/u01/app/oracle/product/version/db_1
export PATH=${PATH}:${ORACLE_HOME}/bin
export ORACLE_SID=orclcdb
unset TWO_TASK

sqlplus /nolog
CONNECT SYS/oracle@ORCL AS SYSDBA

CREATE DIRECTORY DIR_EXTERNAL as '/home/oracle/work/external';

GRANT READ, WRITE ON DIRECTORY DIR_EXTERNAL TO SOE;
```

#3. 隠しパラメータ設定(バッドノウハウ……)

上記状態で外部表を作成しようとすると、下記のエラーが発生してしまいます。

```sql
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-39155: error expanding dump file name
"/home/oracle/work/external/ext_order_items.dmp"
ORA-48128: opening of a symbolic link is disallowed
```

今回使用した VirtualBox 19c環境(OTN Database Virtual Box Appliance) のディレクトリ構造(※homeディレクトリが symbolic link)が原因の模様

```sh
[oracle@localhost /]$ pwd
/
[oracle@localhost /]$ ls -la
total 24
dr-xr-xr-x.   1 root   root  138 May 31 14:42 .
dr-xr-xr-x.   1 root   root  138 May 31 14:42 ..
lrwxrwxrwx.   1 root   root    7 May 31 14:30 bin -> usr/bin
：
lrwxrwxrwx.   1 root   root   13 May 31 14:42 home -> /u01/userhome ★コレ
lrwxrwxrwx.   1 root   root    7 May 31 14:30 lib -> usr/lib
：
drwxr-xr-x.   1 root   root  200 May 31 14:39 var
[oracle@localhost /]$
```

MOSドキュメントに似たような事象が有り、隠しパラメータ(_disable_directory_link_check)を設定して再起動します。バッドノウハウ……彡(-)(-)

 > DB Upgrade failed with ORA-20001 when db on ACFS (ドキュメントID 2546612.1) ※要ログイン
 > https://support.oracle.com/epmos/faces/DocumentDisplay?id=2546612.1

```sql
CONNECT SYS/oracle@ORCLDB

ALTER SYSTEM SET "_disable_directory_link_check" = TRUE SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
```

# 4. 外部表のCTAS(CREATE TABLE AS SELECT)によるファイル出力(アンロード)

気を取り直して、外部表をCTASしてファイル出力(アンロード)してみます。
※COMPRESSIONによるDatapump圧縮は有償オプション機能となります。本番ではご注意下さい。

```sql
CREATE TABLE EXT_ORDER_ITEMS
ORGANIZATION EXTERNAL (
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY DIR_EXTERNAL
  ACCESS PARAMETERS (
    COMPRESSION ENABLED MEDIUM
    LOGFILE DIR_EXTERNAL:'ext_order_items_ctas_%p_%a.log'
  )
  LOCATION (
      'ext_order_items_01.dmp'
  )
)
AS
SELECT * FROM ORDER_ITEMS;

Table created.

```

上手く行ったで！ SELECTしてみると。。。

```sql

SQL> SELECT COUNT(*) FROM EXT_ORDER_ITEMS;

  COUNT(*)
----------
   4300510

SQL>
```

SELECT も完了や！ファイルも見てみると……

```sh
[oracle@localhost external]$ pwd
/home/oracle/work/external
[oracle@localhost external]$ ls -la ext_order_items*
-rw-r-----. 1 oracle oinstall 87388160 Nov 20 08:32 ext_order_items_01.dmp
-rw-r--r--. 1 oracle oinstall      164 Nov 20 08:33 ext_order_items_ctas_16530_000.log
[oracle@localhost external]$
```

しっかり出力されてるやね。彡(^)(^)

# 5. 作成された外部表定義を抽出(DBMS_METADATA.GET_DDL)

作成された外部表定義を DBMS_METADATA.GET_DDL で抽出してみます。

```sql
SET LONG 1000000
SET LONGC 100000;
SET LINESIZE 300;
SET PAGESIZE 1000;
SELECT DBMS_METADATA.GET_DDL('TABLE','EXT_ORDER_ITEMS','SOE') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','EXT_ORDER_ITEMS','SOE')
------------------------------------------------------------------------

  CREATE TABLE "SOE"."EXT_ORDER_ITEMS"
   (    "ORDER_ID" NUMBER(12,0) NOT NULL ENABLE,
        "LINE_ITEM_ID" NUMBER(3,0) NOT NULL ENABLE,
        "PRODUCT_ID" NUMBER(6,0) NOT NULL ENABLE,
        "UNIT_PRICE" NUMBER(8,2),
        "QUANTITY" NUMBER(8,0),
        "DISPATCH_DATE" DATE,
        "RETURN_DATE" DATE,
        "GIFT_WRAP" VARCHAR2(20) COLLATE "USING_NLS_COMP",
        "CONDITION" VARCHAR2(20) COLLATE "USING_NLS_COMP",
        "SUPPLIER_ID" NUMBER(6,0),
        "ESTIMATED_DELIVERY" DATE
   )  DEFAULT COLLATION "USING_NLS_COMP"
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY "DIR_EXTERNAL"
      ACCESS PARAMETERS
      ( COMPRESSION ENABLED MEDIUM
    LOGFILE DIR_EXTERNAL:'ext_order_items_ctas_%p_%a.log'
  )
      LOCATION
       ( 'ext_order_items_01.dmp'
       )
    )
   REJECT LIMIT 0
```

内部構造が垣間見えるで！彡(ﾟ)(ﾟ)

# 6. ~~上手く行かなかったこと~~(PARALLELのアンロード) 11/21 追記：上手く行きました

下記コマンド、文法的には正しく通ったんやけど、~~ファイルが一つしか生成されんかった……彡(-)(-)~~ 初期化パラメータの見直しで上手く行きました。

```sql
CREATE TABLE EXT_ORDER_ITEMS
ORGANIZATION EXTERNAL (
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY DIR_EXTERNAL
  ACCESS PARAMETERS (
    COMPRESSION ENABLED MEDIUM
    LOGFILE DIR_EXTERNAL:'ext_order_items_ctas_%p_%a.log'
  )
  LOCATION (
      'ext_order_items_01.dmp'
    , 'ext_order_items_02.dmp'
  )
)
PARALLEL 2
AS
SELECT * FROM ORDER_ITEMS;
```

※11/21追記、検証に使った Database Virtual Box Appliance の初期化パラメータで parallel関連の数値が軒並み1になってました。修正して再実行で上手く行きました。

```sql
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     1
parallel_min_degree                  string      1
parallel_min_percent                 integer     0
parallel_min_servers                 integer     1

↓

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     24
parallel_min_degree                  string      1
parallel_min_percent                 integer     0
parallel_min_servers                 integer     4

[oracle@localhost external]$ ls -la ext_order_items*
-rw-r-----. 1 oracle oinstall 34627584 Nov 20 23:12 ext_order_items_01.dmp
-rw-r-----. 1 oracle oinstall 52772864 Nov 20 23:12 ext_order_items_02.dmp
-rw-r--r--. 1 oracle oinstall       41 Nov 20 23:12 ext_order_items_ctas_10116_000.log
-rw-r--r--. 1 oracle oinstall       41 Nov 20 23:12 ext_order_items_ctas_10118_001.log
[oracle@localhost external]$
```

# 7. まとめ

Datapumpコマンド(expdp/impdp)無しでも Datapump(アンロード) できる！
SQLだけで完結できるので、使い道は色々ありそうやで彡(^)(^)

ORACLE_LOADERアクセス・ドライバでも同様の事ができると、csvファイル出力が捗りそうなんやけどなぁ……彡(ﾟ)(ﾟ)