Oracle12c Centos7 VARCHAR2のカラム長の拡張を検証 Part3.

概要

Part2.の続きです。
拡張データ型カラムの追加、変更、インデックスの作成を検証します。

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

検証

拡張データ型カラムの追加、変更

^ VARCHAR2(4001)のカラムを新規追加

SQL> alter table tab_vc2_test add( str2 varchar2(4001) );

Table altered.

Elapsed: 00:00:00.23
SQL>
SQL> @desc tab_vc2_test
 Name                           Null?    Type
 ------------------------------ -------- ---------------------
 ID                             NOT NULL NUMBER(5)
 PDATE                                   DATE
 STR1                                    VARCHAR2(1)
 STR2                                    VARCHAR2(4001)

SQL>

問題ありません。

^ 新規追加したVARCHAR2(4001)カラムの、VARCHAR2(32767)への変更

SQL> alter table tab_vc2_test modify( str2 varchar2(32767) );

Table altered.

Elapsed: 00:00:00.06
SQL>
SQL> @desc tab_vc2_test
 Name                           Null?    Type
 ------------------------------ -------- ---------------------
 ID                             NOT NULL NUMBER(5)
 PDATE                                   DATE
 STR1                                    VARCHAR2(1)
 STR2                                    VARCHAR2(32767)

SQL>

これも特に問題ありません。

^ 既存VARCHAR2(1)カラムの、VARCHAR2(4001)への変更

SQL> alter table tab_vc2_test modify( str1 varchar2(4001) );
alter table tab_vc2_test modify( str1 varchar2(4001) )
*
ERROR at line 1:
ORA-01404: ALTER COLUMN will make an index too large


Elapsed: 00:00:00.11
SQL>

カラム変更したらインデックスが大きくなり過ぎるのでエラーとのこと。はい？

拡張データ型カラムへのインデックス作成

^ VARCHAR2(32767)のカラムに新規追加

SQL> create index idx2_vc2_test on tab_vc2_test( str2 );
create index idx2_vc2_test on tab_vc2_test( str2 )
                              *
ERROR at line 1:
ORA-01450: maximum key length (6394) exceeded


Elapsed: 00:00:00.02
SQL>

作成出来ませんでした。
マニュアルには下記の記載はありますが、作成しようとしているのはファンクションインデックスではありません。
拡張データ型にすると、内部的にはファンクション扱いされるということでしょうか。
どうも、DB_BLOCK_SIZEを変更して、Datafileのblocksizeを大きくすると作成出来るようなのですが、
DB作成からになってしまうので、この検証は後にします。

ファンクション索引に関連する仮想列の変更によって索引キーが索引キー長さ制限を超過した場合、ファンクション索引は使用できなくなります。
このような索引の再構築の試行は、「ORA-01450: キーが最大長を超えました」エラーで失敗します。

^ VARCHAR2(4001)のカラムに新規追加

idx1はDROPしてしまいます。

SQL> drop index idx1_vc2_test;

Index dropped.

Elapsed: 00:00:00.24
SQL>

varchar2(1)からvarchar2(4001)に変更します。

SQL> alter table tab_vc2_test modify( str1 varchar2(4001) );

Table altered.

Elapsed: 00:00:00.02
SQL>

varchar2(4001)のカラムstr1に、インデックスを作成してみます。

SQL> create index idx1_vc2_test on tab_vc2_test( str1 );

Index created.

Elapsed: 00:00:00.08
SQL>

varchar2(4001)だと、問題なく作成出来ました。

^ VARCHAR2(6394)のカラムに新規追加

カラム長を6394に変更して、

SQL> alter table tab_vc2_test modify( str1 varchar2(6394) );

Table altered.

Elapsed: 00:00:00.02
SQL>

インデックスを作成してみます。

SQL> create index idx1_vc2_test on tab_vc2_test( str1 );
create index idx1_vc2_test on tab_vc2_test( str1 )
                              *
ERROR at line 1:
ORA-01450: maximum key length (6394) exceeded


Elapsed: 00:00:00.01
SQL>

エラーになりました。
varchar2(4001)のカラムには作成出来たので、拡張データ型が全てダメという訳ではなく、
どういう計算なのでしょうか。

MAX_STRING_SIZEをSTANDARDに変更（ダメ元）

^ PDBを移行モードに変更

PDBをCLOSEします。

SQL> conn / as sysdba
Connected.
SQL>
SQL> alter pluggable database pdborcl2 close;

Pluggable database altered.

Elapsed: 00:00:07.49
SQL>

open upgradeを実行します。

SQL> alter pluggable database pdborcl2 open upgrade;

Pluggable database altered.

Elapsed: 00:00:01.03
SQL>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        READ WRITE NO
         4 PDBORCL2                       MIGRATE    YES
SQL>

^ 試しにalter systemしてみる

SQL> alter session set container = pdborcl2;

Session altered.

Elapsed: 00:00:00.02
SQL>
SQL> alter system set MAX_STRING_SIZE = 'STANDARD' SCOPE = BOTH;
alter system set MAX_STRING_SIZE = 'STANDARD' SCOPE = BOTH
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-14693: The MAX_STRING_SIZE parameter must be EXTENDED.


Elapsed: 00:00:00.01
SQL>

must be EXTENDEDです！と言われてしまいました。

---

MAX_STRING_SIZEを変更して、拡張データ型カラムを作成することは特に難しいことはないものの、
インデックスはあるサイズになると作成出来なかったり、設定は元に戻せないということもあるので、
本番DBに適用するのは慎重に検討した方が良さそうです。