Oracle 12c Database Extended Data Types

Regardless of the character set being used in your database, the maximum size for Varchar2, NVarchar2 remains at 4K and 2k for RAW datatype. If you try to create a column exceeding these limits then you will get the error message. This was a limitation but was also a contradiction to the data type limit in PL/SQL which supported 32767 for the same data types. Starting in 12c, Oracle has increased the size for the database ddatatypes Varchar2, NVarchar2 and RAW  to 32K. Oracle calls them Extended Data Types and in this article we will look at how to enable and use them.

Extended Data Types

The default datatype size is still 12c the same as they were in 11g which are:


• Varchar2 = 4000
• NVarchar2 = 4000
• RAW = 2000


If you try to create a table without enabling the Extended data types in 12c which exceeds these datatype size limits, you get the following error.

SQL> create table t1(
2 id number,
3 name varchar2(5000)
4 );
name varchar2(5000)
*
ERROR at line 3:
ORA-00910: specified length too long for its datatype

Oracle 12c Database Extended Data Types

To enable the extended data types you need to set the MAX_STRING_SIZE parameter to Extended. By default it has the Standard values which only allows the sizes above.

SQL> sho parameter max_string_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string STANDARD

Enabling Extended Data Types

To enable the extended data types you need to change the value of MAX_STRING_SIZE from STANDARD to EXTENDED. By setting this value, Oracle may invalidate certain objects in the database which are dependent on the data types involved. So after the parameter value change, you also have to run a script which effectively migrates the data types from the Standard to Extended.


To enable Extended data types for Non-CDB databases – which don’t use the new Container architecture – the process is straightforward. You shutdown the database, start it in upgrade mode, change the parameter value, run the script and then restart the database in normal mode. The sequence of commands will be as below.

SHUTDOWN IMMEDIATE;

STARTUP UPGRADE;

ALTER SYSTEM SET max_string_size=extended;

@?/rdbms/admin/utl32k.sql

SHUTDOWN IMMEDIATE;

STARTUP;


We will now describe in detail the process for PDB databases.


To enable the Extended data types in a PDB, first log into PDB database using the SYSDBA user and close it.

SQL> shutdown immediate;
Pluggable Database closed.


Now open it in upgrade mode.

SQL> alter pluggable database pdb1 open upgrade;
Pluggable database altered.

SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 MIGRATE YES


Now change the parameter value and run the script for object migration.

SQL> alter system set max_string_size=EXTENDED;
System altered.

SQL> @/u01/app/oracle/product/12.1.0/db_1/rdbms/admin/utl32k.sql
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.
PL/SQL procedure successfully completed.

No errors.

Session altered.
PL/SQL procedure successfully completed.
Commit complete.
Package altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2013-07-20 06:59:44

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2013-07-20 06:59:48

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.

...Database user "SYS", database schema "APEX_040200", user# "98" 07:00:45
...Compiled 0 out of 2998 objects considered, 0 failed compilation 07:00:45
...263 packages
...255 package bodies
...453 tables
...11 functions
...16 procedures
...3 sequences
...458 triggers
...1322 indexes
...207 views
...0 libraries
...6 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 07:00:45
...Completed key object existence check 07:00:46
...Setting DBMS Registry 07:00:46
...Setting DBMS Registry Complete 07:00:46
...Exiting validate 07:00:46

PL/SQL procedure successfully completed.

Once its has been completed you can now open the PDB in normal mode and confirm the parameter change.

SQL> shutdown immediate;
Pluggable Database closed.
SQL> startup
Pluggable Database opened.
SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL> sho parameter max_string_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string EXTENDED

To test you can also go ahead and try to create a table with new data types sizes.

SQL> create table t1(
2 id number,
3 name varchar2(5000)
4 );

Table created.

The table has been created with Varchar2 size in access of 4K.

How it Works

In actuality in the background, Oracle uses the LOBs technology to implement these greater sized columns. Any column exceeding 4K in size will be treated as implicit LOB. But these LOBs will be managed by Oracle itself and you cannot perform any actions on them as you can with the normal LOBs.


It is worth noting here, that the fact that although you need Extended data type for RAW in order to have column greater then 2K length but until and unless a RAW column does not exceed 4K in size, it will not be treated as implicit LOB. It will be treated as inline column.

LOBs are normally stored in same tablespace where the table resides. Also the LOBs created will depend on whether you are using ASSM or not. If you are using ASSM then LOBs created will be Securefiles LOBs. However if you are not using ASSM then LOBs created will be Basic Files LOBs. Basic Files LOBs are not recommended and may get deprecated in the future releases.


The LOBs created to support Extended Data Types are adherent to same restrictions as normal LOBs.


Read more on Oracle 12c Database Heat Maps and Automatic Data Optimization.

Leave a Reply

Your email address will not be published.