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@Geek DBA11g 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@Geek DBA12c ~]$ 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@//Geek DBA12c: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

Geek DBA