外部表(EXTERNAL TABLE) の ORACLE_DATAPUMPアクセス・ドライバーを使用して、CTAS(CREATE TABLE AS SELECT) でデータをファイル出力(アンロード)してみる。
- 1. 環境とデータ準備
- 2. ディレクトリ・オブジェクトの作成＆権限付与
- 3. 隠しパラメータ設定(バッドノウハウ……)
- 4. 外部表のCTAS(CREATE TABLE AS SELECT)によるファイル出力(アンロード)
- 5. 作成された外部表定義を抽出(DBMS_METADATA.GET_DDL)
- 6. 上手く行かなかったこと(PARALLELのアンロード) 11/21 追記：上手く行きました
- 7. まとめ
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> SELECT COUNT(*) FROM SOE.ORDER_ITEMS; COUNT(*) ---------- 4300510
2. ディレクトリ・オブジェクトの作成＆権限付与
SYSユーザーでPDBに接続して、ディレクトリ・オブジェクトの作成と権限付与を行います。
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. 隠しパラメータ設定(バッドノウハウ……)
上記状態で外部表を作成しようとすると、下記のエラーが発生してしまいます。
* 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)が原因の模様
[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
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圧縮は有償オプション機能となります。本番ではご注意下さい。
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> SELECT COUNT(*) FROM EXT_ORDER_ITEMS; COUNT(*) ---------- 4300510 SQL>
SELECT も完了や！ファイルも見てみると……
[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 で抽出してみます。
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 追記：上手く行きました
下記コマンド、文法的には正しく通ったんやけど、ファイルが一つしか生成されんかった……彡(-)(-) 初期化パラメータの見直しで上手く行きました。
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になってました。修正して再実行で上手く行きました。
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ファイル出力が捗りそうなんやけどなぁ……
彡(ﾟ)(ﾟ)
