ORA-22924 reading LOBs / DBMS_REDEFINITON-- any performance suggestions ?
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 31 May 2006 23:13:11 +0800
I have a table with
a. a LOB segment
b. a very high rate of UPDATEs to the LOB (a BLOB !)
c. the LOB segment is in an LMT-AutoAllocate-ASSM Tablespace
d. 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
