まえがき
はじめてsqlldr使った話です。
参考文献
移行先テーブル
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だったり。
with fct as(select level as rn ,chr(level + 64) as str from dual connect by level <= 10) select /*csv*/ s1.* from fct s1;
"RN","STR"
1,"A"
2,"B"
3,"C"
4,"D"
5,"E"
6,"F"
7,"G"
8,"H"
9,"I"
10,"J"
10行が選択されました。
xmlだったり
with fct as(select level as rn ,chr(level + 64) as str from dual connect by level <= 10) select /*xml*/ s1.* from fct s1;
<?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だったり
with fct as(select level as rn ,chr(level + 64) as str from dual connect by level <= 10) select /*json*/ s1.* from fct s1;
{"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行が選択されました。
ディレクトリ作成＆権限付与
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;
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
ファイル確認
[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"
移行先テーブル件数確認
[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実行
[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]$
移行先テーブル件数確認
[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して、外部表切っておしまいか。。
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
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とか吐かれるほうが衝撃でした。。
以上、ありがとうございました。