RE: Change index from nologging to logging

  • From: "Kenneth Naim" <kennaim@xxxxxxxxx>
  • To: <ksmadduri@xxxxxxxxx>, <Oracle-L@xxxxxxxxxxxxx>
  • Date: Sat, 21 Mar 2009 12:41:20 -0400

The "Alter index index_name rebuild online;" will rebuild the index which
will take a while depending on the number of rows in the table and is only
necessary when you want to move it from one tablespace to another or if
something has happened to the index to make it corrupt, or perform poorly.
The second option (alter index index_name logging;) just switches the flag
on index and is very fast, which is what you need. The drop and create are a
third option which I don't recommend as if something goes wrong during the
script you may end up without one or many indexes. 

 

As long as the indexes are in logging mode immediately before and during a
hot backup they will be refreshed properly. 

 

Ken

 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Kumar Madduri
Sent: Saturday, March 21, 2009 11:48 AM
To: Oracle-L@xxxxxxxxxxxxx
Subject: Change index from nologging to logging

 

Hi

Some indexes were build using the nologging option. This creates corruption
when the instance is refreshed.  Now they need to be converted to logging
option. I have two options to do this.

 

Alter index index_name rebuild online;

alter index index_name logging;

 

or

drop index index_name

create index  index_name with logging option;

 

I was going with the first option. Is there a difference between doing 1 and
2.  

Thank you for your time

 

Kumar

Other related posts: