Longer strings in 12c

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

Advertisements

2 thoughts on “Longer strings in 12c

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s