ORACLE MAX_STRING_SIZE 변경

VARCHAR2 의 최대사이즈인 4000byte 를 늘리기 위한 옵션

파라미터 값을 MAX_STRING_SIZE = EXTENDED 로 수정해야한다.

 

 

기본값은 stnadard 로 되어있음.

 

값을 변경하기 위해서는 먼저 shutdown 필요

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

 

업그레이드 모드로 스타트

 

SQL> startup upgrade

ORACLE instance started.

 

Total System Global Area 1593831936 bytes

Fixed Size                  8897024 bytes

Variable Size             922746880 bytes

Database Buffers          654311424 bytes

Redo Buffers                7876608 bytes

Database mounted.

Database opened.

SQL>

 

Max_string_size extended로 변경

 

SQL> alter system set max_string_Size=extended;

 

System altered.

 

SQL>

 

DB 재기동

 

SQL> startup

ORACLE instance started.

 

Total System Global Area 1593831936 bytes

Fixed Size                  8897024 bytes

Variable Size             922746880 bytes

Database Buffers          654311424 bytes

Redo Buffers                7876608 bytes

Database mounted.

ORA-00603: ORACLE server session terminated by fatal error

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-14695: MAX_STRING_SIZE migration is incomplete

Process ID: 18457

Session ID: 237 Serial number: 41399

 

에러 발생

 

참조 : https://www.oraexcel.com/oracle-12cR1-ORA-14695/lang-ko

 

상기 URL 에서 확인해보니 ORA-14695 부분이 문제가 되었고,

$ORACLE_HOME/rdbms/admin 에서 utl32k.sql 을 실행해주어야 한다.

 

DB 종료 후 다시 upgrade mode 로 기동

 

SQL> startup upgrade;

ORACLE instance started.

 

Total System Global Area 1593831936 bytes

Fixed Size                  8897024 bytes

Variable Size             922746880 bytes

Database Buffers          654311424 bytes

Redo Buffers                7876608 bytes

Database mounted.

Database opened.

 

Utl32k.sql 실행

 

SQL> @utl32k.sql

 

Session altered.

 

 

Session altered.

 

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database has not been opened for UPGRADE.

DOC>

DOC>   Perform a "SHUTDOWN ABORT"  and

DOC>   restart using UPGRADE.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

 

no rows selected

 

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database does not have compatible >= 12.0.0

DOC>

DOC>   Set compatible >= 12.0.0 and retry.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

 

PL/SQL procedure successfully completed.

 

 

Session altered.

 

 

1 row updated.

 

 

Commit complete.

 

 

System altered.

 

 

PL/SQL procedure successfully completed.

 

 

Commit complete.

 

 

System altered.

 

 

Session altered.

 

 

Session altered.

 

 

Table created.

 

 

Table created.

 

 

Table created.

 

 

Table truncated.

 

 

0 rows created.

 

 

PL/SQL procedure successfully completed.

 

 

STARTTIME

--------------------------------------------------------------------------------

10/27/2022 22:38:36.790161000

 

 

PL/SQL procedure successfully completed.

 

No errors.

 

PL/SQL procedure successfully completed.

 

 

Session altered.

 

 

Session altered.

 

 

0 rows created.

 

 

no rows selected

 

 

no rows selected

 

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if we encountered an error while modifying a column to

DOC>   account for data type length change as a result of enabling or

DOC>   disabling 32k types.

DOC>

DOC>   Contact Oracle support for assistance.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

 

PL/SQL procedure successfully completed.

 

 

PL/SQL procedure successfully completed.

 

 

Commit complete.

 

 

Package altered.

 

 

Package altered.

 

 

Session altered.

 

DB 재기동 수행

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area 1593831936 bytes

Fixed Size                  8897024 bytes

Variable Size             922746880 bytes

Database Buffers          654311424 bytes

Redo Buffers                7876608 bytes

Database mounted.

Database opened.

SQL>

 

정상적으로 기동됨을 확인.

 

SQL> show parameter string;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

asm_diskstring                       string

max_string_size                      string      STANDARD

 

32K 를 별도로 돌릴경우에는 앞에 에러난 부분이 동작하지 않는다.

DB를 upgrade mode 로 재기동 한 후 max_String_size 파라미터 변경, utl32k.sql 수행을 다시 해준 후 DB 재기동 한다.

 

 

 

--------------------------------------------------------------------------------------------------------------------

 

Shutdown 후 startup upgrade 수행

 

SQL> startup upgrade

 

Spfile 에 max_string_size 를 extended 로 변경

 

SQL> alter system set max_string_size=extended scope=BOTH;

SQL> purge dba_recyclebin;

SQL> @?/rdbms/admin/utl32k.sql

 

변경 후 shutdown 후 startup

 

 

변경 완료