I some how find out that one of my Index in the system tablespace is corrupt what will happen if I drop and recreate that Index.
please try to give reply as soon as possible.
What makes you to suspect the index?
Dropping and recreating the index will get rid of block corruption.
I have got this error
ORA-01115: IO error reading block from file 1 (block # 14022)
ORA-01110: data file 1: '/home/oracle8/OraHome1/oradata/oradata/hungama/system01.dbf'
ORA-27091: skgfqio: unable to queue I/O
Linux Error: 5: Input/output error
Additional information: 14021
file 1 = is system data file
and the block_id is 14022 but when I query for this block to find out its segment it is giving me an error but when I query for 14021 block_id it is giving that this block is for Index I_obj2
now what should I do? Should I drop that Index and recreate it?
If the corrupted block contains user object, then you are safe.
You can drop that object and recreate in a different tablespace.
Execute following query to finedout which object occupies the
SELECT SEGMENT_NAME, SEGMENT_TYPE FROM DBA_EXTENTS WHERE FILE_ID = 1 and 14022 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
Don't worry, recreating an index will not harm you. Use REBUILD command.
You cannot succeed if you fear to face challenges.
BE(CS) , OCP 8/8i.
I think you should look at this document as well
When I tried to rebuild, It is giving Error
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00060: deadlock detected while waiting for resource
If that index belongs to SYS then don't rebuild it. Possibly you will have to restore and recover the SYSTEM datafile.
Sanjay can you write down the steps to recover the system datafile
what is the drawback if I recreate the Index I mean first I drop it and recreate it will it effect somewhere, it is in system tablespace.
Click Here to Expand Forum to Full Width