Oracle Scratchpad

June 28, 2013

Index rebuild 12c

Filed under: 12c,Index Rebuilds,Indexing,Oracle — Jonathan Lewis @ 8:35 am BST Jun 28,2013

Just one of those little snippets about 12c that might help someone.

Further to an earlier post, online rebuild works in 12c even when the key is “too long”. The internal code has changed completely, and there is no sign of the problematic journal table that caused the problem in earlier versions.


  1. Hi Jonathan,

    Tried to experiment the same with test case you have provided in 12c, but appears the issue persists? Is there any thing that I am missing?

    [oracle@oinfo12c tmp]$ sqlplus / as sysdba
    SQL*Plus: Release Production on Mon Sep 9 16:23:01 2013
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 12c Enterprise Edition Release - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    SQL> create table t1(
        v1  varchar2(4000),
        v2  varchar2(2387),
        v3  varchar2(100)
    ) tablespace users;
      2    3    4    5
    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
    SQL> show parameter compati
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    compatible                           string
    SQL> show parameter db_block_size
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_size                        integer     8192

    Comment by Suresh — September 9, 2013 @ 12:00 pm BST Sep 9,2013 | Reply

    • Suresh,

      Interesting – as is often the case, something funny happens with the SYS account.
      I can’t think of a good reason why SYS should have a problem – but it does.

      Comment by Jonathan Lewis — September 9, 2013 @ 9:45 pm BST Sep 9,2013 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by