Re: Oracle XE Corruption
- From: "Daniel Fink" <daniel.fink@xxxxxxxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Wed, 25 Aug 2010 15:35:00 +0000
Glad to hear that all worked out well.
What error did you get when you tried to rebuild the index? Or did the rebuild
'work' but the errors remained?
As someone else has pointed out, you can actually export objects owned by sys
if you specify the table name. That is the power of a collective intelligence -
we learn something new on a daily basis!
------- Original Message -------
On 8/25/2010 12:15 PM Evan Pettrey wrote:
Greetings everybody,
I wanted to follow up concerning the issue I brought to you guys yesterday
and let you know how things turned out. Rebuilding the index didn't actually
fix the problem, what fixed the issue was dropping and recreating the
indexes on table smon_scn_time using the below:
drop index smon_scn_time_scn_idx;
drop index smon_scn_time_tim_idx;
create unique index smon_scn_time_scn_idx on smon_scn_time(scn);
create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp);
analyze table smon_scn_time validate structure cascade;
I created a clone of our Oracle DB VM and then performed the work while the
system was live without any downtime whatsoever, far exceeding my initial
expectations.
Just wanted to thank all of you guys for your help yesterday. I'm not a DBA
by any means, but as the sys admin for a small company (only around 150
employees) I am expected to wear a lot of different hats. With your help I
was able to resolve this issue and ensure I live to see another paycheck.
I've been in the IT industry for awhile now and I make frequent use of
forums. However, I can honestly say this is the most helpful one I've come
across to date. If only they had something this helpful for sys admins!
Thanks again everybody, it is greatly appreciated.
Regards,
Evan
On Tue, Aug 24, 2010 at 3:34 PM, Ilmar Kerm <ilmar.kerm@xxxxxxxxx> wrote:
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
