Introduction
One of the coolest features that were introduced in Oracle 12c (in version 12.1.0.1) is the Extended Data Types which makes the maximum size limit of VARCHAR2, NVARCHAR2 and RAW data types to be 32767 bytes.
In earlier releases (i.e. prior Oracle 12c) the maximum size limit for VARCHAR2, NVARCHAR2 data types was 4000 bytes and for RAW data types was 2000 bytes.
How to enable extended data types in Oracle 12c?
In order to enable support for extended data types, you will have to perform the following steps:
- SHUTDOWN IMMEDIATE;
- STARTUP UPGRADE;
- ALTER SYSTEM SET MAX_STRING_SIZE = EXTENDED; (instead of STANDARD)
- @$ORACLE_HOME/rdbms/admin/utl32k.sql (execute it as SYSDBA)
- Restart the Oracle instance
Conclusion
Before Oracle 12c in order to support strings longer than 4000 bytes we mainly used LOB. If you decide to enable this feature you will need to take into account that internally Oracle is storing these extended types in out-of-line LOB segments but you can't manage these lob segments using DBMS_LOB as Oracle is managing them internally.
From my point of view I think this feature can be useful although its biggest disadvantage is that it requires downtime to enable it which can be problematic for many Oracle customers.
No comments:
Post a Comment