Lazy DBA: September 2018

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>

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> 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>#

no rows selected

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>#

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.

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.

0 rows created.

no rows selected

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>#

PL/SQL procedure successfully completed.

Commit complete.

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

Friday, September 28, 2018

Extended Data Type in Oracle 12c

Extended Data Type in Oracle 12c