ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)?-1451124.1

How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)?-1451124.1

原创 Linux操作系统 作者:rongshiyuan 时间:2013-10-10 01:19:17 0 删除 编辑
 
单击此项可添加到收藏夹 How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)? (文档 ID 1451124.1) 转到底部转到底部

修改时间:2013-3-2类型:HOWTO状态:PUBLISHED优先级:2
没有任何注释注释 (0) 为此文档评级 通过电子邮件发送此文档的链接 在新窗口中打开文档 可打印页

In this Document

Goal
Fix
References

Applies to:

Oracle Server - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.

Goal

Attempt to reduce the size of a LOB segment after the table / LOB becomes sparse

Fix

Large deletions of rows often leave 'holes' in LOB data which is commonly referred to as 'sparse'.

There are several methods of eliminating sparse data (shrinking / reorganizing) within a LOB SEGMENT

1) ALTER TABLE ... SHRINK SPACE

The most effective method is to store the data in an ASSM (Automatic Segment Space Managed) tablespace and use the SHRINK SPACE command

This command can either shrink just the LOB itself .. or can shrink the LOB and Table and Indexes at the same time

Examples of this process can be seen in

        Why is no space released after an ALTER TABLE ... SHRINK? (Document 820043.1) ... Case Studies 3 and 4

PROs: This method is simple and very effective
           This method does not require additional storage space

CONs: This method locks the table and will cause an outage
           This method is restricted as defined in the Oracle® Database SQL Language Reference ... ALTER TABLE command
           SECUREFILE LOBS may not use this method

2) DBMS_REDEFINITION

Another very effective method is to recreate the table using DBMS_REDEFINITION to recreate the table while it is online

Examples of this process can be seen in

         How to Shrink a SECUREFILE LOB Using Online Redefinition (DBMS_REDEFINITION)? (Document 1394613.1)
                    *** NOTE .. this process may also be used for BASICFILE lobs ***

         HOW TO SHRINK A TABLE USING ONLINE REDEFINITION (Document 1357878.1)

PROs: This method leaves the table online except for a short period during DBMS_REDEFINITION.FINISH_REDEF_TABLE

CONs:
This method will require additional space not required by other methods
              (up to double the size of the size of the original table .. to store the interim table)

           This method is restricted as defined in the online documentation for Restrictions on DBMS_REDEFINITION

            This method is complicated

3) ALTER TABLE ... MOVE

Another effective method to shrink space in a LOB is to move the table

The following note demonstrates this

        How To Use ALTER TABLE ... MOVE To Shrink A Table (Including BASICFILE / SECUREFILE LOBs) (Document 1396120.1)

PROs: This method is simple and very effective

CONs: This method locks the table and will cause an outage

          This method will require additional space not required by other methods
              (up to double the size of the size of the table)

          This method is complicated


4) EXPORT / DROP THE TABLE / IMPORT

An export (including DataPump - expdp) followed by a drop of the table containing the lob followed by an import (including Datapump - impdp) will recreate the table and thus reinsert the rows within ... thus eliminating 'sparseness'

PROs: Export/Import are a well established method of reorganizing

CONs: This method requires an outage beginning with the start of the export and ending with the end of the import (and adding of FK

            If Foreign Key constraints (FK) exist TO the table to be dropped .. those constraints must be dropped before the table can be dropped ... then later added again after the table is imported

5) Add / Drop columns

A creative but complicated method of shrinking a LOB is to

* Add a temporary column to the table of the same datatype as the LOB column to be shrunk (CLOB or BLOB)
* Copy the data from the original LOB column to the new column
* Drop the original column
* Add a new column of the same name as the original column to the table of the same datatype as the LOB column to be shrunk (CLOB or BLOB)
* Copy the data from the temporary column to the new original colunn
* Drop the temporary column

For an example of this method see:

        How to release unused space (SHRINK) occupied by a LOB segment by adding / dropping columns? (Document 1417697.1)

PROs:

CONs: This method requires that the table remain unchanged by users during the process

            This method requires the additional storage required by the temporary LOB column

            This method is complicated

References

NOTE:1357878.1 - HOW TO SHRINK A TABLE USING ONLINE REDEFINITION
NOTE:820043.1 - Why is no space released after an ALTER TABLE ... SHRINK?
NOTE:1394613.1 - How to Shrink a SECUREFILE LOB Using Online Redefinition (DBMS_REDEFINITION)?
NOTE:1396120.1 - How To Use ALTER TABLE ... MOVE To Shrink A Table (Including BASICFILE / SECUREFILE LOBs)
NOTE:1417697.1 - How to release unused space (SHRINK) occupied by a LOB segment by adding / dropping columns?
 

相关内容

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17252115/viewspace-774024/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-11-24

  • 博文量
    798
  • 访问量
    3199973