Subscribe to Posts by Email

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages

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

Comments are closed.