standingzzz's Oracle memo

OracleSQLチューニングの豊富な経歴を生かして、主に11gと12cのオプティマイザ動作の違いなどについて、実用的な検証結果をメモしています。

ORACLE 新元号予想[集計関数LISTAGG][再帰SQL]

概要

元号一覧をテーブルに登録して、LISTAGG、再帰SQLを用いて
過去の漢字の出現回数を集計、ついでに新元号を予想します。

検証

データ用意
出現回数集計

明治以降の元号についてみると、
一文字は出現回数が二桁の文字を使うのでしょうか。

121119619721

二桁以上の文字はこちらですが、和とか正とか近年の元号
陛下のお名前「明仁」はもちろん、御子息などの「徳仁浩宮)」「文仁礼宮)」「寛仁」
これまでに出現が多過ぎるもの(組み合わせが難しいので15回以上)は除外

29 多
天           27 多
元           27 多
治           21    消
応           20 多
正           19    消
和           19    消
長           19 多
文           19    消
安           17 多
暦           16 多
延           16 多
保           15 多
寛           15    消
徳           15    消
承           1413    消
嘉           1212    消
康           1010

残った四文字からまず一文字。
平成を決めるにあたり、現代人の感覚として、
近年の元号に使われている漢字は避けたと思います。
四文字中、三文字は江戸時代以降に使われています。
はい、一文字は「康」で決まりです。
江戸以降は、家康の康なので使いにくかったのでしょうが、
健康の康です。信長派、秀吉派の反発は無視して、
そろそろ使いどきでしょう。

--- 正平 1346 (成が使われたのは平成が初)           14    --- 承応 165212    --- 嘉永 184810    --- 康正 145510    --- 宝暦 1751

もう一文字は、今までに使われていない文字で、
康が少し画数が多いので、極力画数が少なく、
未来や平和などを意味するような文字。
「未」は画数は少ないけど、なんとなく干支の漢字もボツでしょう。
「好」「栄」「友」「共」・・・
はい、最後は感覚ですね、「栄康」です。決まりました。

Oracle12c PROCEDURE/FUNCTION COMPILE時のORA-04068回避方法

概要

SESSION-AでFUNCTIONをRECOMPILEすると、その以前から接続していたSESSION-Bで、
ORA-04068が発生するというので、調査した際のメモです。

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CentOS Linux release 7.3.1611 (Core) 

検証

ORA-04068の再現テスト

まずSESSION-AでFUNCTIONを作成します。

create table TAB_4068 ( pid number(1), wstr varchar2(256) );
insert into TAB_4068 values ( 1, 'hoge' );
commit;
CREATE OR REPLACE PACKAGE PACK_4068 AS
  ret varchar2(256);
  FUNCTION P4068 RETURN VARCHAR2;
END PACK_4068;
/

CREATE OR REPLACE PACKAGE BODY PACK_4068 AS
  FUNCTION P4068 RETURN VARCHAR2
  IS
  BEGIN
    select wstr into ret from TAB_4068 where pid = 1;
    RETURN ret;
  END;
END PACK_4068;
/

次にSESSION-B側でFUNCTIONを実行してみます。
ここでは正常に実行できることを確認しているだけです。

SQL> DECLARE
  str varchar2(256);
BEGIN
  str := PACK_4068.P4068;
END;
/
  2    3    4    5    6
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>

SESSION-Aで、テーブル定義を変更して、BODYがINVALIDになることを確認し、

SQL> ALTER TABLE TAB_4068 MODIFY ( wstr char(10) );

Table altered.

Elapsed: 00:00:00.13
SQL>
SQL> @object
Enter value for object_name: PACK_4068

OWNER                OBJECT_NAME                    OBJECT_TYPE        CREATED             LAST_DDL_TIME       STATUS
-------------------- ------------------------------ ------------------ ------------------- ------------------- -------
TEST01               PACK_4068                      PACKAGE            2017/12/18 19:19:17 2017/12/18 19:19:17 VALID
TEST01               PACK_4068                      PACKAGE BODY       2017/12/18 19:19:24 2017/12/18 19:19:24 INVALID

2 rows selected.

Elapsed: 00:00:00.25
SQL>

recompileを実行して、VALIDに戻します。

SQL> ALTER PACKAGE PACK_4068 COMPILE BODY;

Package body altered.

Elapsed: 00:00:00.03
SQL>
SQL> @object
Enter value for object_name: PACK_4068

OWNER                OBJECT_NAME                    OBJECT_TYPE        CREATED             LAST_DDL_TIME       STATUS
-------------------- ------------------------------ ------------------ ------------------- ------------------- -------
TEST01               PACK_4068                      PACKAGE            2017/12/18 19:19:17 2017/12/18 19:19:17 VALID
TEST01               PACK_4068                      PACKAGE BODY       2017/12/18 19:19:24 2017/12/18 19:20:58 VALID

そして、SESSION-Bから再度FUNCTIONを実行すると、ORA-04068が発生します。

SQL> DECLARE
  str varchar2(256);
BEGIN
  str := PACK_4068.P4068;
END;
/
  2    3    4    5    6  DECLARE
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "TEST01.PACK_4068" has been invalidated
ORA-04065: not executed, altered or dropped package body "TEST01.PACK_4068"
ORA-06508: PL/SQL: could not find program unit being called: "TEST01.PACK_4068"
ORA-06512: at line 4
ドキュメント確認

https://docs.oracle.com/cd/E18283_01/server.112/e17128/chapter1_2.htm 2.1.1.5 Improvements to Reduce the Occurrences of "Existing State of Packages Discarded" Errors

DBMS_SESSION.RESET_SELFをCALLすると、04068の問題が解決されるようになりました、
とのお話のようですが、

When the body of a stateful package is recompiled by session A, then a different session B which has already instantiated that package will experience the ORA-04068 existing state of packages… has been discarded family of errors on its next reference to that package following the recompilation. The result is severe disruption for the user of session B.

Two improvements in this release now reduce the likelihood of these ORA-04068 errors:

A package, whose global variables is marked constant; and where the initial value can be computed at compile time, is now considered to be stateless.

The new procedure DBMS_SESSION.RESET_SELF(), whose invocation must be in the package of interest, allows you to preempt the problem when it is safe to reinitialize the package state.

The advantage is that packages (and especially those that are not editioned) can be "hot patched" (that is, recompiled) when other sessions are using them with a greatly reduced likelihood of disrupting those sessions.

探してもRESET_SELFというメンバーは存在しません。
代わりに?RESET_PACKAGEというメンバーが存在しますが。

select owner, object_name, OBJECT_TYPE
  from dba_objects
 where object_name like '%DBMS_SESSION%'

OWNER                OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ ------------------
SYS                  DBMS_SESSION                   PACKAGE
SYS                  DBMS_SESSION_STATE             PACKAGE
SYS                  DBMS_SESSION                   PACKAGE BODY
SYS                  DBMS_SESSION_STATE             PACKAGE BODY

SQL> select * from dba_source where name = 'DBMS_SESSION' and text like '%RESET%';

OWNER                NAME             TYPE               LINE TEXT                                                         ORIGIN_CON_ID
-------------------- ---------------- ------------ ---------- ------------------------------------------------------------ -------------
SYS                  DBMS_SESSION     PACKAGE              73   --         same as DBMS_SESSION.RESET_PACKAGE() interface.             1
RESET_PACKAGE

再現テストからの変更点は、BODYに下記を1行追加しただけです。

DBMS_SESSION.RESET_PACKAGE();

どうなったか。

SQL> create table TAB_4068 ( pid number(1), wstr varchar2(256) );
insert into TAB_4068 values ( 1, 'hoge' );
commit;

Table created.

Elapsed: 00:00:00.01
SQL>
1 row created.

Elapsed: 00:00:00.00
SQL>
Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> CREATE OR REPLACE PACKAGE PACK_4068 AS
  ret varchar2(256);
  FUNCTION P4068 RETURN VARCHAR2;
END PACK_4068;
/
  2    3    4    5
Package created.

Elapsed: 00:00:00.02
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY PACK_4068 AS
  FUNCTION P4068 RETURN VARCHAR2
  IS
  BEGIN
    DBMS_SESSION.RESET_PACKAGE();
    select wstr into ret from TAB_4068 where pid = 1;
    RETURN ret;
  END;
END PACK_4068;
/
  2    3    4    5    6    7    8    9   10
Package body created.

Elapsed: 00:00:00.03
SQL>
SQL>
SQL> ALTER TABLE TAB_4068 MODIFY ( wstr char(10) );

Table altered.

Elapsed: 00:00:00.02
SQL>
SQL> @object
Enter value for object_name: PACK_4068

OWNER                OBJECT_NAME                    OBJECT_TYPE        CREATED             LAST_DDL_TIME       STATUS
-------------------- ------------------------------ ------------------ ------------------- ------------------- -------
TEST01               PACK_4068                      PACKAGE            2017/12/18 19:24:36 2017/12/18 19:24:36 VALID
TEST01               PACK_4068                      PACKAGE BODY       2017/12/18 19:24:46 2017/12/18 19:24:46 INVALID

2 rows selected.

Elapsed: 00:00:00.25
SQL>
SQL> ALTER PACKAGE PACK_4068 COMPILE BODY;

Package body altered.

Elapsed: 00:00:00.02
SQL>
SQL> @object
Enter value for object_name: PACK_4068

OWNER                OBJECT_NAME                    OBJECT_TYPE        CREATED             LAST_DDL_TIME       STATUS
-------------------- ------------------------------ ------------------ ------------------- ------------------- -------
TEST01               PACK_4068                      PACKAGE            2017/12/18 19:24:36 2017/12/18 19:24:36 VALID
TEST01               PACK_4068                      PACKAGE BODY       2017/12/18 19:24:46 2017/12/18 19:25:33 VALID

2 rows selected.

Elapsed: 00:00:00.08
SQL>

同じ操作でエラー回避されました。

SQL> DECLARE
  str varchar2(256);
BEGIN
  str := PACK_4068.P4068;
END;
/
  2    3    4    5    6
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>

ただ、DBMS_SESSION.RESET_PACKAGEの詳しいドキュメントが見当たらず、
実際に問題なく使用出来るかは難しいところです。 

追記:ドキュメントありました。

RESET_PACKAGEプロシージャ
このプロシージャは、このセッションのすべてのパッケージのインスタンス化を解除します。
これは、パッケージ状態を解放します。
実行状態をキャッシュするために使用するメモリーは、セッションで実行された
PL/SQLファンクション、プロシージャおよびパッケージに関連付けられています。

パッケージに関して、このメモリーのコレクションはパッケージ変数の現行の値を保持し、
各PL/SQLプログラムによってオープンされたカーソルのキャッシュを制御します。
RESET_PACKAGEへのコールは、以前実行した各PL/SQLプログラムに関連付けられていたメモリーをセッションから解放し、
この結果、グローバルなパッケージの現行の値は消去され、キャッシュされたカーソルはクローズされます。

RESET_PACKAGEは、セッションで失敗したプログラムを確実に再起動するためにも使用できます。
パッケージ変数を含んだプログラムが失敗すると、どの変数を初期化しなおす必要があるかを判別することは困難です。
RESET_PACKAGEは、すべてのパッケージ変数が初期値に再設定されることを保証します。


使用上の注意

すべての実行PL/SQLが消費するメモリーは大量になるため、RESET_PACKAGEを使用して、
データベース・アプリケーション内のある時点でセッション・メモリー・フットプリントを削減できます。
ただし、パッケージ変数値の再設定がアプリケーションに影響を与えないことを確認してください。
また、キャッシュしたメモリーとカーソルのないプログラムを後で実行すると、
解放されたメモリーとカーソルを再作成する必要があるため、実行速度が遅くなることに留意してください。

RESET_PACKAGEは、メモリー、カーソルおよびパッケージ変数をコール直後に解放しません。

たとえば、PL/SQLプロシージャP1がPL/SQLプロシージャP2をコールし、P2がRESET_PACKAGEをコールしたとします。
プロシージャP1の実行が完了するまで(PL/SQLコールが終了するまで)、RESET_PACKAGEの処理は行われません。

単純なローカルテストでは問題無くとも、オンラインで常にCALLされる場合はやはり難しいところがありそうです。

Oracle リストを横に並べて見やすくする[集計関数LISTAGG]

概要

リストが縦にズラーっと出てきて画面内に収まらない。
そんな時にはLISTAGG関数を使うとキレイになります。

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CentOS Linux release 7.3.1611 (Core) 

検証

データ用意

某野球チーム軍の選手リストを用意しました。

SQL> select * from tab_baseball;

REG_TYPE   UNI_NUMBER PLAYER_NAME   LEFTRI CAREER                                              DRAFT   
---------- ---------- ------------- ------ ------------------------------------------------ ----------------
監督               73 藤田 元司    右右   西条北高-慶應大-日本石油-巨人           
投手               11 斎藤 雅樹    右右   市立川口高                                       1982①
投手               17 槙原 寛己    右右   大府高                                           1981①
投手               18 桑田 真澄    右右   PL学園高                                         1985①
投手               19 吉田 修司    左左   滝高-北海道拓殖銀行                             1988①
投手               21 宮本 和知    左左   下関工業高-川崎製鉄水島                         1984③
投手               25 谷口 功一    右右   天理高                                           1991①
投手               28 広田 浩章    右左   萩商業高-NTT四国                                1985②
投手               30 橋本 清      右右   PL学園高                                         1987①
投手               31 水野 雄仁    右右   池田高                                           1983①
投手               47 木田 優夫    右右   日大明誠高                                       1986①
投手               48 香田 勲男    右左   佐世保工高                                       1983②
投手               59 石毛 博史    右右   市立銚子高                                       1988外           
捕手                9 村田 真一    右右   滝川高                                           1981⑤
捕手               22 中尾 孝義    右右   滝川高-専修大-プリンスホテル-中日           
捕手               22 大久保 博元  右右   水戸商業高-西武         
内野手              0 川相 昌弘    右右   岡山南高                                         1982④
内野手              5 岡崎 郁      右左   大分商高                                         1979③
内野手              6 篠塚 和典    右左   銚子商高                                         1975①
内野手             10 駒田 徳広    左左   桜井商高                                         1980②
内野手             27 福王 昭仁    右左   日大三高-明治大-                               1985⑤
内野手             32 大野 雄次    右右   君津商業高-専修大(中退)-川崎製鉄千葉-大洋           
内野手             35 四條 稔      左左   東海大甲府高-三菱自動車川崎                     1988④
内野手             37 元木 大介    右右   上宮高                                           1990①
内野手             38 勝呂 博憲    右右   千葉商業高-日本通運                             1986⑤
内野手             44 緒方 耕一    右両   熊本工業高                                       1986⑥
内野手             50 後藤 孝志    右左   中京高                                           1987②
外野手              7 吉村 禎章    左左   PL学園高                                         1981③
外野手              8 原 辰徳      右右   東海大相模高-東海大                             1980①
外野手             24 大森 剛      右左   高松商業高-慶應大                               1989①
外野手             29 西岡 良洋    右右   田辺高-西武           
外野手             49 L.モスビー    右左   MLB・デトロイト           
外野手             52 井上 真二    右右   熊本工業高                                       198433 rows selected.

Elapsed: 00:00:00.01
SQL>
懐かしい

背番号1桁の選手名を抽出してみます。

  1  select uni_number
  2       , substr(PLAYER_NAME, 1, instr(PLAYER_NAME, ' ', 1, 1) - 1) player_xing
  3    from tab_baseball where uni_number < 10
  4*  order by 1
SQL> /

UNI_NUMBER PLAYER_XING
---------- ------------
         0 川相
         5 岡崎
         6 篠塚
         7 吉村
         89 村田

6 rows selected.

Elapsed: 00:00:00.01
SQL>
ドラフト外は除いたリスト

最近はテスト生から這い上がって活躍する選手もいますね。

  1  select reg_type
  2       , uni_number
  3       , substr(PLAYER_NAME, 1, instr(PLAYER_NAME, ' ', 1, 1) - 1) player_xing
  4    from tab_baseball
  5   where draft is not null
  6*  order by 1, 2
SQL> /

REG_TYPE   UNI_NUMBER PLAYER_XING
---------- ---------- ------------
内野手              0 川相
内野手              5 岡崎
内野手              6 篠塚
内野手             10 駒田
内野手             27 福王
内野手             35 四條
内野手             37 元木
内野手             38 勝呂
内野手             44 緒方
内野手             50 後藤
外野手              7 吉村
外野手              8 原
外野手             24 大森
外野手             52 井上
投手               11 斎藤
投手               17 槙原
投手               18 桑田
投手               19 吉田
投手               21 宮本
投手               25 谷口
投手               28 広田
投手               30 橋本
投手               31 水野
投手               47 木田
投手               48 香田
投手               59 石毛
捕手                9 村田

27 rows selected.

Elapsed: 00:00:00.00
SQL>

しかし、内外野ピッチャーキャッチャー合わせて27名。
こんなに見辛くては藤田監督に怒られちゃいますね。

LISTAGGの利用

LISTAGG関数で選手情報を横に並べます。

  1  select reg_type
  2       , listagg( '[' || uni_number || ',' || substr(PLAYER_NAME, 1, instr(PLAYER_NAME, ' ', 1, 1) - 1) || ']' )
  3         within group( order by uni_number ) player_list
  4    from tab_baseball
  5   where draft is not null
  6   group by
  7         reg_type
  8   order by
  9*        reg_type
SQL> /

REG_TYPE   PLAYER_LIST
---------- ------------------------------------------------------------------------------------------------------------------------
内野手     [0,川相][5,岡崎][6,篠塚][10,駒田][27,福王][35,四條][37,元木][38,勝呂][44,緒方][50,後藤]
外野手     [7,吉村][8,原][24,大森][52,井上]
投手       [11,斎藤][17,槙原][18,桑田][19,吉田][21,宮本][25,谷口][28,広田][30,橋本][31,水野][47,木田][48,香田][59,石毛]
捕手       [9,村田]

4 rows selected.

Elapsed: 00:00:00.00
SQL>

これなら監督も落ち着いて先発オーダーを決められるでしょう。

Oracle12c ヒント句による実行計画の制御[ORDERED, LEADING, USE_NL, INDEX, OPT_PARAM]

概要

SQLが望ましくない実行計画となっている場合に、
ヒント句により結合順などを制御する例をあげます。

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CentOS Linux release 7.3.1611 (Core) 

検証

結合テスト用オブジェクト、データ用意

市場名と業種名はマスタ化して、メインテーブルには各コードを保持します。

実行計画の制御

stoke_nameが「東京」で始まる企業の、市場/業種を集計するSQLを例とします。


全部をメモリ上に読み込んで、HASH JOIN、MERGE JOINでガシャッという感じですね。
まあ、テーブル件数も少ないので、単体ではこの方が速いのは事実です。
しかし本番運用では多数のSQLが流れるので、こういった実行計画は
好ましくないことがほとんどです。という理屈をつけて、実行計画を制御します。


orderedヒント句は、SQLのテーブル記述順でどうですか、というだけなので、
結合順の選択肢が1つしかなく、その順序では実行計画が成り立たないと判断されると、
オプティマイザはヒント句を無視するので、効かないこともしばしば。
ここでは意図あってテーブルを結合順に並べているので、ヒント句は効きました。


今回の場合、orderedと同じ結合順をleadingに変えているだけなので、
意味合いは全く同じです。当然、実行計画も同じ。
orderedでもleadingでも、結合順はヒント句が効きましたが、
オプティマイザはやはりHASH JOINしたいようです。(間違いではありません)


しかし、本番システム上で1000多重で実行することを考えて、use_nlヒント句により、
結合方法をNestedLoopに変えて見ました。
まとめてメモリ上に読み込まずに、ループしながら必要な件数ずつ読み込むことで、
メモリリソースの使用を抑える意図です。
あとは、せっかく用意したstock_name用のインデックス、idx01_tab_stockを使って欲しいところ。
東京%の条件で、24件まで絞り込めるので。※この想定はオプティマイザには難しい部分

SQL> select count(*) from tab_stock where stock_name like '東京%';

  COUNT(*)
----------
        24

indexヒント句を入れると、これも何とも素直に効いてくれました。

access("S"."STOCK_NAME" LIKE '東京%')

今回のヒント句を入れたものが、必ずしも最適解というわけではありませんが、
オンラインで多数実行される場合に、メモリリソースを抑えるようなケースでは、
このような実行計画が無難なものとなりますね。


もう1つはおまけで。

opt_paramヒント句で、このSQL実行時のoptimizer_index_cost_adjの値を1(デフォルトは100)
に変更してみると、あらら。
leadingもuse_nlも無しで、この実行計画になりました。

Oracle12c 接続中のPDBをSQLPLUSからSQLで確認する

概要

11gまでは接続先を確認する場合、v$instance見て、show userして、
という具合で良かったのですが、12cでPDBに繋いでいる場合の
確認方法がサッと出てこなかったので、メモです。

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
CentOS Linux release 7.3.1611 (Core) 

確認

接続先instanceを確認します。

  1  select instance_name
  2      , host_name
  3      , startup_time
  4      , status
  5*   from v$instance
SQL> /

INSTANCE_NAME        HOST_NAME              STARTUP_TIME          STATUS
-------------------- ------------------------------ ------------------------- ------------
orcl             oracle12c.test         2017/09/18 11:34:49       OPEN

接続userを確認します。

SQL> show user
USER is "TEST01"

11gまではここまでで良いのですが。

v$pdbsに問い合わせて、接続中のPDBを確認します。

  1  select DBID
  2      , NAME
  3      , OPEN_MODE
  4      , OPEN_TIME
  5*  from v$pdbs
SQL> /

      DBID NAME         OPEN_MODE  OPEN_TIME
---------- -------------------- ---------- ---------------------------------------------------------------------------
3547537298 ORCLPDB2        READ WRITE 17-10-13 17:48:53.484 +09:00

1 row selected.

接続情報を確認するだけなら、これで十分ですね。

Oracle12c SQL*Loader(12.2.0.1)を動かしてみる

概要

こちらでインストールしたSQL*Loaderを動かしてみます。

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CentOS Linux release 7.3.1611 (Core) 

検証

テスト用テーブル作成
ロード用CSVデータ
[standingzzz@apl work]$ wc -l KEN_ALL_ROME_u.CSV
124117 KEN_ALL_ROME_u.CSV
[standingzzz@apl work]$


0060050,北海道,札幌市 手稲区,星置南,HOKKAIDO,SAPPORO SHI TEINE KU,HOSHIOKIMINAMI
0060811,北海道,札幌市 手稲区,前田 一条,HOKKAIDO,SAPPORO SHI TEINE KU,MAEDA 1-JO
0060812,北海道,札幌市 手稲区,前田 二条,HOKKAIDO,SAPPORO SHI TEINE KU,MAEDA 2-JO
0060813,北海道,札幌市 手稲区,前田 三条,HOKKAIDO,SAPPORO SHI TEINE KU,MAEDA 3-JO
コントロールファイル
options ( errors=-1, rows=10000 )
load data
append
into table tab_KEN_ALL_ROME
fields terminated by ','
trailing nullcols
(   
    postal_cd     char(7)
  , pref_jp       char(256)
  , city_jp       char(256)
  , town_jp       char(256)
  , pref_en       char(256)
  , city_en       char(256)
  , town_en       char(256)
)
ロード
$ sqlldr test01@PDB1 control=KEN_ALL_ROME.ctl data=KEN_ALL_ROME_u.CSV log=KEN_ALL_ROME.log bad=KEN_ALL_ROME.bad readsize=100000000 bindsize=100000000
Password:

SQL*Loader: Release 12.2.0.1.0 - Production on Wed Oct 11 19:32:54 2017

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

LRM-00104: '-1' is not a legal integer for 'errors'
SQL*Loader-114: Error in OPTIONS statement
[standingzzz@apl work]$

errors=-1のところで、integerじゃ無いからエラーだと言われました。
確かにマニュアル見るとこのような記述があります。12cから変わったのですね。

エラーを許容しない場合は、ERRORS=0を設定します。
エラーを無制限に許容する場合は、非常に大きい値を指定します。
再ロード errors=2147483648

型はintergerなので、integerのmax+1をあえて設定してみます。

$ sqlldr test01@PDB1 control=KEN_ALL_ROME.ctl data=KEN_ALL_ROME_u.CSV log=KEN_ALL_ROME.log bad=KEN_ALL_ROME.bad readsize=100000000 bindsize=100000000
Password:

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Oct 12 20:40:38 2017

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

SQL*Loader-350: Syntax error at line 1.
Expecting non-negative integer, found "-2147483648".
options ( errors=2147483648, rows=10000 )
                  ^
[standingzzz@apl work]$

エラーになりました。

再ロード errors=2147483647

それではintegerのmaxにしてみます。

$ sqlldr test01@PDB1 control=KEN_ALL_ROME.ctl data=KEN_ALL_ROME_u.CSV log=KEN_ALL_ROME.log bad=KEN_ALL_ROME.bad readsize=100000000 bindsize=100000000
Password:

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Oct 12 20:42:06 2017

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

Path used:      Conventional
Commit point reached - logical record count 10000
Commit point reached - logical record count 20000
Commit point reached - logical record count 30000
Commit point reached - logical record count 40000
Commit point reached - logical record count 50000
Commit point reached - logical record count 60000
Commit point reached - logical record count 70000
Commit point reached - logical record count 80000
Commit point reached - logical record count 90000
Commit point reached - logical record count 100000
Commit point reached - logical record count 110000
Commit point reached - logical record count 120000
Commit point reached - logical record count 124117

Table TAB_KEN_ALL_ROME:
  124117 Rows successfully loaded.

Check the log file:
  KEN_ALL_ROME.log
for more information about the load.
[standingzzz@apl work]$

正常に動きました。まあ、0で良いのでしょうけど。

Oracle12c INDEXがUNUSABLEになるとEXTENT,SEGMENTは削除される

概要

ALTER TABLEなどでINDEXがUNUSABLEとなった場合に、EXTENT、SEGMENTは削除されるという事象の検証(というかメモ)です。 OracleMasterの教科書に載っているような話らしいですね。資格は持っていないので知らないです。

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CentOS Linux release 7.3.1611 (Core) 

検証

テーブル、インデックスの用意

検証用のテーブルとインデックス、データを用意します。

事前確認

まずは、テーブル、インデックスを作成しただけの状態で、関連ディクショナリを確認します。

ALTER INDEX xxx UNUSABLE

次に、インデックスをUNUSABLEに変更してみます。

SQL> alter index IDX1_TAB_UNUSABLE unusable;

Index altered.

Elapsed: 00:00:00.06
SQL>

すると、user_extents,user_segmentsからは削除されました。

ALTER INDEX xxx REBUILD

UNUSABLEとなった、インデックスをREBUILDします。

SQL> alter index IDX1_TAB_UNUSABLE rebuild;

Index altered.

Elapsed: 00:00:00.08
SQL>

extent,segmentも復活しています。

ALTER TABLE xxx MOVE

テーブル圧縮を実行してみます。

SQL> alter table TAB_UNUSABLE move compress for oltp;

Table altered.

Elapsed: 00:00:00.09
SQL>

テーブルをMOVEすると、インデックスはUNUSABLEになるので、当然と言えば当然なのですが、
やはりextent,segmentからは削除されました。