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に適用するのは慎重に検討した方が良さそうです。

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

概要

Part1.の続きです。
マニュアルの記載に沿ってMAX_STRING_SIZEを変更してみます。

^ PDBのVARCHAR2、NVARCHAR2およびRAW列の最大サイズを増加する

1. PDBを停止します。
2. PDBを移行モードで再オープンします。
3. PDBのMAX_STRING_SIZEの設定をEXTENDEDに変更します。
4. PDBでrdbms/admin/utl32k.sqlスクリプトを実行します。
5. PDBをNORMALモードで再度オープンします。

しかし、

MAX_STRING_SIZEの値はSTANDARDからEXTENDEDに変更できます。
ただし、MAX_STRING_SIZEの値をEXTENDEDからSTANDARDには変更できません。

この仕様はちょっと怖いですね。もしも本番DBに適用して、不可避なバグでも踏んでしまったら・・・

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

検証

MAX_STRING_SIZEの変更

^ 1. PDBを停止します。

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

Pluggable database altered.

Elapsed: 00:00:14.71
SQL>

^ 2. PDBを移行モードで再オープンします。

upgrade句をつけてPDBをオープン。

SQL> alter pluggable database pdborcl2 open upgrade;

Pluggable database altered.

Elapsed: 00:00:01.36
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>

^ 3. PDBのMAX_STRING_SIZEの設定をEXTENDEDに変更します。

変更するPDBに切り替えて、

SQL> alter session set container = pdborcl2;

Session altered.

Elapsed: 00:00:00.02
SQL>

alter systemで変更します。

SQL> alter system set MAX_STRING_SIZE = 'EXTENDED' SCOPE = BOTH;

System altered.

Elapsed: 00:00:00.01
SQL>

^ 4. PDBでrdbms/admin/utl32k.sqlスクリプトを実行します。

SQL> @$ORACLE_HOME/rdbms/admin/utl32k

Session altered.

-- ここはあえて詳細は記載しませんが、対象カラムごとに色々とコンバートしているようです

^ 5. PDBをNORMALモードで再度オープンします。

いったんcloseして、今度は普通にopenします。

SQL> alter pluggable database pdborcl2 close;

Pluggable database altered.

Elapsed: 00:00:00.31
SQL>
SQL> alter pluggable database pdborcl2 open;

Pluggable database altered.

Elapsed: 00:00:01.38
SQL>

MAX_STRING_SIZEの確認

変更したPDBにテストユーザで接続して、

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 PDBORCL2                       READ WRITE NO
SQL>

MAX_STRING_SIZEがEXETENDEDに変更されていることを確認出来ました。

SQL> show parameter max_string_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED
SQL>

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

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

概要

「PDBのVARCHAR2の最大サイズを増加する」を検証していきます。
テーブル、インデックスを事前に作成して、カラムのmodify、add、そのカラムに対するインデックスの作成を実施します。
VARCHAR2(4000)やCLOBにインデックス貼りたいという要望は、結構あるのですよね。

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

検証

拡張実施前の準備色々

^ PDBのOPEN、接続確認

-- AS SYSDBA
SQL> alter pluggable database pdborcl2 open;

Pluggable database altered.

Elapsed: 00:00:05.52
SQL>

-- TEST USER
SQL> show con_name

CON_NAME
------------------------------
PDBORCL2
SQL>

^ テーブル作成

後で4001以上にmodifyするために、あらかじめvarchar2のカラムを定義しておきます。

  1  create table tab_vc2_test
  2  (
  3      id         number(5)
  4    , pdate      date
  5    , str1       varchar2(1)
  6  )
  7  partition by range( id )
  8  (
  9      partition p001 values less than ( 1000 )
 10    , partition p002 values less than ( 2000 )
 11    , partition p003 values less than ( 3000 )
 12    , partition p004 values less than ( 4000 )
 13    , partition p005 values less than ( 5000 )
 14    , partition p006 values less than ( 6000 )
 15    , partition pmax values less than ( maxvalue )
 16  )
 17* enable row movement
SQL> /

Table created.

Elapsed: 00:00:00.06
SQL>

SQL> @desc tab_vc2_test
 Name                           Null?    Type
 ------------------------------ -------- ---------------------
 ID                             NOT NULL NUMBER(5)
 PDATE                                   DATE
 STR1                                    VARCHAR2(1)

SQL>

^ プライマリーキー作成

PKはローカルインデックスで。

SQL> create unique index idxp_vc2_test on tab_vc2_test( id ) local;

Index created.

Elapsed: 00:00:00.03
SQL>
SQL> alter table tab_vc2_test add constraints idxp_vc2_test primary key( id );

Table altered.

Elapsed: 00:00:00.05
SQL>

^ テストテーブルへのデータ登録

3000件をINSERTします。

  1  declare
  2      sdate          varchar2(30);
  3      edate          varchar2(30);
  4      insert_rows    number(10);
  5  begin
  6      select to_char( sysdate, 'yyyy/mm/dd hh24:mi:ss' ) into sdate from dual;
  7      insert_rows := 0;
  8      for i in 1..3000 loop
  9          insert into tab_vc2_test values ( i, sysdate, DBMS_RANDOM.STRING( 'a', 1 ) );
 10          insert_rows := insert_rows + 1;
 11          if mod( insert_rows, 100 ) = 0 then
 12              commit;
 13          end if;
 14      end loop;
 15      commit;
 16      select to_char( sysdate, 'yyyy/mm/dd hh24:mi:ss' ) into edate from dual;
 17      DBMS_OUTPUT.PUT_LINE( 'START[' || sdate || '] end[' || edate || ']');
 18  exception
 19      when others then
 20          DBMS_OUTPUT.PUT_LINE( sqlerrm );
 21          rollback;
 22* end;
 23  /
START[2017/08/29 16:08:07] end[2017/08/29 16:08:07]

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.40
SQL>

SQL> select count(*) from tab_vc2_test;

  COUNT(*)
----------
      3000

1 row selected.

Elapsed: 00:00:00.05
SQL>

^ VARCHAR2のカラムにインデックス作成

VARCHAR2カラムを拡張した場合を検証するため、あらかじめインデックスを作成しておきます。

SQL> create index idx1_vc2_test on tab_vc2_test( str1 );

Index created.

Elapsed: 00:00:00.03
SQL>

^ MAX_STRING_SIZEの確認

まだ何も変更していないので、DEFAULTのSTANDARDになっています。

SQL> show parameter max_string_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD
SQL>

^ VARCHAR2(4001)にMODIFY

エラーになるとわかっていますが、確認しておきます。

SQL> alter table tab_vc2_test modify( str1 varchar2(4001) );
alter table tab_vc2_test modify( str1 varchar2(4001) )
                                               *
ERROR at line 1:
ORA-00910: specified length too long for its datatype


Elapsed: 00:00:00.01
SQL>

Part2.では、MAX_STRING_SIZEを変更します。