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.
Shailendra
Printable View
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.
Shailendra
What makes you to suspect the index?
Dropping and recreating the index will get rid of block corruption.
Sanjay
Thanks sanjay
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?
Shailendra
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
corrupt block.
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.
Hi
I think you should look at this document as well
http://metalink.oracle.com/metalink/...T&p_id=28814.1
Cheers
Afshin
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
Shailendra
No, no.
If that index belongs to SYS then don't rebuild it. Possibly you will have to restore and recover the SYSTEM datafile.
Sanjay
Sanjay can you write down the steps to recover the system datafile
Shailendra
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.
Shailendra Singh