not able rebuild index
I was rebuilding an index and was killed and now try to re-execute the rebuilding of index but was face with error
ora-08104-this index object 3969 is being online built or rebuilt
I know I have to wiat for smon to do recovery but is there any recovery so that I can re-execute the rebuilding of index......this table is very huge where I think it will take days
I am rebuild an index where the base table is 71G
not able to get the rows because face with snapshot too old when trying to run a query to count the table but based on the application user they say it is more than millions rows
my 65 million rows table with 17 columns is 4gb... how can your 1 million rows table be 71gb...??????
Metalink issues three possible ways to overcome this :
1. wait for SMON to clean up the temporary segments in the tablespace
2. try FORCE dropping the index, eg. 'drop index blah.blah FORCE;' ;
however this only applies to DOMAIN indexes
3. in Doc.ID 363662.999 (titled "How to handle ora-8104 errors")
Oracle gives the following possibility :
You can force this using the command
"alter session set events
'immediate trace name DROP_SEGMENTS level TS#+1';"
This event finds all the temporary segments in a tablespace which are not currently locked and drops them.
level = tablespace number+1. If the value is 2147483647 then temp segments in ALL tablespaces are dropped, otherwise, only segments in a tablespace whose number is equal to the LEVEL specification are dropped.
So you have to find the number of the tablespace in which the index resides issue the above command where substitute ts-number+1 for the level.
However, I would advise you to :
-first : visit metalink and search for the appropriate error reports
-second: try these possiblities out in TEST before applying them in your PRODUCTION environment
Last edited by icdb023; 01-14-2004 at 07:17 AM.
Click Here to Expand Forum to Full Width