RE: Index rebuild problem

From: Singla, Sanjeev <SSingla_at_oxhp.com>
Date: Wed, 28 Jun 2000 09:24:46 -0400
Message-Id: <10542.110646@fatcity.com>

Actually when u rebuild a table online Oracle keeps the current DML's in the temporary journal table which after built gets merged to the created index.
It looks like Oracle is not able to create that journal table of online transactions. See where it is creating that temporary table. For the time being u can remove the online keyword and whenever table will be available oracle takes a lock on the table and create the index in exclusive mode.
But that defeats the purpose of online index rebuild in 8i. You should dig more
on this problem because it will be very helpful in future.

Sanjeev

-----Original Message-----
From: paquette stephane [mailto:stephane_paquette_at_yahoo.com] Sent: Wednesday, June 28, 2000 4:43 AM
To: Multiple recipients of list ORACLE-L Subject: Index rebuild problem

Hi,

On Oracle 8.1.5/Solaris 2.6, I've tried to rebuild an index in a different tablespace using "alter index xxx rebuild online tablespace yyyy;".

I had an ORA-3113 during the process.

Now, the index is still in the bad tablespace, when doing another alter index rebuild I have a ORA-08106: can not create journal table
HOURA.SYS_JOURNAL_9631
when doing a drop of the index I have ORA-08104: this index object 9631 is being online built or rebuilt.

If doing an explain plan I see that Oracle still uses the index to access the table .

Doing an analyze index validate structure reports no error.

How can I change that index not to be in the rebuild mode ? I still want to move that index.
This is a 24 hour system I do not have a time window to drop the table and rebuilt it.

Stephane Paquette
DBA Oracle
stephane_paquette_at_yahoo.com
spaquette_at_houra.fr
(33) 01 53 93 06 50

Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/

-- 
Author: =?iso-8859-1?q?paquette=20stephane?=
  INET: stephane_paquette_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

Received on Wed Jun 28 2000 - 08:24:46 CDT