My Blog

Pluggable database and the 32k VARCHAR2!

by admin on 22nd October 2013 No comments

So yesterday we had a customer who is running in our DBaaS platform ask for us to extend the VARCHAR2 to 32k. This has been pretty well covered off by other people

1) change the MAX_STRING_SIZE parameter to extended in the spfile
2) restart the database in upgrade mode
3) Run $ORACLE_HOME/rdbms/admin/utl32k.sql
4) Job Done!

But how does a container/pluggable database configuration alter things? Well, as you can see below… it needs doing for each Pluggable database..

Having done this, and going for a database restart we immediately see an error

SQL> STARTUP;
ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size              2299656 bytes
Variable Size           2063599864 bytes
Database Buffers      3271557120 bytes
Redo Buffers              7274496 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14696: MAX_STRING_SIZE migration is incomplete for pluggable database PDB$SEED
Process ID: 26612
Session ID: 237 Serial number: 5

So, even if you don’t have any running PDBS, you’re still going to need to upgrade the seed…

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size              2299656 bytes
Variable Size           2063599864 bytes
Database Buffers      3271557120 bytes
Redo Buffers              7274496 bytes
Database mounted.
Database opened.
SQL> alter session set container=PDB$SEED;

Session altered.

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

Session altered.

<Results Truncated for brevity>

Now what about the Pluggable database. This one is a little easier

1) Start the database (container database) normally
2) For each pluggable database open it in upgrade mode
3) Run the $ORACLE_HOME/rdbms/admin/utl32k.sql
4) close the pluggable database and open it read write
5) That’s it

SQL> alter pluggable database ola open upgrade;

Pluggable database altered.

SQL> alter session set container = ola;

Session altered.

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

Session altered.

adminPluggable database and the 32k VARCHAR2!

Join the conversation