Extended Data Type in Oracle 12c
Oracle 12c introduced extended data types, in which, VARCHAR2, NVARCHAR2, and RAW data types can store more data. Before 12c, there was a restriction as 4000 bytes for the VARCHAR2 and NVARCHAR2 data types, and 2000 bytes for the RAW data type.Now, this size limitation increased by 32767 bytes for the VARCHAR2, NVARCHAR2, and RAW data types.
Steps to enable Extended Data Type
Step 1: Close PDB
Step 2: Open PDB in Upgrade mode
Step 3: Change init parameter max_string_size to “extended”
Step 4: Run utl32k.sql script to make data dictionary changes at system level
Step 5: Close PDB
Step 6: Open PDB in read write mode
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
- 64bit Production
SQL> alter session set container=DB12C;
Session altered.
SQL> show parameter max_string_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string STANDARD
SQL>
SQL>
SQL>
SQL> create table char_test(c1 varchar2(32767));
create table char_test(c1 varchar2(32767))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string STANDARD
SQL>
SQL>
SQL>
SQL> create table char_test(c1 varchar2(32767));
create table char_test(c1 varchar2(32767))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype
SQL>
max_string_size default value is standard, hence one cannot crate table with varchar2(32767).
Let's change max_string_size to extended by following above steps.
Step 1: Close
PDB
SQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
Pluggable database altered.
Step 2: Open
PDB in Upgrade mode
SQL> ALTER PLUGGABLE DATABASE OPEN UPGRADE;
Pluggable database altered.
Step 3: Change
init parameter max_string_size to “extended”
SQL> ALTER SYSTEM SET max_string_size=extended;
System altered.
Step 4: Run
utl32k.sql script to make data dictionary changes at system level
SQL> @?/rdbms/admin/utl32k
SP2-0042: unknown command "aRem" - rest of line
ignored.
Session altered.
DOC>#######################################################################
DOC>#######################################################################
DOC> The
following statement will cause an "ORA-01722: invalid number"
DOC> error if
the database has not been opened for UPGRADE.
DOC>
DOC> Perform a
"SHUTDOWN ABORT" and
DOC> restart
using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The
following statement will cause an "ORA-01722: invalid number"
DOC> error if
the database does not have compatible >= 12.0.0
DOC>
DOC> Set
compatible >= 12.0.0 and retry.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
PL/SQL procedure successfully completed.
Session altered.
0 rows updated.
Commit complete.
System altered.
PL/SQL procedure successfully completed.
Commit complete.
System altered.
Session altered.
Session altered.
Table created.
Table created.
Table created.
Table truncated.
0 rows created.
PL/SQL procedure successfully completed.
STARTTIME
--------------------------------------------------------------------------------
09/25/2018 16:01:44.423000000
PL/SQL procedure successfully completed.
No errors.
PL/SQL procedure successfully completed.
Session altered.
Session altered.
0 rows created.
no rows selected
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The
following statement will cause an "ORA-01722: invalid number"
DOC> error if
we encountered an error while modifying a column to
DOC> account
for data type length change as a result of enabling or
DOC> disabling
32k types.
DOC>
DOC> Contact
Oracle support for assistance.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Commit complete.
Package altered.
Package altered.
Step 5: Close
PDB
SQL> ALTER PLUGGABLE
DATABASE CLOSE;
Pluggable database
altered.
Step 6: Open
PDB in read write mode
SQL> ALTER PLUGGABLE
DATABASE OPEN;
Pluggable database altered.
Let's check parameter and create table with VARCHAR2(32767)
SQL> show parameter max_string_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string EXTENDED
SQL>
SQL> create table char_test(c1 varchar2(32767));
Table created.
SQL>
SQL> desc char_test;
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 VARCHAR2(32767)
That's it...
You can now use extended data type.
Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert
