Post
Ask Question

Look At Extended Data Types In Oracle 12c

In this article, we will look into a new feature of Oracle 12c known as extended data types, which will help to increase the maximum size of VARCHAR2, NVARCHAR2 and RAW columns in a table. Prior to Oracle 12c, we have the limits, given below, on the maximum size of a column:

VARCHAR2 : 4000 bytes
NVARCHAR2 : 4000 bytes
RAW : 2000 bytes

In Oracle 12c, we can have a maximum limit of 32767 bytes for the data types, mentioned above.

VARCHAR2 : 32767 bytes
NVARCHAR2 : 32767 bytes
RAW : 32767 bytes

If NVARCHAR2’s character set is AL16UTF16, it would occupy 2 bytes for a character. Thus, we can store maximum of 2000 characters.

Let’s create a table in Oracle 12c in SQL Developer and understand it.

table

As we can see, it won’t allow us to create an Address column with the size of 4500. We can fix it by either reducing its size or changing its data type to NCLOB.

In Oracle 12c, we can use the extended data types to store more data in NVARCHAR2 columns.

Let’s run the script, given below, as SYSDBA in SQLPLUS to do it to enable this feature:

script

Here, we are shutting down DB and running in upgrade mode to set max_string_size parameter, as extended. The default value for this parameter is STANDARD.

If you get the error, shown below,while setting max_string_size:

error

We can use the command, given below:

command

After setting the parameter, we can confirm the same by running the show parameter command, given below:

command

Once we enable extended data types, we cannot switch back to the STANDARD mode and its one-way operation.

We enabled the extended data types. Let’s create the table, given below, with a NVARCHAR2 column with a maximum limit as 16000 characters [almost 32000 bytes]:

code

Output

Output

A few points worth notable regarding extended data types are:

  1. VARCHAR2 and NVARCHAR2 columns come with a declared size greater than 4000 bytes are considered the extended data types.
  2. RAW columns with a declared size greater than 2000 bytes are considered the extended data types.
  3. All the extended data types will be stored out-of-line in LOB segments.
  4. The LOB segments will always be stored in the same tablespace as the associated table.

It is a good practice to use the extended data types, instead of CLOBs for data exceeding 4000 bytes. For more details on this feature, please refer here.

I am ending things here. I hope this article will be helpful for all.


Similar Articles