Join with the other

    311

Subscribe Blog Via Email

Flag Counter

12c Database : No more ORA-01450: maximum key length (3215) exceeded while rebuilding index?

Most of the times when the index key length is bigger you cannot do the rebuild the index online , this is due to the fact that IOT table will be created by Oracle while rebuilding online and the key length is exceeded.

ORA-01450: maximum key length (3215) exceeded

More read on this, http://jonathanlewis.wordpress.com/2009/06/05/online-rebuild/

But this is no more the case with Oracle 12c, Let’s check Create a table with maximum varchar sizes so that the index keys are bigger

11G

	[oracle@oinfo11g tmp]$ sqlplus / as sysdba

	SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 9 16:20:01 2013

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


	Connected to:
	Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
	With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

	SQL> Conn test/test123;
	Connected.

	SQL> create table t1(
	    v1  varchar2(4000),
	    v2  varchar2(2387),
	    v3  varchar2(100)
	) tablespace users;
	
	Table created.

	SQL> create index t1_i1 on t1(v1, v2) tablespace users;

	Index created.

	SQL> SQL> alter index t1_i1 rebuild;

	Index altered.

	SQL> alter index t1_i1 rebuild online;
	alter index t1_i1 rebuild online
	*
	ERROR at line 1:
	ORA-00604: error occurred at recursive SQL level 1
	ORA-01450: maximum key length (3215) exceeded

As you see the rebuild (offline) operation suceeded not the online one, lets check in the 12c Database

12c

	[oracle@oinfo12c ~]$ sqlplus / as sysdba

	SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 13 19:41:02 2013

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

	Connected to:
	Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
	With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

	Connected.

	SQL> show con_name

	CON_NAME
	------------------------------
	CDB$ROOT

	SQL> alter session set container=pdb12c;

	Session altered.


	SQL> conn test/test@//oinfo12c:1521/pdb12c
	Connected.

	SQL> create table t1(
	    v1  varchar2(4000),
	    v2  varchar2(2387),
	    v3  varchar2(100)
	) tablespace users;

	Table created.

	SQL> create index t1_i1 on t1(v1, v2) tablespace users;

	Index created.

	SQL> alter index t1_i1 rebuild;

	Index altered.

	SQL> alter index t1_i1 rebuild online;

	Index altered.

Now, you see no more ora-1450 error Note: The issue can be reproduced in 12c also if you connect with SYS user, Thanks to Jonathan for pointing this out to me.

Thanks

Suresh


Rs. 150 .Com at GoDaddy.com!

Leave a Reply