Forum Stats

  • 3,872,223 Users
  • 2,266,407 Discussions
  • 7,911,107 Comments

Discussions

Enable shrink on securefile lobs

RobertOrtel
RobertOrtel Member Posts: 66 Blue Ribbon
edited Jan 11, 2016 6:01PM in Database Ideas - Ideas

To shrink lob segments is working with basicfiles but has no effect with securefiles, which for me is the major reason to avoid securefiles as long as I can. I never understood why this is the case. So please add this.

RobertOrtelborneselUser259623 -OracleManish ChaturvediFranck PachotcaadecarvalhoGerald Venzl-OraclectriebMarwimArpit Jain -OraclebhagatsinghJagadekaravinaykumar2ulohmannUser9928175-Oracletonibony7BPeaslandDBAHristiyanKUser_1TGCVRainer StenzelsensoftGbenga AjakayedherzhauDonald BMustafa_KALAYCIDinoBatchAdminYevon
28 votes

Active · Last Updated

Comments

  • Ivica Arsov
    Ivica Arsov Member Posts: 173

    There are multiple ways to shrink SECUREFILE LOBs, one of them is with DBMS_REDEFINITION (online redefinition)

    Regards,

    Ivica Arsov

    Franck Pachot
  • RobertOrtel
    RobertOrtel Member Posts: 66 Blue Ribbon

    There are multiple ways to shrink SECUREFILE LOBs, one of them is with DBMS_REDEFINITION (online redefinition)

    Regards,

    Ivica Arsov

    Hi Ivica,

    yes, but redefinition is a feature of enterprise edition only and requires more or less twice the space (depending on the shrinkable amount), ignoring the huge amount of online log to handle while doing so. Have you ever redefined a multi terrabyte lob? Please show me your other ways to shrink, as I like to know them for consideration.

    Regards,

    Robert

  • Ivica Arsov
    Ivica Arsov Member Posts: 173
    edited Feb 1, 2015 4:14PM

    Hi Ivica,

    yes, but redefinition is a feature of enterprise edition only and requires more or less twice the space (depending on the shrinkable amount), ignoring the huge amount of online log to handle while doing so. Have you ever redefined a multi terrabyte lob? Please show me your other ways to shrink, as I like to know them for consideration.

    Regards,

    Robert

    Yes, online redefinition method requires additional space but the table will stay online. Everything has cons and pros, e.g the method you propose will cause table lock and data outage (which for some its not an option).

    As other options you can do ALTER TABLE .... MOVE and export/drop/import. (which you won't like them, because you are looking for an option which doesn't require additional space).

    Why there is a restriction for securefiles?
    I believe it has something with securefile features (deduplication,encryption,compression) and its different storage organization from basicfiles.

    Regards,

    Ivica Arsov

  • PS_orclNerd
    PS_orclNerd Member Posts: 384 Bronze Badge

    Hi Ivica,

    yes, but redefinition is a feature of enterprise edition only and requires more or less twice the space (depending on the shrinkable amount), ignoring the huge amount of online log to handle while doing so. Have you ever redefined a multi terrabyte lob? Please show me your other ways to shrink, as I like to know them for consideration.

    Regards,

    Robert

    I did it this way, but with a downtime.. https://oraclenerdsk.wordpress.com/2015/06/21/tm-contention-and-insert-append-parallel-sessions/ Sometimes it's better to speak with App Managers than later thinking how to reorganize a huge LOB.

  • alter table MY_TABLE move lob(LOB_COL) store as (tablespace EXISTING_OR_NEW_TABLESPACE ) online;

    allows for online reclamation of lob space in a securefile lob