当表随着数据的增加，会使用越来越多的块，HWM会升高，当删除一些数据后，可能会产生很多空块（不包含数据的块），但它们仍在HWM之下。为了降低高水位线，有必要对表进行重组 （reorganization）:
ALTER TABLE T ENABLE ROW MOVEMENT;
ALTER TABLE T SHRINK SPACE;
什么时候应该收缩表空间呢？
从统计信息表中dba_tables，可以计算出表共使用的空间（AVG_ROW_LEN * NUM_ROWS），和占用的块数（BLOCKS）（高水位之下的块），据此大概可知高水位之下空闲块的数量，以决定是否收缩表。
SQL> select dbms_metadata.get_ddl('TABLE','TEST') from dual;
DBMS_METADATA.GET_DDL('TABLE','TEST')
--------------------------------------------------------------------------
CREATE TABLE "SYS"."TEST"
( "TEXT" CHAR(2000)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
SQL> insert into test select object_name from dba_objects where rownum < 3000;
已创建2999行。
SQL> commit;
提交完成。
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TEST',estimate_percent=>30);
PL/SQL 过程已成功完成。
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS,avg_row_len,num_rows from dba_tables where table_name='TE
ST' and owner='SYS';
TABLE_NAME BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_ROWS
------------------------------ ---------- ------------ ----------- ----------
TEST 1000 0 2001 2999
SQL> delete from test where rownum <2000;
已删除1999行。
SQL> commit;
提交完成。
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TEST',estimate_percent=>30);
PL/SQL 过程已成功完成。
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS,avg_row_len,num_rows from dba_tables where table_name='TE
ST' and owner='SYS';
TABLE_NAME BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_ROWS
------------------------------ ---------- ------------ ----------- ----------
TEST 1000 0 2001 1000
--BLOCKS没有改变，同样是1000。
SQL> set autot traceonly
SQL> select * from test;
已选择1000行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 1954K| 221 (1)| 00:00:03 |
| 1 | TABLE ACCESS FULL| TEST | 1000 | 1954K| 221 (1)| 00:00:03 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
135 recursive calls
0 db block gets
1076 consistent gets --扫描的块数 1076
0 physical reads
0 redo size
2026014 bytes sent via SQL*Net to client
1111 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> set autot off
SQL> alter table test enable row movement;
表已更改。
SQL> alter table test shrink space; --对表收缩
表已更改。
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TEST',estimate_percent=>30);
PL/SQL 过程已成功完成。
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS,avg_row_len,num_rows from dba_tables where table_name='TE
ST' and owner='SYS';
TABLE_NAME BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_ROWS
------------------------------ ---------- ------------ ----------- ----------
TEST 250 0 2001 1000
--对表分析后，BLOCKS为250。
SQL> set autot traceonly
SQL> select * from test;
已选择1000行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 1954K| 56 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1000 | 1954K| 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
135 recursive calls
0 db block gets
317 consistent gets --扫描的块数变为317
0 physical reads
0 redo size
2026014 bytes sent via SQL*Net to client
1111 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> set autot offALTER TABLE <table_name> SHRINK SPACE COMPCAT;的使用并查看使用效果：
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TEST',estimate_percent=>30);
PL/SQL 过程已成功完成。
SQL> select * from test;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2002 | 56 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 2002 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
254 consistent gets
0 physical reads
0 redo size
270 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> alter table test SHRINK SPACE COMPACT;
表已更改。
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TEST',estimate_percent=>30);
PL/SQL 过程已成功完成。
SQL> select * from test;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2002 | 56 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 2002 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
135 recursive calls
0 db block gets
267 consistent gets --基本没有变
0 physical reads
0 redo size
270 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> set autot off
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS,avg_row_len,num_rows from dba_tables where table_name='TE
ST' and owner='SYS';
TABLE_NAME BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_ROWS
------------------------------ ---------- ------------ ----------- ----------
TEST 250 0 0 0
--BLOCKS仍然是250
SQL> alter table test SHRINK SPACE ;
表已更改。
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TEST',estimate_percent=>30);
PL/SQL 过程已成功完成。
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS,avg_row_len,num_rows from dba_tables where table_name='TE
ST' and owner='SYS';
TABLE_NAME BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_ROWS
------------------------------ ---------- ------------ ----------- ----------
TEST 1 0 0 0
shrink_clause
The shrink clause lets you manually shrink space in a table, index-organized table or its overflow segment, index, partition, subpartition, LOB segment, materialized view, or materialized view log. This clause is valid only for segments in tablespaces with automatic segment management. By default, Oracle Database compacts the segment, adjusts the high water mark, and releases the recuperated space immediately.
Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before specifying this clause. Further, if your application has any rowid-based triggers, you should disable them before issuing this clause.
COMPACT If you specify COMPACT, then Oracle Database only defragments the segment space and compacts the table rows for subsequent release. The database does not readjust the high water mark and does not release the space immediately. You must issue another ALTER TABLE ... SHRINK SPACE statement later to complete the operation. This clause is useful if you want to accomplish the shrink operation in two shorter steps rather than one longer step.
For an index or index-organized table, specifying ALTER [INDEX | TABLE] ... SHRINK SPACE COMPACT is equivalent to specifying ALTER [INDEX | TABLE ... COALESCE. The shrink_clause can be cascaded (please refer to the CASCADE clause, which follows) and compacts the segment more densely than does a coalesce operation, which can improve performance. However, if you do not want to release the unused space, then you can use the appropriate COALESCE clause.
CASCADE If you specify CASCADE, then Oracle Database performs the same operations on all dependent objects of table, including secondary indexes on index-organized tables.
Restrictions on the shrink_clause The shrink_clause is subject to the following restrictions:
You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.
Segment shrink is not supported for tables with function-based indexes or bitmap join indexes.
This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.
You cannot specify this clause for a compressed table.
You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.
明白以下语句的作用：
ALTER TABLE <table_name> ENABLE ROW MOVEMENT -->前提条件
ALTER TABLE <table_name> SHRINK SPACE [ <NULL> | COMPACT | CASCADE ];
ALTER TABLE <table_name> SHRINK SPACE COMPCAT; -->缩小表和索引，不移动高水位线，不释放空间
ALTER TABLE <table_name> SHRINK SPACE; -->收缩表，降低高水位线;
ALTER TABLE <table_name> SHRINK SPACE CASCADE; -->收缩表，降低高水位线，并且相关索引也要收缩一下
ALTER TABLE <table_name> MODIFY LOB (lob_column) (SHRINK SPACE); -->收缩LOB段
ALTER INDEX IDXNAME SHRINK SPACE; -->索引段的收缩，同表段
使用 show_space (来自tom)区分段管理为manual,还是auto
DBMS_SPACE.UNUSED_SPACE Returns information about unused space in an object (table, index, or cluster).
DBMS_SPACE.FREE_BLOCKS Returns information about free data blocks in an object (table, index, or cluster) whose segment free space is managed by free lists (segment space management is MANUAL).
DBMS_SPACE.SPACE_USAGE Returns information about free data blocks in an object (table, index, or cluster) whose segment space management is AUTO.
CREATE OR REPLACE PROCEDURE show_space
(
p_segname IN VARCHAR2,
p_owner IN VARCHAR2 DEFAULT USER,
p_type IN VARCHAR2 DEFAULT 'TABLE',
p_partition IN VARCHAR2 DEFAULT NULL
)
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE, and so it can be installed
-- once per database, instead of once per user who wanted to use it
AUTHID CURRENT_USER AS
l_free_blks NUMBER;
l_total_blocks NUMBER;
l_total_bytes NUMBER;
l_unused_blocks NUMBER;
l_unused_bytes NUMBER;
l_LastUsedExtFileId NUMBER;
l_LastUsedExtBlockId NUMBER;
l_LAST_USED_BLOCK NUMBER;
l_segment_space_mgmt VARCHAR2(255);
l_unformatted_blocks NUMBER;
l_unformatted_bytes NUMBER;
l_fs1_blocks NUMBER;
l_fs1_bytes NUMBER;
l_fs2_blocks NUMBER;
l_fs2_bytes NUMBER;
l_fs3_blocks NUMBER;
l_fs3_bytes NUMBER;
l_fs4_blocks NUMBER;
l_fs4_bytes NUMBER;
l_full_blocks NUMBER;
l_full_bytes NUMBER;
-- inline procedure to print out numbers nicely formatted
-- with a simple label
PROCEDURE p
(
p_label IN VARCHAR2,
p_num IN NUMBER
) IS
BEGIN
dbms_output.put_line(rpad(p_label, 40, '.') ||
to_char(p_num, '999,999,999,999'));
END;
BEGIN
-- this query is executed dynamically in order to allow this procedure
-- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
-- via a role as is customary.
-- NOTE: at runtime, the invoker MUST have access to these two
-- views!
-- this query determines if the object is an ASSM object or not
BEGIN
EXECUTE IMMEDIATE 'select ts.segment_space_management
from dba_segments seg, dba_tablespaces ts
where seg.segment_name = :p_segname
and (:p_partition is null or
seg.partition_name = :p_partition)
and seg.owner = :p_owner
and seg.tablespace_name = ts.tablespace_name'
INTO l_segment_space_mgmt
USING p_segname, p_partition, p_partition, p_owner;
EXCEPTION
WHEN too_many_rows THEN
dbms_output.put_line('This must be a partitioned table, use p_partition => ');
RETURN;
END;
-- if the object is in an ASSM tablespace, we must use this API
-- call to get space information, otherwise we use the FREE_BLOCKS
-- API for the user-managed segments
IF l_segment_space_mgmt = 'AUTO' THEN
dbms_space.space_usage(p_owner,
p_segname,
p_type,
l_unformatted_blocks,
l_unformatted_bytes,
l_fs1_blocks,
l_fs1_bytes,
l_fs2_blocks,
l_fs2_bytes,
l_fs3_blocks,
l_fs3_bytes,
l_fs4_blocks,
l_fs4_bytes,
l_full_blocks,
l_full_bytes,
p_partition);
p('Unformatted Blocks ', l_unformatted_blocks);
p('FS1 Blocks (0-25) ', l_fs1_blocks);
p('FS2 Blocks (25-50) ', l_fs2_blocks);
p('FS3 Blocks (50-75) ', l_fs3_blocks);
p('FS4 Blocks (75-100)', l_fs4_blocks);
p('Full Blocks ', l_full_blocks);
ELSE
dbms_space.free_blocks(segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks);
p('Free Blocks', l_free_blks);
END IF;
-- and then the unused space API call to get the rest of the
-- information
dbms_space.unused_space(segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK);
p('Total Blocks', l_total_blocks);
p('Total Bytes', l_total_bytes);
p('Total MBytes', trunc(l_total_bytes / 1024 / 1024));
p('Unused Blocks', l_unused_blocks);
p('Unused Bytes', l_unused_bytes);
p('Last Used Ext FileId', l_LastUsedExtFileId);
p('Last Used Ext BlockId', l_LastUsedExtBlockId);
p('Last Used Block', l_LAST_USED_BLOCK);
END;
Total Blocks --总块数
Full Blocks --填满的块数
FS4 Blocks
FS3 Blocks
FS2 Blocks
从以上几个输出也可以看出是否需要对表进行收缩。
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2156
参考：
http://blog.csdn.net/robinson_0612/article/details/6630673
1842
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
