Corrupt index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Corrupt index

  1. #1
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Hi,

    DB: Oracle 7.3.4.0
    OS: Compaq Tru64Unix v4.0 D

    I am getting ORA-600 while running query against one table. I got the following output after running "analyze table validate structure cascade"

    Dump file /ora51/app/oracle/admin/cdsbp/udump/ora_9226.trc
    Oracle7 Server Release 7.3.4.0.0 with the 64-bit option - Production
    With the distributed option
    PL/SQL Release 2.3.4.0.0 - Production
    ORACLE_HOME = /ora51/app/oracle/product/7.3.4
    System name: OSF1
    Node name: puma.qntm.com
    Release: V4.0
    Version: 878
    Machine: alpha
    Instance name: cdsbp
    Redo thread mounted by this instance: 1
    Oracle process number: 26
    Unix process pid: 9226, image: oraclecdsbp

    *** 2001.08.20.15.38.51.931
    *** SESSION ID:(10.1860) 2001.08.20.15.38.51.886
    Table/Index row count mismatch
    table 9949285 : index 9949298, 0
    Index root = dba: 0x2c019935

    How should I know from this trace file, which index to be rebuilt ? Or do I need to rebuild all indexes on this table??

    Appreciate all the help.

    Sanjay

  2. #2
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234

    Lightbulb Analyze

    Hi,

    you can analyze the indexes on the table like this

    ANALYZE INDEX indexname COMPUTE STATISTICS;

    If the index is corrupt it will show up the same way as it did after the analyze table command.

    I think all your indexes wil have to be rebuilded but I'm not sure.

    Greetings,

    Tycho

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    If you don't know which one is giving you the problem, I would rebuild them all.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Thanks Marist and Tycho,

    I dropped and recreated all the indexes on that table but still getting the error. Does anybody know, how to find eaxctly which index is corrupt ??

    Looking forward for some hints..

    Thanks
    Sanjay

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758

    Corrupt Index-Resolved

    Eureka !!!!!!

    This is how you can find the exact corrupt index...

    -Search the trace file for followin excerpts...

    Block header dump: dba: 0x6401fb06
    Object id on Block? Y
    seg/obj: 0x4014 csc: 0x00.3493f469 itc: 3 flg: - typ: 2 - INDEX
    fsl: 0 fnx: 0x0

    -Take 4014 from seg/obj
    -Convert this HEX value to Decimal.
    -That will give you the object_id of the corrupt index.

    In case if the table is of huge size (in my case, it is) and you can't afford to recreate all the indexes on the table this will help you to find exactly which index is corrupt.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width