DBA Hub

📋Steps in this guide1/3

MAX_STRING_SIZE parameter in oracle 12c new feature DBACLASS

MAX_STRING_SIZE is a new feature in oracle 12c. With this we can increase limit for maximum size of the string from 4000 to 32765.

oracle clusteringintermediate
by OracleDba
13 views
1

STEPS for converting MAX_STRING_SIZE to EXTENDED:

1. Start database in upgrade mode: 2. Change the value to EXTENDED 3.Run utl32k.sql script: @?/rdbms/admin/utl32k.sql 4.Restart the database: Let’s create a table with length more than 4000.

Code/Command (click line numbers to comment):

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
133
134
135
136
137
138
139
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>STARTUP UPGRADE

ORACLE instance started.

Total System Global Area 1.4663E+10 bytes
Fixed Size 15697000 bytes
Variable Size 1.1878E+10 bytes
Database Buffers 2717908992 bytes
Redo Buffers 51404800 bytes
Database mounted.
Database opened.

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

System altered.

SQL> show parameter MAX_STRING_SIZE

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

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

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.


1524 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.


PL/SQL procedure successfully completed.

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.

shutdown immediate;
startup


SQL> show parameter max_string

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

SQL> create table UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000));

Table created.
2

COMMON ISSUES:

1. If you try to restart the database without running script, got below error. So start the database again in mode and execute script. 2. If you try to set the value to EXTENDED , when database is not in UPGRADE mode. So before running this alter statement, start the database in mode. > CONCLUSION: Once the max_string_size is changed to ,We can not  revert to original value.So before changing the value in critical or production database, Please do thorough testing , As you may hit uninvited bugs. CONCLUSION: Once the max_string_size is changed to ,We can not  revert to original value.So before changing the value in critical or production database, Please do thorough testing , As you may hit uninvited bugs.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area 1.4663E+10 bytes
Fixed Size                 15697000 bytes
Variable Size            1.1878E+10 bytes
Database Buffers         2717908992 bytes
Redo Buffers               51404800 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: 18669
Session ID: 401 Serial number: 16419

SQL> alter system set MAX_STRING_SIZE=EXTENDED;
alter system set MAX_STRING_SIZE=EXTENDED
*
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
3

NOTE: As suggested by one Mark in comment section:

————————————————————- Thanks for this. One other thing you might want to add: If you have items in the recyclebin when you run utl32k.sql, you’ll get: DECLARE * ERROR at line 1: ORA-38301: can not perform DDL/DML over objects in Recycle Bin ORA-06512: at line 121 Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 – 64bit Production With the Automatic Storage Management option Simple fix, purge recyclebin and re-run utl32k.sql: $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue May 12 13:24:55 2020 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 – 64bit Production With the Automatic Storage Management option SQL> purge dba_recyclebin; @?/rdbms/admin/utl32k -Mark —————————————————-

Comments (0)

Please to add comments

No comments yet. Be the first to comment!