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

Home -> Community -> Mailing Lists -> Oracle-L -> ORA-22924 reading LOBs / DBMS_REDEFINITON-- any performance suggestions ?

ORA-22924 reading LOBs / DBMS_REDEFINITON-- any performance suggestions ?

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Wed, 31 May 2006 23:13:11 +0800
Message-Id: <6.2.1.2.0.20060531230516.01fe1d60@pop.singnet.com.sg>

I have a table with

  1. a LOB segment
  2. a very high rate of UPDATEs to the LOB (a BLOB !)
  3. the LOB segment is in an LMT-AutoAllocate-ASSM Tablespace
  4. the database is running AUM and UNDO_RETENTION is 3600 and, it follows, the RETENTION for the LOB is 3600 (ie I expect it to default to using RETENTION instead of PCTVERSION and when I alter UNDO_RETENTION, I can see the LOB's RETENTION also changing)

I am trying to rebuild the table using DBMS_REDEFINITION. The first DBMS_REDEFINITION.START_REDEF_TABLE keeps failing with ORA-1555, ORA-22924 errors after 3 to 6 minutes ! Setting PARALLEL for the source table and the target table is an option that I have also explored, but with only a DEGREE of 2 on both tables
(parallel_max_servers is 5).

Short of stopping the UPDATEs to the table (and hey ! this is a very active database and the only reason why I am attempting DBMS_REDEFINITION is that I am not allowed more than the minimal downtime !) what options do I have ?

With RETENTION of 3600, I expected to be able to read the table and do not know why I get ORA-1555/ORA-22924s. As this issue arose today, I have logged a TAR and the Support Analyst first three Note#253131.1
(Concurrent Writes may corrupt LOB Segments in ASSM, causing ORA-1555s).
I have rejected that as this database is on 9.2.0.4 (the fix version for the bug)
and the application and alert.log do not see the errors mentioned in that note.

Why does START_REDEF_TABLE have to create the whole table up-front ? Can it not start with 0 rows and then incrementally update everytime I run a SYNC_TABLE ?

Hemant K Chitale
http://web.singnet.com.sg/~hkchital

--
http://www.freelists.org/webpage/oracle-l
Received on Wed May 31 2006 - 10:13:11 CDT

Original text of this message

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