data types can store more data. Before 12c, there was a restriction as 4000 bytes for the
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>#######################################################################
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)
