Sunday, September 27, 2020

ORA-01450: maximum key length exceeded - oracle varchar2 data type support 32000 bytes/characters

Oracle Database 12c onwards the varchar2 data type support 32000 bytes/character.  but by default it is not enabled. you have to do some work to make it happen. 

by default it support only 4000 bytes. 

Here are the steps to implement this - 

  1. Shut down the database.

  2. Restart the database in UPGRADE mode.

  3. Change the setting of MAX_STRING_SIZE to EXTENDED.

  4. Run the rdbms/admin/utl32k.sql script. You must be connected AS SYSDBA to run the script.

  5. Restart the database in NORMAL mode.

Now practically- 


Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\my>sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 16 10:36:19 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL>
SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
         0

PL/SQL Release 12.1.0.2.0 - Production
         0

CORE    12.1.0.2.0      Production
         0


BANNER
--------------------------------------------------------------------------------
    CON_ID
----------
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
         0

NLSRTL Version 12.1.0.2.0 - Production
         0


SQL>
SQL>
SQL> create table test(comments varchar2(30000)) ;
create table test(comments varchar2(30000))
                                    *
ERROR at line 1:
ORA-00910: specified length too long for its datatype



SQL>
SQL>
SQL> show parameter max_string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD

SQL>
SQL>
SQL>
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> STARTUP UPGRADE;
ORACLE instance started.

Total System Global Area 5150605312 bytes
Fixed Size                  3100560 bytes
Variable Size            1094713456 bytes
Database Buffers         4047503360 bytes
Redo Buffers                5287936 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL>
SQL> ALTER SYSTEM SET max_string_size=extended;

System altered.

SQL>
SQL>
SQL> @C:\app12c\oracle\product\12.1.0\dbhome_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.


Package altered.

SQL>
SQL>
SQL>
SQL>
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup ;
ORACLE instance started.

Total System Global Area 5150605312 bytes
Fixed Size                  3100560 bytes
Variable Size            1094713456 bytes
Database Buffers         4047503360 bytes
Redo Buffers                5287936 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create table test(comments varchar2(30000)) ;

Table created.

SQL>
SQL>
SQL> show parameter max_string

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

SQL>
SQL>
SQL>
SQL>
SQL> create table halim(name varchar2(32000)) ;

Table created.


SQL>
SQL>
SQL>
SQL>
SQL>
SQL> show parameter compa

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offload_compaction              string      ADAPTIVE
compatible                           string      12.1.0.2.0
noncdb_compatible                    boolean     FALSE
plsql_v2_compatibility               boolean     FALSE
SQL>
SQL>
SQL>
SQL>
SQL>




For more info- 



No comments: