Datatypes - Expert Oracle Database Architecture - page 523

Extended Datatypes
Prior to Oracle 12 c , the maximum length allowed for VARCHAR2 and NVARCHAR2 datatypes was 4,000 bytes, and
2,000 bytes for the RAW datatype. Starting with Oracle 12 c , these datatypes can be configured to store up to 32,767 bytes.
Listed next are the steps for enabling extended datatypes for a noncontainer (see Chapter 2 for a definition of the
types of databases), single instance database. These steps must be performed as SYS :

SYS@O12CE> shutdown immediate;
SYS@O12CE> startup upgrade;
SYS@O12CE> alter system set max_string_size=extended;
SYS@O12CE> @?/rdbms/admin/utl32k.sql
SYS@O12CE> shutdown immediate;
SYS@O12CE> startup;

■ 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).

Note

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:

EODA@O12CE> create table t(et varchar2(32727)) tablespace users;

Table created.

If you describe the table it will show the large definition:

EODA@O12CE> desc t
Name Null? Type
----------------------------- -------- --------------------
ET VARCHAR2(32727)

You can manipulate the extended VARCHAR2 column via SQL just as you would a nonextended column,
for example:

EODA@O12CE> insert into t values(rpad('abc',10000,'abc'));
EODA@O12CE> select substr(et,9500,10) from t where UPPER(et) like 'ABC%';