Posted by Most people are now aware that in 12c, a VARCHAR2 has been extended from a maximum size of 4000 to 32767 bytes.
Similarly, there’s many a note/blog post etc about needing to set the following parameter to enable it:
SQL> alter system set max_string_size = EXTENDED scope=spfile;
System altered.
and then bounce the database. But its important note that its NOT just as simple as that. Here’s what will happen if you only do that
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 2097152000 bytes
Fixed Size 3047568 bytes
Variable Size 503320432 bytes
Database Buffers 1040187392 bytes
Redo Buffers 13725696 bytes
In-Memory Area 536870912 bytes
Database mounted.
Database opened.
SQL> alter system set max_string_size = EXTENDED;
System altered.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2097152000 bytes
Fixed Size 3047568 bytes
Variable Size 503320432 bytes
Database Buffers 1040187392 bytes
Redo Buffers 13725696 bytes
In-Memory Area 536870912 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14695: MAX_STRING_SIZE migration is incomplete
Process ID: 10924
Session ID: 6 Serial number: 58918
SQL> startup upgrade
SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0
Unsafe to proceed
ORA-03114: not connected to ORACLE
Now that all looks quite alarming, because your database looks like its in a bad way. But its not. (As documented) you also need to run a migration script when enabling the extended sizes. Here’s what you should be doing:
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 2097152000 bytes
Fixed Size 3047568 bytes
Variable Size 503320432 bytes
Database Buffers 1040187392 bytes
Redo Buffers 13725696 bytes
In-Memory Area 536870912 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utl32k.sql
[snip]
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2097152000 bytes
Fixed Size 3047568 bytes
Variable Size 503320432 bytes
Database Buffers 1040187392 bytes
Redo Buffers 13725696 bytes
In-Memory Area 536870912 bytes
Database mounted.
Database opened.
and the job is done
Learning is not a spectator sport 
2 comments