2012-11-2 11gR2 "ADMINISTRATOR'S GUIDE" page 551 - 584

 

 

Shrinking Database Segments Online -- 在线压缩数据库的段空间 这里的压缩指的是把空间合并以释放更多的空闲区域
Segment shrink reclaims unused space both above and below the high water mark. In
contrast, space deallocation reclaims unused space only above the high water mark. In
shrink operations, by default, the database compacts the segment, adjusts the high
water mark, and releases the reclaimed space. -- 高水位线以上及以下均有压缩。

 

Shrink operations can be performed only on segments in locally managed tablespaces
with automatic segment space management (ASSM). Within an ASSM tablespace, all
segment types are eligible for online segment shrink except these: 以下对象无法使用SHRINK SPACE压缩
 -- IOT mapping tables
 -- Tables with rowid based materialized views
 -- Tables with function-based indexes
 -- SECUREFILE LOBs
 -- Compressed tables

 

Examples
Shrink a table and all of its dependent segments (including BASICFILE LOB
segments):
ALTER TABLE employees SHRINK SPACE CASCADE;
Shrink a BASICFILE LOB segment only:
ALTER TABLE employees MODIFY LOB (perf_review) (SHRINK SPACE);
Shrink a single partition of a partitioned table:
ALTER TABLE customers MODIFY PARTITION cust_P1 SHRINK SPACE;
Shrink an IOT index segment and the overflow segment:
ALTER TABLE cities SHRINK SPACE CASCADE;
Shrink an IOT overflow segment only:
ALTER TABLE cities OVERFLOW SHRINK SPACE;

Deallocating Unused Space -- DEALLOCATE与SHRINK的区别是 DEALLOCATE只释放高水位线以下的空间 当然不只是这一点
The following statements deallocate unused space in a segment (table, index or
cluster):
ALTER TABLE table DEALLOCATE UNUSED KEEP integer;
ALTER INDEX index DEALLOCATE UNUSED KEEP integer;
ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;
The KEEP clause is optional and lets you specify the amount of space retained in the
segment. You can verify that the deallocated space is freed by examining the DBA_
FREE_SPACE view.

 

Dropping Unused Object Storage -- 11.2.0.2版本新特性
Beginning with Oracle Database 11g release 2 (11.2.0.2), the DBMS_SPACE_ADMIN
package includes the DROP_EMPTY_SEGMENTS procedure, which enables you to drop
segments for empty tables and partitions that have been migrated from previous
releases. This includes segments of dependent objects of the table, such as index
segments, where possible.
The following example drops empty segments from every table in the database.

DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS();
END;
The following drops empty segments from the HR.EMPLOYEES table, including
dependent objects.
BEGIN
DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS(
schema_name => 'HR',
table_name => 'EMPLOYEES');
END;
This procedure requires 11.2 compatibility level.

Using PL/SQL Packages to Display Information About Schema Object Space Usage -- 使用PLSQL包查看对象的空间使用情况

 

Example: Using DBMS_SPACE.UNUSED_SPACE
The following SQL*Plus example uses the DBMS_SPACE package to obtain unused
space information.
SQL> VARIABLE total_blocks NUMBER
SQL> VARIABLE total_bytes NUMBER
SQL> VARIABLE unused_blocks NUMBER
SQL> VARIABLE unused_bytes NUMBER
SQL> VARIABLE lastextf NUMBER
SQL> VARIABLE last_extb NUMBER
SQL> VARIABLE lastusedblock NUMBER
SQL> exec DBMS_SPACE.UNUSED_SPACE('SCOTT', 'EMP', 'TABLE', :total_blocks, -
> :total_bytes,:unused_blocks, :unused_bytes, :lastextf, -
> :last_extb, :lastusedblock);
PL/SQL procedure successfully completed.
SQL> PRINT
TOTAL_BLOCKS
------------
5
TOTAL_BYTES
-----------
10240
...
LASTUSEDBLOCK
-------------
3