Change VARCHAR2 to 32K – 12c Edition

Oracle has made a few changes to the database to allow organizations to reduce the cost of migrating to Oracle 12c.  One of these changes is with the size limits that have been placed on the VARCHAR2, NVARCHAR2 and RAW data types.  In past versions of Oracle database the maximum size for these data types were 4,000 bytes.  In Oracle 12c, these data types can now  be increased to 32,767 bytes.

In order to set these data types to use the larger setting, the MAX_STRING_SIZE parameter needs to be set.  The only values that this parameter can take is STANDARD and EXTENDED.  Once the parameter has been set to EXTENDED, you cannot go back to STANDARD.

Note: Altering MAX_STRING_SIZE will update database objects and possibly invalidate them

To increase the size of the VARCHAR2, NVARCHAR2 and RAW columns for a non-CDB database,  these steps need to be followed: 

                  1. Shutdown the database

                  2. Restart in UPGRADE mode

                  3. Change the setting of MAX_STRING_SIZE to EXTENDED

                  4. Run $OH/rdbms/admin/utl32k.sql  <- must be connected as SYSDBA

                  5. Restart in NORMAL mode.

The same procedure can be followed and applied against an Oracle PDB, Oracle RAC database, Oracle Data Guard Logical Standby Databases.  

About these ads

3 comments

  1. Reblogged this on Thoughts from James H. Lui and commented:
    Here’s also the My Oracle Support Main 12c Reference Document – Master Note For Oracle Database 12c Release 1 (12.1) Database/Client Installation/Upgrade/Migration Standalone Environment (Non-RAC) [ID 1520299.1]

  2. Carmine Marrone · · Reply

    Have you ever tried this on a non-CDB ? Lot’s of errors on my side. I had to create a CDB before changing the VARCHAR2 limit.

    1. Carmine,

      When I wrote the post, I was using a traditional (non-cdb) 12c database. What errors were you getting?

      Bobby

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

Martins Blog

Trying to explain complex things in simple terms

Oracle Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

PeteWhoDidNotTweet

Stuff that interests me, if not you!

The Oracle Instructor

Explain, Exemplify, Empower

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Oracle Spin - Flimatech Blog

Sharing Our Database Experience

Oracle Scratchpad

Just another Oracle weblog

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Follow

Get every new post delivered to your Inbox.

Join 1,457 other followers

%d bloggers like this: