This discussion is archived
5 Replies Latest reply: Feb 11, 2013 11:34 AM by spur230 RSS

convert lob from basicfile to securefile

spur230 Newbie
Currently Being Moderated
I am usig Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production.

I was able to convert basicfile to securefile user alter table move command.

However, Oracle documentation 11.2 says "Online redefinition is the only recommended method for migration of BasicFiles LOBs to SecureFiles LOBs."

Please advise if there are any know issues for using " Alter table <tablename> move lob" to convert from basicfile to securefile.

Why shouldnot we use simple alter table move lob statement as oppose to Online redefinition?
ALTER TABLE scott.stat_table
    MOVE LOB(cl1) STORE AS  securefile (
        TABLESPACE users
    );
Thanks.
  • 1. Re: convert lob from basicfile to securefile
    Reid Ricks Explorer
    Currently Being Moderated
    Please review the following if you haven't already: Secure file Migration and Accessing securefile metadata information [Video] (Doc ID 1170351.1)

    We can easily migrate from a Basicfile to Secure file LOBS.The two recommended methods for migration to securefiles are Partition exchange and online redefinition.

    1] Partition Exchange
    . Needs additional space equal to the largest of the partition in
    the table.
    . Can maintain indexes during the exchange.
    . Can spread the workload out over several smaller maintenance
    windows.
    . Required that the table or Partition needs to be offline to
    perform the exchange.

    2] Online Redefinition

    . No need to take the table or partition offline.
    . Can be done in parallel.
    . Requires additional storage equal to the entire table and all
    LOB segments to be available.
    . Requires that any global indexes to be rebuilt.

    Online redefinition is the recommended method. However, Using portioning and taking these actions on a partition-by-partition basis may be help lower the disk space required.
  • 2. Re: convert lob from basicfile to securefile
    spur230 Newbie
    Currently Being Moderated
    Thanks for the information but I wanted to know why shouldn't we use intutive alter table move statement.
  • 3. Re: convert lob from basicfile to securefile
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    >
    However, Oracle documentation 11.2 says "Online redefinition is the only recommended method for migration of BasicFiles LOBs to SecureFiles LOBs."

    Please advise if there are any know issues for using " Alter table <tablename> move lob" to convert from basicfile to securefile.
    >

    It may be no issues. However it is undocumented and not recommended way. If you will get into issues, most likely you will be on your own, most likely Oracle support will not help you, they will ask you to make migration "the proper way".
  • 4. Re: convert lob from basicfile to securefile
    spur230 Newbie
    Currently Being Moderated
    Thanks user11181920 . That makes sense.
  • 5. Re: convert lob from basicfile to securefile
    spur230 Newbie
    Currently Being Moderated
    As Mark said.
    It may be no issues. However it is undocumented and not recommended way.

    Thanks Mark.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points