はじめてsqlldr使った話

#まえがき
はじめてsqlldr使った話です。

#参考文献
[SQL*Loader Express Modeと外部表](http://cosol.jp/techdb/2013/08/sqlloader_express_mode_and_external_tables.html)

#移行先テーブル

```sql
drop table tbl___loader___ purge;
create table tbl___loader___ (
	rn   number
	, str varchar2(10)
);

desc tbl___loader___;

名前  Nullかどうか タイプ          
--- -------- ------------ 
RN           NUMBER       
STR          VARCHAR2(10) 

```

#CSVファイル作成

>今日Twiiterでみつけたやつ。Twitterは昨日からはじめた。衝撃だった。コメントみたいに書いてsqldeveloperのF5ボタン押すと、コンソール画面にCSV出力される。json,html,xmlもいけるぽい。コピってutf-8のlfで保存。サーバーにおく。csvだったり。

```sql
with fct as(select level as rn ,chr(level + 64) as str from dual connect by level <= 10) select /*csv*/ s1.* from fct s1;
```

```sql
"RN","STR"
1,"A"
2,"B"
3,"C"
4,"D"
5,"E"
6,"F"
7,"G"
8,"H"
9,"I"
10,"J"

10行が選択されました。 
```

>xmlだったり

```sql
with fct as(select level as rn ,chr(level + 64) as str from dual connect by level <= 10) select /*xml*/ s1.* from fct s1;
```


```sql
<?xml version='1.0'  encoding='UTF-8' ?>
<RESULTS>
	<ROW>
		<COLUMN NAME="RN"><![CDATA[1]]></COLUMN>
		<COLUMN NAME="STR"><![CDATA[A]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="RN"><![CDATA[2]]></COLUMN>
		<COLUMN NAME="STR"><![CDATA[B]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="RN"><![CDATA[3]]></COLUMN>
		<COLUMN NAME="STR"><![CDATA[C]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="RN"><![CDATA[4]]></COLUMN>
		<COLUMN NAME="STR"><![CDATA[D]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="RN"><![CDATA[5]]></COLUMN>
		<COLUMN NAME="STR"><![CDATA[E]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="RN"><![CDATA[6]]></COLUMN>
		<COLUMN NAME="STR"><![CDATA[F]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="RN"><![CDATA[7]]></COLUMN>
		<COLUMN NAME="STR"><![CDATA[G]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="RN"><![CDATA[8]]></COLUMN>
		<COLUMN NAME="STR"><![CDATA[H]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="RN"><![CDATA[9]]></COLUMN>
		<COLUMN NAME="STR"><![CDATA[I]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="RN"><![CDATA[10]]></COLUMN>
		<COLUMN NAME="STR"><![CDATA[J]]></COLUMN>
	</ROW>
</RESULTS>
10行が選択されました。 
```

>jsonだったり

```sql
with fct as(select level as rn ,chr(level + 64) as str from dual connect by level <= 10) select /*json*/ s1.* from fct s1;
```

```sql
{"results":[{"columns":[{"name":"RN","type":"NUMBER"},{"name":"STR","type":"VARCHAR2"}],"items":
[
{"rn":1,"str":"A"}
,{"rn":2,"str":"B"}
,{"rn":3,"str":"C"}
,{"rn":4,"str":"D"}
,{"rn":5,"str":"E"}
,{"rn":6,"str":"F"}
,{"rn":7,"str":"G"}
,{"rn":8,"str":"H"}
,{"rn":9,"str":"I"}
,{"rn":10,"str":"J"}
]}]}
10行が選択されました。 


```

#ディレクトリ作成＆権限付与

```sql
CREATE OR REPLACE DIRECTORY dp_in_aine_dir AS '/mnt/18cr3/share/dump/ORCL/aine/in';
GRANT READ ON DIRECTORY dp_in_aine_dir TO aine;
GRANT WRITE ON DIRECTORY dp_in_aine_dir TO aine;
```


```sql
oracle@f285aba0589a in]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 31 19:40:39 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@pdb1> CREATE OR REPLACE DIRECTORY dp_in_aine_dir AS '/mnt/18cr3/share/dump/ORCL/aine/in';

Directory created.

Elapsed: 00:00:00.01
SYS@pdb1> GRANT READ ON DIRECTORY dp_in_aine_dir TO aine;

Grant succeeded.

Elapsed: 00:00:00.02
SYS@pdb1> GRANT WRITE ON DIRECTORY dp_in_aine_dir TO aine;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

```

#ファイル確認

```sql
[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ cd /mnt/18cr3/share/dump/ORCL/aine/in
[oracle@f285aba0589a in]$ ll
total 4
-rw-r--r--. 1 1000 1001 61 Mar 31 19:50 tbl___loader___.dat
[oracle@f285aba0589a in]$ vi tbl___loader___.dat

1,"A"
2,"B"
3,"C"
4,"D"
5,"E"
6,"F"
7,"G"
8,"H"
9,"I"
10,"J"


```

#移行先テーブル件数確認

```sql
[oracle@f285aba0589a in]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 31 20:04:47 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Mar 31 2019 19:52:35 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> select count(*) from tbl___loader___;

     COUNT(*)
-------------
            0

1 row selected.

Elapsed: 00:00:00.01

```

#sqlldr実行

```sql
[oracle@f285aba0589a in]$ sqlldr aine/ORACLE_PWD@pdb1 TABLE=tbl___loader___

SQL*Loader: Release 18.0.0.0.0 - Production on Sun Mar 31 20:05:50 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: TBL___LOADER___
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table TBL___LOADER___:
  10 Rows successfully loaded.

Check the log files:
  tbl___loader___.log
  tbl___loader____%p.log_xt
for more information about the load.
[oracle@f285aba0589a in]$ ll
total 12
-rw-r--r--. 1   1000     1001   61 Mar 31 19:50 tbl___loader___.dat
-rw-r--r--. 1 oracle oinstall 2283 Mar 31 20:05 tbl___loader___.log
-rw-r--r--. 1 oracle oinstall  654 Mar 31 20:05 tbl___loader____18130.log_xt
[oracle@f285aba0589a in]$

```

#移行先テーブル件数確認


```sql
[oracle@f285aba0589a in]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 31 20:06:27 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Mar 31 2019 20:05:50 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> select count(*) from tbl___loader___;

     COUNT(*)
-------------
           10

1 row selected.

Elapsed: 00:00:00.01


desc tbl___loader___;

名前  Nullかどうか タイプ          
--- -------- ------------ 
RN           NUMBER       
STR          VARCHAR2(10) 


```
#ログ確認

>外部表作って、select-insertして、外部表切っておしまいか。。

```sql:tbl___loader___.log

SQL*Loader: Release 18.0.0.0.0 - Production on Sun Mar 31 20:05:50 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: TBL___LOADER___
Data File:      tbl___loader___.dat
  Bad File:     tbl___loader____%p.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      External Table

Table TBL___LOADER___, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
RN                                  FIRST     *   ,       CHARACTER
STR                                  NEXT     *   ,       CHARACTER

Generated control file for possible reuse:
OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
LOAD DATA
INFILE 'tbl___loader___'
APPEND
INTO TABLE TBL___LOADER___
FIELDS TERMINATED BY ","
(
  RN,
  STR
)
End of generated control file for possible reuse.


enable parallel DML: ALTER SESSION ENABLE PARALLEL DML

creating external table "SYS_SQLLDR_X_EXT_TBL___LOADER___"

CREATE TABLE "SYS_SQLLDR_X_EXT_TBL___LOADER___"
(
  "RN" NUMBER,
  "STR" VARCHAR2(10)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY DP_IN_AINE_DIR
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'DP_IN_AINE_DIR':'tbl___loader____%p.bad'
    LOGFILE 'tbl___loader____%p.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," LRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "RN" CHAR(255),
      "STR" CHAR(255)
    )
  )
  location
  (
    'tbl___loader___.dat'
  )
)REJECT LIMIT UNLIMITED

executing INSERT statement to load database table TBL___LOADER___

INSERT /*+ append parallel(auto) */ INTO TBL___LOADER___
(
  RN,
  STR
)
SELECT
  "RN",
  "STR"
FROM "SYS_SQLLDR_X_EXT_TBL___LOADER___"

dropping external table "SYS_SQLLDR_X_EXT_TBL___LOADER___"

Table TBL___LOADER___:
  10 Rows successfully loaded.

Run began on Sun Mar 31 20:05:50 2019
Run ended on Sun Mar 31 20:05:50 2019

Elapsed time was:     00:00:00.24
CPU time was:         00:00:00.02

```

```sql:tbl___loader____18130.log_xt


 LOG file opened at 03/31/19 20:05:50

Total Number of Files=1

Data File: tbl___loader___.dat

Log File: tbl___loader____18130.log_xt



 LOG file opened at 03/31/19 20:05:50

Bad File: tbl___loader____18130.bad

Field Definitions for table SYS_SQLLDR_X_EXT_TBL___LOADER___
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Reject rows with all null fields

  Fields in Data Source:

    RN                              CHAR (255)
      Terminated by ","
      Trim whitespace from left and right
    STR                             CHAR (255)
      Terminated by ","
      Trim whitespace from left and right

```

#あとがき

さらっと使ってみたけど、面白そうだね。sqlldr。12cからtable指定できるポイです。コントロールファイルなくなってスッキリ。コメントでjsonとか吐かれるほうが衝撃でした。。


以上、ありがとうございました。