Re: By default, LOB store in line or out of line?

  • From: du shenglin <shenglin.du@xxxxxxxxx>
  • To: kamusis@xxxxxxxxx
  • Date: Tue, 24 Apr 2012 08:57:48 +0800

I have the similar painful case to migrate from LONG(most are chain row) to
LOB. the bottleneck for us is the 'db file sequential read' with undo. Once
CTAS started to read UNDO, it will became worse and worse. Even read one
data block, it will need much more read of undo data. Finally, it failed
with ORA-01555.
Now, we decided to export from physical standby database. (open read only)
if so, we don't need to the undo read.
on primary, we created the trigger to record the changed PK values. Once
finishing the export/import, we will use script to sync up data based on
these PK data
Thanks
Shenglin



On Mon, Apr 23, 2012 at 10:17 PM, Leyi Kamus Zhang <kamusis@xxxxxxxxx>wrote:

> Hi, Fairlie
> Our test is with 8 concurrent sessions CTAS over dblink, we transfered 7TB
> LOB in 64 hours with "as of scn" flashback query from source database.
>
> Yours 12 concurrent jobs in 9 hours transfered 500GB data or 13TB? I also
> tested insert /+*append parallel*/, but the result is far worse than CTAS.
> Can you share your test result? Did you test CTAS?
>
> Our migration is painful... the source database has a single table over
> 7TB, almost LOB, no partition, actively inserted and updated, we have to
> use WHERE condition to split the source table into little pieces and have
> to use flashback query to get the consistency. The worst experience was
> after 20 hours CTAS we got ORA-01555 error, even we have set the UNDO
> RETENTION for LOB to 48 hours.
>
> --
> Kamus <kamusis@xxxxxxxxx>
>
> Visit my blog for more : http://www.dbform.com
> Join ACOUG: http://www.acoug.org
>
>
> On Mon, Apr 23, 2012 at 7:10 PM, fairlie rego <fairlie_r@xxxxxxxxx> wrote:
>
> > Approach 2
> > ---------
> > Insert over a dblink by having 12 concurrent scheduler jobs each working
> > on discrete data (as outlined by Tanel below) took less than 9 hours.
> >
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>


--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 04-2012