This parameter was introduced in Oracle 12cMAX_STRING_SIZE controls the maximum size of string size in Oracle database.
Either we can set it to STANDARD or.EXTENDED The default value is 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 .

We can change the value of MAX_STRING_SIZE from STANDARD to EXTENDED. But not from EXTENDED to STANDARD.

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.

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:

4.Restart the database:

Let’s create a table with length more than 4000.

COMMON ISSUES:

1. If you try to restart the database without running utl32k.sql script, got below error.

So start the database again in UPGRADE mode and execute utlk32.sql 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 UPGRADE mode.

CONCLUSION: Once the max_string_size is changed to EXTENDED,We cannot  revert to original STANDARD value.So before changing the value in critical or production database, Please do thorough testing , As you may hit uninvited bugs.

Source

Leave a Reply