Oracle Scratchpad

September 16, 2007

Index Rebuild

Filed under: Index Rebuilds,Indexing,Infrastructure,Troubleshooting — Jonathan Lewis @ 10:12 pm BST Sep 16,2007

From time to time I see people asking about Oracle error ORA-01410: invalid rowid. It’s a problem I’ve responded to a few times in newsgroups and presentations, but I realised recently that despite mentioning it casually for several years I’ve never written a specific note about it – so here it is.

There are probably several reasons why you might see Oracle error ORA-01410, including the obvious one that you’ve created a rowid in the front-end code that cannot correspond to a real rowid. However, a much more subtle, yet relatively frequent, cause of the error comes from the habit of using automatic programs to rebuild multiple indexes.

Although most indexes in Oracle can safely be left to keep themselves tidy and efficient some DBAs (possibly many DBAs) have been persuaded that rebuilding indexes regularly is a good thing. Other DBAs are a little more cautious and write programs that attempt to make a sensible decision about which indexes really do need to be rebuilt – and then report suspect indexes rather than rebuilding them.

Even if you fall into the latter group and do manage to identify the few indexes that can benefit from being rebuilt, be careful about letting the rebuilds take place automatically – the timing can be critical.

Consider this: if you rebuild an index (whether or not you use the online option) Oracle will copy the index contents from one location to another and, when the copy is complete, free up the space that held the original index contents.

What’s going to happen to a long running query that started running before you did your rebuild ? Surprisingly the query will carry on running using the “old” index. Oracle introduced a form of ‘cross-DDL read consistency’ many years ago to make partition maintenance operations possible, and that’s the feature that allows your query to carry on running.

But what’s going to happen if some other process now demands the space and puts something into it – for example by creating or extending a table or index. The next time your long-running query hits the “old” index it may find itself looking at a block that contains the wrong type of data for the wrong object id. And so you get Oracle error ORA-01410: invalid rowid.

The effect is easy to demonstrate if you start with a completely empty tablespace (preferably locally managed with uniform extents, though this isn’t an absolute necessity):

create table t1 as
	rownum	id,
	rpad(rownum,10)	v1,
	rpad('x',100)	v2
	rownum <= 10000

alter table t1 add constraint t1_pk primary key(id);

execute dbms_stats.gather_table_stats(user,'t1')

	m_v1 varchar2(10);
	for r in (select /*+ index(t1) */ v1 from t1 where id > 0)
		m_v1 := r.v1;

	end loop;

All I do is create an indexed table, then run a simple procedure that is forced to walk through the index very slowly.

Now start up a new session for the same user (and you have about 100 seconds to do this)  and execute the following statement:

alter index t1_pk rebuild online;
create table t2 as select * from t1;

The new table t2 should fill the hole left by rebuilding the index t1_pk, and your first session should crash moments later.

The behaviour is much less predictable on production systems, of course. In the real world a random process may simply crash at some random time in the overnight batch once every few weeks for no apparent reason.

Footnote: there is an alternative to ORA-01410 that can appear if you rebuild indexes while running large reporting jobs. If, for example,  your code executes “set transaction read only “ and then runs lots of separate SQL statements, a statement that needs an index that was rebuilt after the “set transaction” call will fail with “ORA-08176: consistent read failure; rollback data not available”.

Update (Jan 2013):

I noticed a relatively recent incoming referral on this item that shows the problem is still happening in 2012 – you might want to read it as an example of how the symptoms can appear.


[Further reading on rebuilding indexes]


  1. Jonathan

    Very nicely written !

    Cheers :)


    Comment by Sidhu — September 17, 2007 @ 1:45 am BST Sep 17,2007 | Reply

  2. This one just hit the spot, Jonathan.
    Quite timely!

    Comment by Noons — September 17, 2007 @ 2:20 am BST Sep 17,2007 | Reply

  3. Is the new ‘cross-DDL read consistency’ the same feature that allows a select to continue after the table (or partition) on which it is running has been dropped (or exchanged) ?
    It is an interesting thought that the recycle bin in 10g makes less likely that the dropped segment is overwritten soon – even if I don’t think that it will protect the index rebuild.

    Comment by Alberto Dell'Era — September 17, 2007 @ 12:34 pm BST Sep 17,2007 | Reply

  4. @Jonathan


    I would expected that Oracle placed a lock on the that table/partition, so no-one can drop/alter/move it.

    More, if I’m rebuilding the index “online”, then I will expected that Oracle is “self consistent” (i.e. Oracle build a separate copy of the index before “switching” the original with the rebuild one).

    Well, a “strange” thing indeed! ^_^

    Comment by Antonio — September 17, 2007 @ 3:18 pm BST Sep 17,2007 | Reply

  5. […] unknown wrote an interesting post today onHere’s a quick excerptFrom time to time I see people asking about Oracle error ORA-01410: invalid rowid. It’sa problem I’ve responded to a few times in newsgroups and presentations, but I recently realised that despite mentioning it casually for several … […]

    Pingback by Mike’s Musings » Index Rebuild — September 17, 2007 @ 3:35 pm BST Sep 17,2007 | Reply

  6. @Antonio

    I believe, Oracle tries to avoid this kind of lock for the same reason why there is multi version read consistency: increased throughput. That’s one of the features that sets Oracle apart from other DB products.

    Also, Oracle *is* self consistent and it *does* the switch as you request. But from what I understand the ORA-01410 is similar in nature to ORA-01555 (snapshot too old), i.e. Oracle tries to do the job as good as possible but if resources are exhausted you get an error.

    Comment by Robert — September 19, 2007 @ 8:41 am BST Sep 19,2007 | Reply

  7. @Robert

    got your point.

    But…here we are seeing a “consistent read failure” (ORA-08176) and a invalid rowid (ORA-01410). It sound like: Oracle says: OMG! where the rows gone?

    So, we got read consistent problems: Oracle rebuild the index and generate new rowids (only for the index probably), so the running session (for..loop) find itself with a bunch of old refs (i.e. no more usable ones).

    I think is a bit different than “snapshot too old”, it’s more like: who moved my cheese? ^_^

    Comment by Antonio — September 19, 2007 @ 10:13 am BST Sep 19,2007 | Reply

  8. Antonio,

    I don’t understand the requirment about locking a table/partition so it can’t be moved. I don’t move a table. (Although I do copy the t1 table as an easy way of filling a hole in the tablespace – but the choice of thing to copy has nothing to do with the issue).

    When rebuilding (online, or “offline”) Oracle is self-consistent – the new version of the index is not made available until it is complete, the old version of the index is not decommissioned until the new index is available.

    The problem occurs because some processes are allowed to believe that a decommissioned index is still usable and try to keep on running.

    Comment by Jonathan Lewis — September 19, 2007 @ 11:29 am BST Sep 19,2007 | Reply

  9. @Jonathan

    The requirement about locking is to avoid the problem that arise here: if someone is using an object, I can not move/rebuild (“steal” :) ) that object.

    You say: the problem arise because some processes believe that the index is still OK, they believe to a “lie”.

    I say: Oracle doesn’t track that someone else is still using an object and so I should not modify it.

    Probably is “only” a design issue. That’s all!

    Comment by Antonio — September 19, 2007 @ 11:51 am BST Sep 19,2007 | Reply

  10. Antonio,
    Try this:
    Session 1: Create the table t1 and index.
    Session 2: Set transaction read only;
    Session 1: Rebuild the index and create the second table.
    Session 2: Query table t1 through the index.

    Session 2 crashes with ORA-08176.

    It wasn’t using the index when the rebuild started – so there’s no clue for session 1 that the index should not be rebuilt.

    Comment by Jonathan Lewis — September 19, 2007 @ 1:02 pm BST Sep 19,2007 | Reply

  11. Jonathan,

    Nice summary, and a very nice demo/test case.

    I’ve never run across an ORA-8176 before, and my first thought was “Huh? Why not ORA-1555?” But after reading the output of ‘oerr ora 8176’, it’s fairly self-explanatory. 1555 means the rollback has been overwritten, 8176 means the required rollback never existed.


    Comment by Mark Bobak — September 20, 2007 @ 4:26 am BST Sep 20,2007 | Reply

  12. @Jonathan


    I was thinking of rebuild while someone is reading.

    The new example leave me with no words.

    Comment by Antonio — September 20, 2007 @ 12:22 pm BST Sep 20,2007 | Reply

  13. Thanks Jonathan for your article. It pretty much sums up the problems that i’m having in production, because of a software that rotates partitions everyday and rebuilds indexes while other jobs are running queries on the same db. It looks like there’s no perfect solution for this problem.

    Comment by Ana — November 16, 2007 @ 11:12 am GMT Nov 16,2007 | Reply

  14. Ana,

    Yours is a case where you could look at the option of having two tablespaces for the ‘rotating’ index partitions. Start with the indexes in tablespaceA, rebuild to tablespaceB one day, then back to tablespaceA the next.

    Unfortunately you won’t be able to move all indexes instantaneously, so you will still run the risk of one index extending into the space just left by a other index – but the strategy should reduce the probability of getting the ORA-01410.

    The trouble is, the code can be a bit messy, and if it’s third-party it may be impossible to get it changed.

    Comment by Jonathan Lewis — November 21, 2007 @ 7:38 am GMT Nov 21,2007 | Reply

  15. […] Jonathan Lewis @ 9:12 am UTC Feb 9,2008 I’ve written a few notes about the pros and cons of index rebuilds in the past. A comment on Richard Foote’s blog describes a bug in 10g (reminiscent of an old […]

    Pingback by Index Rebuild 10g « Oracle Scratchpad — February 9, 2008 @ 9:12 am GMT Feb 9,2008 | Reply

  16. […] A little-known threat from index rebuilds […]

    Pingback by Index Efficiency « Oracle Scratchpad — May 30, 2008 @ 5:09 pm BST May 30,2008 | Reply

  17. […] Index Rebuild […]

    Pingback by Index analysis « Oracle Scratchpad — October 9, 2008 @ 11:41 am BST Oct 9,2008 | Reply

  18. Excellent article !!!
    You are absolutely bang on Target. Thank you very much.

    -Dillip Nayak

    Comment by Dillip Nayak — July 12, 2011 @ 4:39 pm BST Jul 12,2011 | Reply

  19. Sir,
    I daily reads your blog as I reads docs. If you allow me to give a little request, if possible can you please add “See also” for all your blog entries with co-related links, so that newbie readers like me, can found it much more helpful. Please ignore this request, if someone have already posted and/or not possible.

    Best Regards
    Girish Sharma

    Comment by Girish Sharma — January 24, 2012 @ 10:40 am GMT Jan 24,2012 | Reply

    • Girish,
      If you check the top line of each entry you will see (in red) a list of categories for the entry. If you pick a category that is the closest match to the particular feature of the blog item that interests you then you will get a list of the related items that I have written. I usually put a “fold line” in each item a few weeks after writing it so that this listing give you the first few lines from each item.

      Comment by Jonathan Lewis — January 26, 2012 @ 11:13 pm GMT Jan 26,2012 | Reply

  20. Sir,
    Thank you very much for your reply.

    Best Regards
    Girish Sharma

    Comment by Girish Sharma — January 28, 2012 @ 1:18 pm GMT Jan 28,2012 | Reply

  21. Dear Jonathan,

    Interesting post as always.
    I checked out the ORA-08176 at the URL I have given.

    Kind Regards,


    Comment by Andrew Reid — April 9, 2013 @ 7:45 pm BST Apr 9,2013 | Reply

  22. I’ve just discovered a link back to this blog post from StackExchange from someone who was seeing ORA-01410 and needed help finding out why:

    Comment by Jonathan Lewis — December 19, 2014 @ 5:42 pm GMT Dec 19,2014 | Reply

  23. […] If so, then you could be hitting the situation that Jonathan Lewis describes here: […]

    Pingback by ora-01410 invalid RowID sporadically in a select statement | XL-UAT — March 10, 2015 @ 8:54 pm GMT Mar 10,2015 | Reply

  24. Could this error occur if you have different Oracle clients? I am using Oracle 10g with an application that runs a select statement and it works fine, but I installed it on a server that uses Oracle 11g and the same select statement returns an Invalid ROWID error.

    Comment by Osvaldo — July 15, 2015 @ 3:02 pm BST Jul 15,2015 | Reply

    • Osvaldo,

      I see you’ve also raised this on the OTN database forum.
      From the way you’ve described it on the forum I doubt if your problem is anything to do with index rebuilds so I’ve followed up on the forum rather than here.

      Comment by Jonathan Lewis — July 15, 2015 @ 9:06 pm BST Jul 15,2015 | Reply

  25. Hi, dear jonathan,

    some testing result:
    ERROR at line 1:
    ORA-01410: invalid ROWID
    ORA-06512: at line 4 &
    ERROR at line 1:
    ORA-08103: object no longer exists
    ORA-06512: at line 4

    Best Regards,

    Comment by blueseine — July 13, 2016 @ 2:11 am BST Jul 13,2016 | Reply

    • Blueseine,

      Thanks for the comment – it’s nice to see that newer versions of Oracle report a more suitable error message. (And nice to see someone repeating some of my demos on newer versions of Oracle. And even nicer when they report changes in the results.

      Jonathan Lewis

      Comment by Jonathan Lewis — July 13, 2016 @ 5:58 pm BST Jul 13,2016 | Reply

  26. Hi Jonathan,

    I have the same problem, Invalid rowid comes from a select statement, Index rebuilding is not done through another process, so what is the cause and what will be the proper solution

    Error message is like this ORA-01410: invalid ROWID-ORA-06512


    Comment by Pawan Kant Prajapati — January 23, 2017 @ 5:59 am GMT Jan 23,2017 | Reply

    • This falls into the category:
      “There are probably several reasons why you might see Oracle error ORA-01410, including the obvious one that you’ve created a rowid in the front-end code that cannot correspond to a real rowid.”

      I see from the ORA-06512 that you’re calling a statement from PL/SQL, which suggests as a first guess that you are constructing a rowid that is not valid, or that you have some code that selects a rowid at one point in time that is not a valid rowid when you use it. The latter may be indicative of code that stores rowids forgetting that the object at which they point might be rebuilt at some time.

      If you want to pursue this further I suggest you post a question to the OTN database forum. Don’t forget to include the Oracle version, and do remember to describe the problem as accurately as possible: for example you should have pointed out that this code was embedded in pl/sql: what is the code trying to do, is it in a pl/sql loop, is the select explicitly requesting a rowid or join to a column that is supposed to hold a rowid, etc. The better your desrciption of the activity the more likely that someone will be able to answer the question.

      Comment by Jonathan Lewis — January 23, 2017 @ 10:54 am GMT Jan 23,2017 | Reply

  27. Hi Jonathan,

    My plsql code is like as below:


    Thanks in Advance

    Comment by Pawan Kant Prajapati — January 24, 2017 @ 3:26 am GMT Jan 24,2017 | Reply

  28. We have faced ORA-8176 when distributed query was running and new partition to the interval partitioned table added

    Comment by Josh — February 15, 2018 @ 7:20 am GMT Feb 15,2018 | Reply

  29. […] confirm it – and it doesn’t matter how many times I show queries succeeding. Thanks to “cross-DDL read-consistency” as it was called in 8i when partition-exchange appeared and because the old objects still exist in […]

    Pingback by 12c MView refresh | Oracle Scratchpad — February 15, 2022 @ 12:15 pm GMT Feb 15,2022 | 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 )

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

%d bloggers like this: