MAX_STRING_SIZE Parameter in Oracle 19c : In this article we will discuss about MAX_STRING_SIZE Parameter in Oracle 19c. This parameter was introduced in Oracle 12c onwards. MAX_STRING_SIZE controls the maximum size of string size in Oracle database.
By default this parameter value is STANDARD, We can change the value of MAX_STRING_SIZE from STANDARD to EXTENDED, But not from EXTENDED to 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 bytes.
Note:- Once this parameter value is changed to EXTENDED, we can not revert to original STANDARD value. So before changing the value in critical or production database, Please do thorough testing.
With MAX_STRING_SIZE set to STANDARD , if we try to set the length of column more than 4000, then it will throw ORA-00910 error.
SQL> CREATE TABLE "SCOTT"."ORAKLDBA"
2 ("OWNERID" NUMBER(10,0) NOT NULL ENABLE,
3 "DESCRIPTION" NVARCHAR2(10000),
4 "CRITERIA" NVARCHAR2(8000) NOT NULL ENABLE,
5 "START" DATE)
6 ;
"DESCRIPTION" NVARCHAR2(10000),
*
ERROR at line 3:
ORA-00910: specified length too long for its datatype
SQL>
Steps change the value of MAX_STRING_SIZE from STANDARD to EXTENDED :
To increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in a non-CDB:
- Check the value of MAX_STRING_SIZE parameter.
- Purge dba_recyclebin.
- Shut down the database.
- Restart the database in UPGRADE mode.
- Change the setting of MAX_STRING_SIZE to EXTENDED.
- Run the rdbms/admin/utl32k.sql script. You must be connected AS SYSDBA to run the script.
- Restart the database in NORMAL mode.
- Check the value of MAX_STRING_SIZE parameter.
- Run the rdbms/admin/utlrp.sql script to recompile invalid objects. You must be connected AS SYSDBA to run the script.
- Let’s create a table with length more than 4000.
Check the value of MAX_STRING_SIZE parameter :
SQL> show parameter string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
asm_diskstring string
max_string_size string STANDARD
SQL>
Purge dba_recyclebin :
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL>
Shutdown the database and Startup the database in Upgrade mode :
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> STARTUP UPGRADE
ORACLE instance started.
Total System Global Area 734000128 bytes
Fixed Size 9032704 bytes
Variable Size 234881024 bytes
Database Buffers 482344960 bytes
Redo Buffers 7741440 bytes
Database mounted.
Database opened.
SQL>
Change the setting of MAX_STRING_SIZE to EXTENDED :
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>
Run utl32k.sql script :
Run the rdbms/admin/utl32k.sql script. You must be connected AS SYSDBA to run the script.
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>#
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.
0 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.
STARTTIME
--------------------------------------------------------------------------------------------------------------------------------
06/07/2021 17:05:48.280000000
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.
SQL>
Restart the database in NORMAL mode. :
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 734000128 bytes
Fixed Size 9032704 bytes
Variable Size 234881024 bytes
Database Buffers 482344960 bytes
Redo Buffers 7741440 bytes
Database mounted.
Database opened.
SQL>
Check the value of MAX_STRING_SIZE parameter :
SQL> show parameter max_string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string EXTENDED
SQL>
Run the utlrp.sql script :
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2021-06-07 20:10:35
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2021-06-07 20:10:39
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
1
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL>
Let’s create a table with length more than 4000 :
SQL> CREATE TABLE "SCOTT"."ORAKLDBA"
2 ("OWNERID" NUMBER(10,0) NOT NULL ENABLE,
3 "DESCRIPTION" NVARCHAR2(10000),
4 "CRITERIA" NVARCHAR2(8000) NOT NULL ENABLE,
5 "START" DATE)
6 ;
Table created.
SQL>
I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You can write to mail me through : orakldba@gmail.com.