ITPub博客

首页 > Linux操作系统 > Linux操作系统 > release unused space (SHRINK) occupied by a LOB seg by add/drop col-1417697.1

release unused space (SHRINK) occupied by a LOB seg by add/drop col-1417697.1

原创 Linux操作系统 作者:rongshiyuan 时间:2013-10-10 05:40:42 0 删除 编辑

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

In this Document

Goal
Fix
References

Applies to:

Oracle Server - Enterprise Edition - Version 9.2.0.8 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.
Reviewed for relevance on 19 April 2012

Goal


How to release space occupied by LOB segments after deleting large number of rows from a table containing LOB data?

Fix


The only way to get rid of the old information is to perform. some simple table maintenance

The following provides an example of dumping redundant or obsolete space and indexes.

First check space, by using the following query:

1)
COL owner FORMAT A5 HEADING "Owner"
COL TABLE_NAME FORMAT A5 HEADING "Table|Name"
COL column_name FORMAT A10 HEADING "Column|Name"
COL segment_name FORMAT A26 HEADING "Segment Name"
COL segment_type FORMAT A10 HEADING "Segment|Type"
COL bytes HEADING "Segment|Bytes"

SELECT l.owner
, l.TABLE_NAME
, l.column_name
, s.segment_name
, s.segment_type
, s.bytes
FROM dba_lobs l
, dba_segments s
WHERE REGEXP_SUBSTR(l.segment_name,'([[:alnum:]]|[[:punct:]])+'
, CASE
WHEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1) > 0
THEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1)
ELSE 1
END) =
REGEXP_SUBSTR(s.segment_name,'([[:alnum:]]|[[:punct:]])+'
, CASE
WHEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1) > 0
THEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1)
ELSE 1
END)
AND l.TABLE_NAME = UPPER('&table_name')
AND l.owner = UPPER('&owner')
ORDER BY l.column_name, s.segment_name;




TABLE COLUMN Segment Segment

Name Name        Segment Name                       TYPE            Bytes
----- ---------      -------------------------              ----------      ---------
ITEM ITEM_BLOB SYS_IL0000074435C00007$$    LOBINDEX     65536
ITEM ITEM_BLOB SYS_LOB0000074435C00007$$ LOBSEGMENT 2097152
ITEM ITEM_DESC SYS_IL0000074435C00006$$    LOBINDEX     393216
ITEM ITEM_DESC SYS_LOB0000074435C00006$$ LOBSEGMENT 226492416


2) Create a temporary CLOB column in the target table. Then, you update the temporary column with the value from your real column.

ALTER TABLE item ADD (item_temp CLOB);
UPDATE item SET item_temp = item_desc;


When you requery the table’s indexes and segments, you’d find something like the following. You should note the size of the index and segments are three times larger in the real column than the temporary columns.

TABLE COLUMN Segment Segment

Name Name        Segment Name                       TYPE            Bytes
----- ---------      -------------------------              ----------      ---------
ITEM ITEM_BLOB SYS_IL0000074435C00007$$    LOBINDEX     65536
ITEM ITEM_BLOB SYS_LOB0000074435C00007$$ LOBSEGMENT 2097152
ITEM ITEM_DESC SYS_IL0000074435C00006$$    LOBINDEX     393216
ITEM ITEM_DESC SYS_LOB0000074435C00006$$ LOBSEGMENT 226492416
ITEM ITEM_TEMP SYS_IL0000074435C00016$$    LOBINDEX     131072
ITEM ITEM_TEMP SYS_LOB0000074435C00016$$ LOBSEGMENT 65011712


3) Drop the real column and add it back, or simply rename the new table to the old column once you’ve dropped it. Then, you update the real column with the values from the temporary column.

ALTER TABLE item DROP COLUMN item_desc;
ALTER TABLE item ADD (item_desc CLOB);
UPDATE item SET item_desc = item_temp;


4) Re-query the table and find that you’ve eliminated extraneous space.

TABLE COLUMN Segment Segment
Name Name        Segment Name                       TYPE            Bytes
----- ---------      -------------------------              ----------      ---------
ITEM ITEM_BLOB SYS_IL0000074435C00006$$    LOBINDEX     65536
ITEM ITEM_BLOB SYS_LOB0000074435C00006$$ LOBSEGMENT 2097152
ITEM ITEM_DESC SYS_IL0000074435C00016$$    LOBINDEX     131072
ITEM ITEM_DESC SYS_LOB0000074435C00016$$ LOBSEGMENT 65011712
ITEM ITEM_TEMP SYS_IL0000074435C00016$$    LOBINDEX     131072
ITEM ITEM_TEMP SYS_LOB0000074435C00016$$ LOBSEGMENT 65011712


5) Drop the temporary column after making the change.

ALTER TABLE item DROP COLUMN item_temp;

Now re-query the table and find that you’ve eliminated extraneous space.

TABLE COLUMN Segment Segment

Name Name        Segment Name                       TYPE            Bytes
----- ---------      -------------------------              ----------      ---------
ITEM ITEM_BLOB SYS_IL0000074435C00006$$    LOBINDEX     65536
ITEM ITEM_BLOB SYS_LOB0000074435C00006$$ LOBSEGMENT 2097152
ITEM ITEM_DESC SYS_IL0000074435C00016$$    LOBINDEX     131072
ITEM ITEM_DESC SYS_LOB0000074435C00016$$ LOBSEGMENT 650117


*** NOTE ... Other methods of reducing / shrinking / reorganizing LOB may be found in Note:1451124.1

References

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

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

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

注册时间:2009-11-24

  • 博文量
    798
  • 访问量
    3241760