Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Alter index rebuild

Re: Alter index rebuild

From: Syed Jaffar Hussain <sjaffarhussain_at_gmail.com>
Date: Tue, 15 May 2007 19:57:04 +0300
Message-ID: <97b7fd2f0705150957n5553124qb0f2d3c34fb027f8@mail.gmail.com>


Which version of Oracle are you using?

You can rebuild the index using online so that it will not affect the end-users during rebuilding process.

However, one thing you need to be care is about the space. Rebuild requires almost double space during index rebuilding process and also generates lot of redo and which can leads to good amount of archived logs. All these depends on the size of the index.

An index rebuilt either Online or Offline.

Online Index Rebuild Features:

+ ALTER INDEX REBUILD ONLINE;
+ DMLs are allowed on the base table
+ It is comparatively Slow
+ Base table is referred for the new index
+ Base table is locked in shared mode and DDLs are not possible
+ Intermediate table stores the data changes in the base table, during the
index rebuild to update the new index later

Jaffar

On 5/15/07, BLock_at_dvfs.com <BLock_at_dvfs.com> wrote:
>
>
> Can I do an Alter Index rebuild to move an index to an index tablespace
> while the database is up?
>
> We are a 24x7 operation with scheduled outages usually only once a month.
>
> alter index schema.I_B_OAPREFSETTLEMENTVALUE_1 rebuild tablespace
> schema_IDX storage ( initial 256 K next 256 K pctincrease 0 ) ;
>
> Thanks
>
> Brian Lock
> DBA - East Campus
>
>

-- 
Best Regards,
Syed Jaffar Hussain
Oracle ACE
8i,9i & 10g OCP DBA

http://jaffardba.blogspot.com/
http://www.oracle.com/technology/community/oracle_ace/ace1.html#hussain
----------------------------------------------------------------------------------
"Winners don't do different things. They do things differently."

--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 15 2007 - 11:57:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US