Re: Moving BLOBs from BASICFILES to SECUREFILES

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Tue, 9 Feb 2016 16:36:15 -0600
Message-ID: <CAJvnOJZ3DO=v92_fs8XYDNQMPNYaDa-MqAMZmuuLDA69+O=Nfg_at_mail.gmail.com>

DBMS redefinition is the way to go. This is an online operation using dbms_redefinition, so down time is not required. Just do a periodic 'sync_interim_table' so the final switch doesnt take too long.

However, there is no way anyone here knows the characteristics of your system, or how long it will take to move 300G of blobs. Any estimates we give would be purely guesswork.

I did a blog on the use of dbms_redefinition some time ago, linked here:

https://dbakerber.wordpress.com/2015/03/03/dbms_redefinition-revisited/

I think it provides a pretty good example.

On Tue, Feb 9, 2016 at 4:10 PM, Chen Zhou <oracle.unknowns_at_gmail.com> wrote:

> Hi,
> We have this table with BLOBs, which has high "enq:HW- contention" from
> time to time.
> We already have event "44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024" set.
> The only option I can think of is to move this BLOB from BASICFILE to
> SECUREFILE.
> There has been discussion of doing this by using dbms_redefinition here,
> it seems to be a great tool that allows changing BLOB online.
> This is a very important production database, will there be significant
> performance impact using this tool? If so, we may just schedule a
> maintenance time.
> How long should we expect to redefine 300GB BLOBs? A few hours or a few
> days? Can it end up hanging?
> Can I take the opportunity to move the BLOB to another tablespace during
> this redefinition process?
> Thank you for your information.
> Chen
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

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

Received on Tue Feb 09 2016 - 23:36:15 CET