Oracle Database 12c: Extended Datatypes

The datatypes of varchar2, NVARCHAR2 has increased from 4000 bytes to 32767 bytes in 12c.

After the upgrade if you try to create a table with the new size you can’t do that directly:

create table mousaem.tab1 (id number,text varchar2(32767 byte));

You will receive the below error:

1

To enable this feature:

1.You first need to make sure that the parameter “COMPATIBLE=12.1.0” is added to your init.ora file. usually the initialization parameter file is located under Oracle Home then dbs file system in (unix/linux) environments.

COMPATIBLE=12.1.0

2. Then, add the following parameter in your init.ora file

         max_string_size=EXTENDED

3.Then you need to shut down the database

4.Then startup in “upgrade” mode

2

5.Then execute the script:

@?/rdbms/admin/utl32k.sql

6.Then shutdown the database normally

SQL> shutdown immediate

7.Then “startup” normally and check the parameter

3

Now Try re-creating the table again ………. created successfully!!!!!

4

you can double check it from TOAD or Sql developer:

5

Interesting New Feature 😉

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