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.

2 Comments »

  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 12.1.0.1.0 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 12.1.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
    SQL>
    
    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>
    
    
    SQL> show parameter compati
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    compatible                           string      12.1.0.0.0
    
    SQL> show parameter db_block_size
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_size                        integer     8192
    SQL>
    
    
    

    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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,268 other followers