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を変更します。