반응형
■ [2025-06-21] MAX_STRING_SIZE parameters (12cR1 New features)

 

[2025-06-21] MAX_STRING_SIZE parameters (12cR1 New features) (12cr2에서 테스트)

 

[실습 환경]

cat > make_orainfo.sh <<'EOF'
#!/bin/bash
echo "========================================"
echo "        Oracle/OS Info Summary"
echo "========================================"
echo -n "Hostname     : "; hostname
echo -n "IP Address   : "; hostname -i
echo -n "OS Name      : "; grep -i pretty_name /etc/os-release | cut -d= -f2 | tr -d '"'
echo -n "Kernel Ver   : "; uname -r
echo -n "DB Version   : "
$ORACLE_HOME/bin/sqlplus -S / as sysdba <<EOQ | head -1
SET TIMING OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET ECHO OFF
SELECT banner FROM v\$version WHERE rownum=1;
EXIT
EOQ
echo "<Patch Info>"
$ORACLE_HOME/OPatch/opatch lspatches
echo "========================================"
EOF
sh make_orainfo.sh

========================================
        Oracle/OS Info Summary
========================================
Hostname     : tdb01p
IP Address   : 192.168.0.41
OS Name      : Oracle Linux Server 7.9
Kernel Ver   : 5.4.17-2102.201.3.el7uek.x86_64
DB Version   : Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
<Patch Info>
33561275;OJVM RELEASE UPDATE 12.2.0.1.220118 (33561275)
33678030;OCW JAN 2022 RELEASE UPDATE 12.2.0.1.220118 (33678030)
33587128;Database Jan 2022 Release Update : 12.2.0.1.220118 (33587128)

OPatch succeeded.
========================================

 

MAX_STRING_SIZE 파라미터는 Oracle 12cr1에서 도입

MAX_STRING_SIZE는 Oracle 데이터베이스에서 문자열 크기의 최대 크기를 제어
STANDARD 또는 Extended로 설정할 수 있음. 기본값은 STANDARD

 

MAX_STRING_SIZE = STANDARD means the maximum size of strings is 4000 bytes for VARCHAR2 and NVARCHAR2
MAX_STRING_SIZE = EXTENDED means maximum size can be upto 32767 byte .

 

MAX_STRING_SIZE의 값을 표준에서 확장으로 변경할 수 있음. 하지만 확장에서 표준으로 변경할 수는 없음.
MAX_STRING_SIZE를 STANDARD로 설정하면 열의 길이를 4000 이상으로 설정하면 ORA-00910 오류가 발생

 

max_string_size가 Extended로 변경되면 원래 표준 값으로 되돌릴 수 없음
따라서 데이터베이스의 값을 변경하기 전에, 철저한 테스트를 수행해야함

 

[PTDB:oracle@tdb01p][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'

[PTDB:oracle@tdb01p][/home/oracle]$ ss

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 28 00:39:02 2025

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[2025-06-28:00:39:02][tdb01p]<SYS@PTDB> SHOW PARAMETER MAX_STRING_

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
max_string_size                      string                            STANDARD
[2025-06-28:00:39:02][tdb01p]<SYS@PTDB> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[PTDB:oracle@tdb01p][/home/oracle]$ alias st
alias st='rlwrap sqlplus tuner/oracle'
[PTDB:oracle@tdb01p][/home/oracle]$ st

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 28 00:39:16 2025

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Thu Jun 19 2025 21:56:39 +09:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[2025-06-28:00:39:16][tdb01p]<TUNER@PTDB> CREATE TABLE TB_MAX_STRING_SIZE_TEST ( MAX_STRING VARCHAR2(8000));
CREATE TABLE TB_MAX_STRING_SIZE_TEST ( MAX_STRING VARCHAR2(8000))
                                                           *
ERROR at line 1:
ORA-00910: specified length too long for its datatype


Elapsed: 00:00:00.01

[PTDB:oracle@tdb01p][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[PTDB:oracle@tdb01p][/home/oracle]$ ss

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 28 00:40:25 2025

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[2025-06-28:00:40:25][tdb01p]<SYS@PTDB> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[2025-06-28:00:40:25][tdb01p]<SYS@PTDB> startup upgrade;
ORACLE instance started.

Total System Global Area 5184159744 bytes
Fixed Size                  8631432 bytes
Variable Size            1946159992 bytes
Database Buffers         3221225472 bytes
Redo Buffers                8142848 bytes
Database mounted.
Database opened.

 

[2025-06-28:00:40:25][tdb01p]<SYS@PTDB> alter system set MAX_STRING_SIZE='EXTENDED' SCOPE=BOTH;
--> 이 설정을 할때는 반드시 upgrade mode로 디비가 기동되어 있어야함
--> 그렇지 않으면 아래의 에러 발생
--ORA-02097: parameter cannot be modified because specified value is invalid
--ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration

System altered.

Elapsed: 00:00:00.14


[2025-06-28:00:40:25][tdb01p]<SYS@PTDB> show parameter MAX_STRING_SIZE

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
max_string_size                      string                            EXTENDED

[2025-06-28:00:48:28][tdb01p]<SYS@PTDB> @?/rdbms/admin/utl32k.sql
--> utl32k.sql 을 실행하지 않으면 db가 기동되지 않음
--> 아래의 에러가 나게됨
--ORA-00603: ORACLE server session terminated by fatal error
--ORA-01092: ORACLE instance terminated. Disconnection forced
--ORA-14695: MAX_STRING_SIZE migration is incomplete
--utl32k.sql을 실행할 때 recyclebin에 항목이 있으면 다음과 같은 결과를 얻을 수 있습니다:
--ORA-38301: can not perform DDL/DML over objects in Recycle Bin
--ORA-06512: at line 121
--Simple fix, purge recyclebin and re-run utl32k.sql:

--SP2-0042: unknown command "aRem" - rest of line ignored.

Session altered.

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

Elapsed: 00:00:00.00
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.

Elapsed: 00:00:00.00

Session altered.

Elapsed: 00:00:00.00

0 rows updated.

Elapsed: 00:00:00.02

Commit complete.

Elapsed: 00:00:00.00

System altered.

Elapsed: 00:00:00.06

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

Commit complete.

Elapsed: 00:00:00.00

System altered.

Elapsed: 00:00:00.04

Session altered.

Elapsed: 00:00:00.01

Session altered.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.00

Table truncated.

Elapsed: 00:00:00.01

0 rows created.

Elapsed: 00:00:00.00

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

STARTTIME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
06/28/2025 00:48:35.689545000


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
No errors.

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

Session altered.

Elapsed: 00:00:00.00

Session altered.

Elapsed: 00:00:00.00

0 rows created.

Elapsed: 00:00:00.01

no rows selected

Elapsed: 00:00:00.00

no rows selected

Elapsed: 00:00:00.00
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.

Elapsed: 00:00:00.01

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

Commit complete.

Elapsed: 00:00:00.00

Package altered.

Elapsed: 00:00:00.43

Package altered.

Elapsed: 00:00:00.30

[2025-06-28:00:48:28][tdb01p]<SYS@PTDB> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[2025-06-28:00:48:28][tdb01p]<SYS@PTDB> startup
ORACLE instance started.

Total System Global Area 5184159744 bytes
Fixed Size                  8631432 bytes
Variable Size            1946159992 bytes
Database Buffers         3221225472 bytes
Redo Buffers                8142848 bytes
Database mounted.
Database opened.


[PTDB:oracle@tdb01p][/home/oracle]$ alias st
alias st='rlwrap sqlplus tuner/oracle'
[PTDB:oracle@tdb01p][/home/oracle]$ st

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 28 00:50:48 2025

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sat Jun 28 2025 00:39:16 +09:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[2025-06-28:00:50:48][tdb01p]<TUNER@PTDB> CREATE TABLE TB_MAX_STRING_SIZE_TEST ( MAX_STRING VARCHAR2(8000));

Table created.

Elapsed: 00:00:00.08
[2025-06-28:00:50:48][tdb01p]<TUNER@PTDB> insert into TB_MAX_STRING_SIZE_TEST values ('1');

1 row created.

Elapsed: 00:00:00.01
[2025-06-28:00:50:48][tdb01p]<TUNER@PTDB> commit;

Commit complete.

Elapsed: 00:00:00.01
[2025-06-28:00:50:48][tdb01p]<TUNER@PTDB> create index idx_TB_MAX_STRING_SIZE_TEST_01 on TB_MAX_STRING_SIZE_TEST(MAX_STRING);
create index idx_TB_MAX_STRING_SIZE_TEST_01 on TB_MAX_STRING_SIZE_TEST(MAX_STRING)
                                               *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
--> 인덱스를 만들 수 없음

Elapsed: 00:00:00.01


[2025-06-28:00:50:48][tdb01p]<TUNER@PTDB> drop table TB_MAX_STRING_SIZE_TEST purge;

Table dropped.

Elapsed: 00:00:00.24

[2025-06-28:00:50:48][tdb01p]<TUNER@PTDB> CREATE TABLE TB_MAX_STRING_SIZE_TEST ( MAX_STRING VARCHAR2(6398));

Table created.

Elapsed: 00:00:00.02
[2025-06-28:00:50:48][tdb01p]<TUNER@PTDB> insert into TB_MAX_STRING_SIZE_TEST values ('1');

1 row created.

Elapsed: 00:00:00.01
[2025-06-28:00:50:48][tdb01p]<TUNER@PTDB> commit;

Commit complete.

Elapsed: 00:00:00.01
[2025-06-28:00:50:48][tdb01p]<TUNER@PTDB> create index idx_TB_MAX_STRING_SIZE_TEST_01 on TB_MAX_STRING_SIZE_TEST(MAX_STRING);
create index idx_TB_MAX_STRING_SIZE_TEST_01 on TB_MAX_STRING_SIZE_TEST(MAX_STRING)
                                               *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded


Elapsed: 00:00:00.02


[2025-06-28:00:50:48][tdb01p]<TUNER@PTDB> CREATE TABLE TB_MAX_STRING_SIZE_TEST ( MAX_STRING VARCHAR2(4001));

Table created.

Elapsed: 00:00:00.02
[2025-06-28:00:50:48][tdb01p]<TUNER@PTDB> insert into TB_MAX_STRING_SIZE_TEST values ('1');

1 row created.

Elapsed: 00:00:00.00
[2025-06-28:00:50:48][tdb01p]<TUNER@PTDB> commit;

Commit complete.

Elapsed: 00:00:00.01
[2025-06-28:00:50:48][tdb01p]<TUNER@PTDB> create index idx_TB_MAX_STRING_SIZE_TEST_01 on TB_MAX_STRING_SIZE_TEST(MAX_STRING);

Index created.

Elapsed: 00:00:00.02
[2025-06-28:00:50:48][tdb01p]<TUNER@PTDB>
explain plan for
select *
  from TB_MAX_STRING_SIZE_TEST
where MAX_STRING = '1';

Explained.

Elapsed: 00:00:00.00

[2025-06-28:00:50:48][tdb01p]<TUNER@PTDB> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2270638305

---------------------------------------------------------------------------------------------------
| Id  | Operation        | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                                |     1 |  2002 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_TB_MAX_STRING_SIZE_TEST_01 |     1 |  2002 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("MAX_STRING"='1')
       filter(INTERNAL_FUNCTION("MAX_STRING"))

14 rows selected.

Elapsed: 00:00:00.02

 

--6389 사이즈부터 인덱스 생성 가능

DROP TABLE TB_MAX_STRING_SIZE_TEST;

 

CREATE TABLE TB_MAX_STRING_SIZE_TEST (MAX_STRING VARCHAR2(6389));

 

create index idx_TB_MAX_STRING_SIZE_TEST_01 on TB_MAX_STRING_SIZE_TEST(MAX_STRING);

 

explain plan for
select *
  from TB_MAX_STRING_SIZE_TEST
where MAX_STRING = '1';

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                                                      
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1270725689                                                                                            
                                                                                                                       
---------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                           | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | 
---------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                    |                                |     1 |  3219 |     1   (0)| 00:00:01 | 
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TB_MAX_STRING_SIZE_TEST        |     1 |  3219 |     1   (0)| 00:00:01 | 
|*  2 |   INDEX RANGE SCAN                  | IDX_TB_MAX_STRING_SIZE_TEST_01 |     1 |       |     1   (0)| 00:00:01 | 
---------------------------------------------------------------------------------------------------------------------- 
                                                                                                                       
Predicate Information (identified by operation id):                                                                    
---------------------------------------------------                                                                    
                                                                                                                       
2 - access("MAX_STRING"='1')                                                                                           
filter(INTERNAL_FUNCTION("MAX_STRING"))                                                                                

15 rows selected.

SQL Execution Time > 00:00:00.016
Total Elapsed Time > 00:00:00.031

반응형

'Oracle > Etc' 카테고리의 다른 글

[2026-01-06] Oracle 12cR2 to 19c 환경에서 DB Replay 테스트 (ACFS 사용 및 ACFS 사용을 위한 커널 변경 절차 포함)  (0) 2026.01.07
[2025-03-04] Oracle Restart Hostname 변경 (12cR2)  (1) 2025.08.05

+ Recent posts

티스토리툴바