Extended Datatypes-Datatypes
Prior to Oracle 12c, the maximum length allowed for VARCHAR2 and NVARCHAR2 datatypes was 4000 bytes, and 2000 bytes for the RAW datatype. Starting with Oracle 12c, these datatypes can be configured to store up to 32,767 bytes. Listed next are the steps for enabling extended datatypes for a non-container (see Chapter 2 for a definition of the types of databases), single instance database. These steps must be performed as SYS:
$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup upgrade;
SQL> alter system set max_string_size=extended;
SQL> @?/rdbms/admin/utl32k.sql
SQL> shutdown immediate;
SQL> startup;
If you’re working in a container database, here are the steps to enable extended datatypes:
$ sqlplus / as sysdba
SQL> ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;
SQL> shutdown immediate;
SQL> startup upgrade;
SQL> exit;
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS –force_pdb_mode ‘UPGRADE’ -d $ORACLE_HOME/rdbms/admin -l ‘/home/oracle/ utlrp_cdb_pdbs_output’ -b utlrp_cdb_pdbs_output utl32k.sql
$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup;
Note Refer to the Oracle Database Reference guide for complete details on implementing extended datatypes for all types of databases (single instance, container, RAC, and Data Guard Logical Standby).
Once you’ve modified the MAX_STRING_SIZE to EXTENDED, you cannot modify the value back to the default (of STANDARD). It’s a one-way change. If you need to switch back, you will have to perform a recovery to a point in time before the change was made— meaning you’ll need RMAN backups (taken prior to the change) or have the Flashback Database enabled. You can also take a Data Pump export from a database with extended datatypes enabled and import into a database without extended datatypes enabled with the caveat that any tables with extended columns will fail on the import.
After enabling the extended datatype, you can create a table with an extended column, as follows:
$ sqlplus eoda/foo@PDB1
SQL> create table t(et varchar2(32727)) tablespace users;Table created.
If you describe the table, it will show the large definition:
SQL> desc tName Null? Type
You can manipulate the extended VARCHAR2 column via SQL just as you would a nonextended column, for example:
SQL> insert into t values(rpad(‘abc’,10000,’abc’));
SQL> select substr(et,9500,10) from t where UPPER(et) like ‘ABC%’;
The extended datatype is internally implemented as a LOB. Assuming that the T table is created in a schema not containing any other objects, you’ll get the following when querying USER_OBJECTS:
SQL> select object_name, object_type from user_objects;OBJECT_NAME OBJECT_TYPE
You can further verify the LOB segment details by querying USER_LOBS:
SQL> select table_name, column_name, segment_name, tablespace_name, in_row
2 from user_lobs where table_name=’T’;TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME IN_
You have no direct control over the LOB associated with the extended column. This means that you cannot manipulate the underlying LOB column with the DBMS_LOB package. Also, the internal LOB associated with the extended datatype column is not visible to you via DBA_TAB_COLUMNS or COL$.
The LOB segment and associated LOB index are always stored in the tablespace of the table that the extended datatype was created in. Following normal LOB storage rules, Oracle stores the first 4000 bytes inline within the table. Anything greater than 4000 bytes is stored in the LOB segment. If the tablespace that the LOB is created in is using Automatic Segment Space Management (ASSM), then the LOB is created as a SecureFiles LOB; otherwise, it is created as a BasicFiles LOB.
Note See the “LOB Types” section later in this chapter for a discussion on in-row storage and the technical aspects of SecureFiles and BasicFiles.
Your SQL access to any data stored in the extended-column LOB segment is transparently handled by Oracle. This has some interesting implications. For example, you can successfully select data stored in an extended column via a database link. This bit of code selects (via a database link) from a table named T in a remote database named OCE:
SQL> select substr(et, 9000,10) from t@OCE;SUBSTR(ET,9000,10)
Why is that important? Consider what happens when a table is created in the remoteOCE database with a column defined with a LOB datatype:
SQL> create table c(ct clob);Table created.
Oracle throws an error if you attempt to select from the LOB column remotely over a database link:
SQL> select * from c@REMDB;
ERROR:
ORA-22992: cannot use LOB locators selected from remote tables
You can also perform set operation comparisons (UNION, UNION ALL, MINUS,INTERSECT) on extended columns, for example:
SQL> select et from t minus select et from t;
Whereas if you tried to compare two LOB columns via a set operator, Oracle returns an error:
SQL> select ct from c minus select ct from c; select ct from c minus select ct from
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected – got CLOB
The prior examples demonstrate that you have more flexibility working with anextended datatype than you would if working directly with a LOB column. Therefore, ifyou have an application that deals with character data greater than 4000 bytes but lessthan or equal to 32,727 bytes, then you may want to consider using extended datatypes.
Also, if you’re migrating from a non-Oracle database (that supports large charactercolumns) to an Oracle database, the extended datatype feature will help make thatmigration easier, as you can now define large sizes for VARCHAR2, NVARCHAR2, and RAWcolumns natively in Oracle.