LoginSignup
8
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@ora_gonsuke777in日本オラクル株式会社

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

Last updated at Posted at 2019-11-20

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ファイル出力が捗りそうなんやけどなぁ……彡(ﾟ)(ﾟ)

8
1
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?