OS 환경 : Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c varchar2 최대 길이 32767로 변경
오라클에선 varchar2의 최대길이가 기본적으로 4000임,
하지만 12c 이상부터 max_string_size 파라미터를 expected로 변경해준뒤 utl32k.sql을 실행하는 절차를 수행하면 최대 길이가 32767로 늘어남
본문에서는 이 파라미터 변경 전, 후에 데이터가 어떻게 삽입되는지, 파라미터는 어떻게 변경하는지, 변경시 특이 케이스를 테스트하고 설명함
참고 : https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/MAX_STRING_SIZE.html
테스트
기존 환경에서 4000자 이상 삽입 테스트
max_string_size 파라미터 변경 후 테스트
테스트
기존 환경에서 4000자 이상 삽입 테스트
현재 파라미터 확인
|
1
2
3
4
5
|
SQL> show parameter max_string_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string STANDARD
|
기본값인 standard임
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
drop table test_varchar1 purge;
drop table test_varchar2 purge;
drop table test_varchar3 purge;
create table test_varchar1 (id number, long_text varchar2(4000));
create table test_varchar2 (id number, long_text varchar2(32767));
create table test_varchar3 (id number, long_text varchar2(32768));
*
ERROR at line 3:
ORA-00910: specified length too long for its datatype
|
test_varchar1, test_varchar2 테이블은 정상적으로 생성되지만 varchar2(32768)로 설정한 test_varchar3 테이블은 생성되지 않음
참고로 영문 1글자는 1byte임, lengthb 함수로 확인가능함
|
1
2
3
4
5
|
SQL> select lengthb('A') from dual;
LENGTHB('A')
------------
1
|
먼저 test_varchar1 테이블에 4000자 삽입(pl/sql)
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set serveroutput on
DECLARE
v_str VARCHAR2(4000);
BEGIN
v_str := RPAD('A', 4000, 'A');
INSERT INTO test_varchar1 (id, long_text) VALUES (1, v_str);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
|
정상적으로 삽입됨
데이터 확인
|
1
2
3
4
5
|
SQL> select length(long_text) from test_varchar1;
LENGTH(LONG_TEXT)
-----------------
4000
|
정상적으로 삽입됨
다음 테스트를 위해 truncate
|
1
2
3
|
SQL> truncate table test_varchar1;
Table truncated.
|
test_varchar1 테이블에 4001자 삽입(pl/sql)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
DECLARE
v_str VARCHAR2(4001);
BEGIN
v_str := RPAD('A', 4001, 'A');
INSERT INTO test_varchar1 (id, long_text) VALUES (1, v_str);
COMMIT;
END;
/
ERROR at line 1:
ORA-12899: value too large for column "SYS"."TEST_VARCHAR1"."LONG_TEXT"
(actual: 4001, maximum: 4000)
ORA-06512: at line 5
|
test_varchar1 테이블의 long_text 컬럼의 최대 길이가 4000이기때문에 에러가 발생함
다음 테스트를 위해 truncate
|
1
2
3
|
SQL> truncate table test_varchar1;
Table truncated.
|
먼저 test_varchar1 테이블에 rpad를 이용해 4001자 삽입(sql)
|
1
2
3
|
SQL> insert into test_varchar1 (id, long_text) values (2, rpad('b', 4001, 'b'));
1 row created.
|
4001자를 넣었지만 에러가 안남
데이터 확인
|
1
2
3
4
5
|
SQL> select length(long_text) from test_varchar1;
LENGTH(LONG_TEXT)
-----------------
4000
|
4001개를 넣었는데 실제론 4000개만 들어감
이는 rpad가 varchar2 길이제한 때문에 4000까지 동작하기 때문임
|
1
2
3
4
5
|
SQL> select length(rpad('b', 4001, 'b')) from dual;
LENGTH(RPAD('B',4001,'B'))
--------------------------
4000
|
다음 테스트를 위해 truncate
|
1
2
3
|
SQL> truncate table test_varchar1;
Table truncated.
|
test_varchar2 테이블에 32767자 삽입(pl/sql)
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
DECLARE
v_str VARCHAR2(32767);
BEGIN
v_str := RPAD('C', 32767, 'C');
INSERT INTO test_varchar2 (id, long_text) VALUES (1, v_str);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
|
정상적으로 삽입됨
데이터 확인
|
1
2
3
4
5
6
7
8
|
SQL> select * from test_varchar2;
ID
----------
LONG_TEXT
--------------------------------------
1
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
.....
|
32767 들어감
데이터 확인
|
1
2
3
4
5
|
SQL> select length(long_text) from test_varchar2;
LENGTH(LONG_TEXT)
-----------------
32767
|
* plsql limit은 32767이기 때문에 32767길이까지 삽입됨
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-program-limits.html
* sql limit은 4000
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/datatype-limits.html
다음 테스트를 위해 truncate
|
1
2
3
|
SQL> truncate table test_varchar1;
Table truncated.
|
rpad로 4000자 + 'A'라는 문자 삽입 시도
|
1
2
3
4
5
|
SQL> insert into test_varchar2 values (3, RPAD('A', 4000, 'A')||'A');
insert into test_varchar2 values (3, RPAD('A', 4000, 'A')||'A')
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
|
rpad 4000자 + 'A'를 넣어 4001자를 insert 하려하면 ORA-01489 에러가 발생함
max_string_size 파라미터 변경 후 테스트
max_string_size 파라미터 변경
|
1
2
3
4
5
|
SQL> alter system set max_string_size=extended scope=both;
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration
|
upgrade 모드로 수행하라고 나옴
db 종료 후 upgrade 모드로 기동
|
1
2
3
|
SQL>
shutdown immediate
startup upgrade
|
파라미터 변경
|
1
2
3
|
SQL> alter system set max_string_size=extended scope=both;
System altered.
|
적용 스크립트 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
|
SQL> @?/rdbms/admin/utl32k.sql extended
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.
1578 rows 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.
Session altered.
PL/SQL procedure successfully completed.
STARTTIME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
11/24/2025 15:03:51.635590000
PL/SQL procedure successfully completed.
No errors.
Session altered.
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.
Session altered.
|
일반 모드로 기동
|
1
2
3
|
SQL>
shutdown immediate
startup
|
varchar2의 길이제한이 32767로 늘어남
테스트
test_varchar2 테이블에 4001개 삽입
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> truncate table test_varchar2;
SQL> insert into test_varchar2 values (3, RPAD('A', 4000, 'A')||'A');
1 row created.
#변경전에는 에러가 발생했었음
SQL> insert into test_varchar2 values (3, RPAD('A', 4000, 'A')||'A');
insert into test_varchar2 values (3, RPAD('A', 4000, 'A')||'A')
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
|
정상적으로 삽입됨
rpad 제한 확인
|
1
2
3
4
5
|
SQL> select length(rpad('b', 32768, 'b')) from dual;
LENGTH(RPAD('B',32768,'B'))
---------------------------
32767
|
rpad가 32767까지 동작함, varchar2 길이제한때문
참고1. props$의 MAX_STRING_SIZE 값 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
SQL>
set lines 200 pages 1000
col name for a20
col value$ for a20
col comment$ for a60
select * from props$ where name = 'MAX_STRING_SIZE';
#파라미터 적용전 : STANDARD
NAME VALUE$ COMMENT$
-------------------- -------------------- ------------------------------------------------------------
MAX_STRING_SIZE STANDARD MAX_STRING_SIZE parameter used for dictionary metadata
#max_string_size 파라미터 extended scope=spfile; 후 upgrade 모드로 기동시 : MIGRATING
NAME VALUE$ COMMENT$
-------------------- -------------------- ------------------------------------------------------------
MAX_STRING_SIZE MIGRATING MAX_STRING_SIZE parameter used for dictionary metadata
#utl32k.sql 수행후 : EXTENDED
NAME VALUE$ COMMENT$
-------------------- -------------------- ------------------------------------------------------------
MAX_STRING_SIZE EXTENDED MAX_STRING_SIZE parameter used for dictionary metadata
#일반 모드로 재기동후(동일) : EXTENDED
NAME VALUE$ COMMENT$
-------------------- -------------------- ------------------------------------------------------------
MAX_STRING_SIZE EXTENDED MAX_STRING_SIZE parameter used for dictionary metadata
|
참고2. 파라미터만 변경하고 일반모드로 기동하는 경우
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL> alter system set max_string_size=extended scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1526722880 bytes
Fixed Size 8896832 bytes
Variable Size 369098752 bytes
Database Buffers 1140850688 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: 29012
Session ID: 261 Serial number: 3943
|
에러 발생함
이후 pfile로 다시 파라미터 standard로 변경해도 에러 발생함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
$ vi initoracle19.ora
*.max_string_size=standard
SQL> startup pfile='initoracle19.ora';
ORACLE instance started.
Total System Global Area 1526722880 bytes
Fixed Size 8896832 bytes
Variable Size 369098752 bytes
Database Buffers 1140850688 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: 30873
Session ID: 261 Serial number: 44811
|
에러 발생함
참고3. upgrade 모드가 아닌 일반모드에서 파라미터 변경 후 utl32k.sql을 실행하는 경우
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL> @?/rdbms/admin/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>#
SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance
*
ERROR at line 1:
ORA-01722: invalid number
|
에러 발생함
참고4. upgrade 모드에서 파라미터 변경 후 utl32k.sql을 여러번 실행하는 경우
|
1
2
3
|
SQL> @?/rdbms/admin/utl32k.sql
SQL> @?/rdbms/admin/utl32k.sql
SQL> @?/rdbms/admin/utl32k.sql
|
2번까지는 잘 수행되지만 3번째에 Segmentation fault (core dumped) 메세지가 발생하면서 세션이 끊김
여러번 수행해도 일반모드로 재기동시 extended로 정상적으로 변경됨
이외 특이사항 발견되지 않음
참고5. utl32k.sql 수행시 alert log에는 별다른 로그가 발생하지 않음
|
1
2
|
$ tail -300f alert_oracle19.log
(로그 미발생)
|
결론 :
varchar2의 4000자 제한이 부족한 경우
업그레이드 모드에서 max_string_size 파라미터를 extended로 변경하고 rdbms/admin/utl32k.sql 스크립트를 수행하면
4000자가 아닌 32767자까지 데이터를 넣을수 있음
하지만 재기동이 필요한 만큼 충분히 고려해본뒤 적용하는걸 권장함
참조 :
https://positivemh.tistory.com/1136
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/MAX_STRING_SIZE.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/datatype-limits.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-program-limits.html
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c SQL Plan Management(SPM) 및 Automatic SPM(19.22부터) 무료 (0) | 2025.12.06 |
|---|---|
| 오라클 19c ssl(tls)을 이용한 tns(tcps) 연결 설정 (0) | 2025.12.02 |
| 오라클 19c expdp impdp 상태 n초마다 자동 확인 (0) | 2025.11.24 |
| 오라클 19c 정적 리스너 이용하여 원격지 db에 접속 및 재시작 (0) | 2025.11.22 |
| 오라클 19c opatch debug 방법 (0) | 2025.11.19 |