…and you shouldn’t worry about it hanging if you really decide to do it online,
as all the operations of writes happen to the alternate table( materialized
view). If it hangs then kill the session and start over if you have to.
Matthew Parker
Chief Technologist
Dimensional DBA
425-891-7934 (cell)
D&B 047931344
CAGE 7J5S7
Dimensional.dba@xxxxxxxxxxx
<http://www.linkedin.com/pub/matthew-parker/6/51b/944/> View Matthew Parker's
profile on LinkedIn
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Andrew Kerber
Sent: Tuesday, February 09, 2016 2:36 PM
To: Chen Zhou
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Moving BLOBs from BASICFILES to SECUREFILES
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@xxxxxxxxx> 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.'