Re: Oracle XE Corruption
- From: Ilmar Kerm <ilmar.kerm@xxxxxxxxx>
- To: "Oracle-L@xxxxxxxxxxxxx" <Oracle-L@xxxxxxxxxxxxx>
- Date: Tue, 24 Aug 2010 22:34:54 +0300
From XE:
SQL> explain plan for alter index SMON_SCN_TIME_TIM_IDX rebuild;
Explained.
SQL> select
substr (lpad(' ', level-1) || operation || ' (' || options ||
')',1,30 ) "Operation",
object_name
"Object"
from
plan_table
start with id = 0
connect by prior id=parent_id;
Operation
Object
--------------------------------------------------------------------------------------------------------------
------------------------------
ALTER INDEX STATEMENT ()
INDEX BUILD (UNIQUE)
SMON_SCN_TIME_TIM_IDX
SORT (CREATE INDEX)
INDEX (FAST FULL SCAN)
SMON_SCN_TIME_TIM_IDX
On Tue, Aug 24, 2010 at 9:52 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx> wrote:
> Not sure if that is true, as I recall alter index rebuild... will fix an
> index that is in an invalid state.
>
> On Tue, Aug 24, 2010 at 1:43 PM, Ilmar Kerm <ilmar.kerm@xxxxxxxxx> wrote:
>>
>> Hi
>>
>> Doesn't rebuilding actually use the existing index to read the data
>> from, so rebuild will actually hit the corruption?
>> From documentation:
>>
>> http://www.filibeto.org/sun/lib/nonsun/oracle/10.2.0.1.0/B19306_01/server.102/b14231/indexes.htm#i1006864
>> "When you rebuild an index, you use an existing index as the data source."
>>
>> So drop and create the index.
>>
>> 1. Get the index DDL using DBMS_METADATA.GET_DDL
>> select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX') txt from
>> dual;
>>
>> 2. Drop old index: drop index SMON_SCN_TIME_TIM_IDX;
>>
>> 3. Run create index statement returned from dbms_metadata.get_ddl
--
Ilmar Kerm
--
//www.freelists.org/webpage/oracle-l
Other related posts:
