2. 删除记录之后,进行收缩表段SQL> col SEGMENT_NAME format a20 SQL> select OWNER,SEGMENT_NAME,BYTES/1024/1024,BLOCKS from dba_segments 2 where wner='SCOTT' and SEGMENT_NAME='BIG_TABLE'; OWNER SEGMENT_NAME BYTES/1024/1024 BLOCKS ------------------------------ -------------------- --------------- ---------- SCOTT BIG_TABLE 120 15360 SQL> select table_name,blocks,empty_blocks from dba_tables 2 where table_name='big_table' and wner='scott'; -->未更新统计信息前BLOCKS,EMPTY_BLOCKS列为空 TABLE_NAME BLOCKS EMPTY_BLOCKS ------------------------------ ---------- ------------ BIG_TABLE SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'BIG_TABLE',estimate_percent=>30); PL/SQL procedure successfully completed. SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from dba_tables 2 where TABLE_NAME='BIG_TABLE' and wner='SCOTT'; TABLE_NAME BLOCKS EMPTY_BLOCKS ------------------------------ ---------- ------------ BIG_TABLE 14590 0 -->使用gather_table_stats时不会统计EMPTY_BLOCKS块 SQL> analyze table big_table compute statistics; -->使用analyze更新统计信息后EMPTY_BLOCKS得到数据 Table analyzed. SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from dba_tables 2 where TABLE_NAME='BIG_TABLE' and wner='SCOTT'; TABLE_NAME BLOCKS EMPTY_BLOCKS ------------------------------ ---------- ------------ BIG_TABLE 14590 770 SQL> set serveroutput on; SQL> exec show_space('BIG_TABLE','SCOTT'); -->使用show_space过程或的BIG_TABLE上的空间分配信息 Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 0 -->空闲度为0-25%的块数。FS1,FS2,FS3,FS4为空闲度所占的百分比 FS2 Blocks (25-50) ..................... 0 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 14,427 Total Blocks............................ 15,360 Total Bytes............................. 125,829,120 Total MBytes............................ 120 Unused Blocks........................... 770 Unused Bytes............................ 6,307,840 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 16,521 Last Used Block......................... 254 PL/SQL procedure successfully completed.
- SQL> col SEGMENT_NAME format a20
- SQL> select OWNER,SEGMENT_NAME,BYTES/1024/1024,BLOCKS from dba_segments
- 2 where owner='SCOTT' and SEGMENT_NAME='BIG_TABLE';
- OWNER SEGMENT_NAME BYTES/1024/1024 BLOCKS
- ------------------------------ -------------------- --------------- ----------
- SCOTT BIG_TABLE 120 15360
- SQL> select table_name,blocks,empty_blocks from dba_tables
- 2 where table_name='big_table' and owner='scott'; -->未更新统计信息前BLOCKS,EMPTY_BLOCKS列为空
- TABLE_NAME BLOCKS EMPTY_BLOCKS
- ------------------------------ ---------- ------------
- BIG_TABLE
- SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'BIG_TABLE',estimate_percent=>30);
- PL/SQL procedure successfully completed.
- SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from dba_tables
- 2 where TABLE_NAME='BIG_TABLE' and owner='SCOTT';
- TABLE_NAME BLOCKS EMPTY_BLOCKS
- ------------------------------ ---------- ------------
- BIG_TABLE 14590 0 -->使用gather_table_stats时不会统计EMPTY_BLOCKS块
- SQL> analyze table big_table compute statistics; -->使用analyze更新统计信息后EMPTY_BLOCKS得到数据
- Table analyzed.
- SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from dba_tables
- 2 where TABLE_NAME='BIG_TABLE' and owner='SCOTT';
- TABLE_NAME BLOCKS EMPTY_BLOCKS
- ------------------------------ ---------- ------------
- BIG_TABLE 14590 770
- SQL> set serveroutput on;
- SQL> exec show_space('BIG_TABLE','SCOTT'); -->使用show_space过程或的BIG_TABLE上的空间分配信息
- Unformatted Blocks ..................... 0
- FS1 Blocks (0-25) ...................... 0 -->空闲度为0-25%的块数。FS1,FS2,FS3,FS4为空闲度所占的百分比
- FS2 Blocks (25-50) ..................... 0
- FS3 Blocks (50-75) ..................... 0
- FS4 Blocks (75-100)..................... 0
- Full Blocks ............................ 14,427
- Total Blocks............................ 15,360
- Total Bytes............................. 125,829,120
- Total MBytes............................ 120
- Unused Blocks........................... 770
- Unused Bytes............................ 6,307,840
- Last Used Ext FileId.................... 4
- Last Used Ext BlockId................... 16,521
- Last Used Block......................... 254
- PL/SQL procedure successfully completed.
3. 验证cascade与compact的差异SQL> delete from big_table where owner in ('SCOTT','SYSTEM'); -->删除记录 8715 rows deleted. SQL> commit; Commit complete. SQL> alter table big_table shrink space; -->实施shrink,提示没有启用ROW MOVEMENT alter table big_table shrink space * ERROR at line 1: ORA-10636: ROW MOVEMENT is not enabled SQL> alter table big_table enable row movement; -->开启row movement Table altered. SQL> alter table big_table shrink space; -->shrink成功 Table altered. SQL> exec show_space('BIG_TABLE','SCOTT'); -->从下面的结果中可以看到块数以及总大小已经变小 Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 1 FS2 Blocks (25-50) ..................... 1 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 14,318 Total Blocks............................ 14,488 Total Bytes............................. 118,685,696 Total MBytes............................ 113 Unused Blocks........................... 5 Unused Bytes............................ 40,960 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 16,521 Last Used Block......................... 147 PL/SQL procedure successfully completed.
- SQL> delete from big_table where owner in ('SCOTT','SYSTEM'); -->删除记录
- 8715 rows deleted.
- SQL> commit;
- Commit complete.
- SQL> alter table big_table shrink space; -->实施shrink,提示没有启用ROW MOVEMENT
- alter table big_table shrink space
- *
- ERROR at line 1:
- ORA-10636: ROW MOVEMENT is not enabled
- SQL> alter table big_table enable row movement; -->开启row movement
- Table altered.
- SQL> alter table big_table shrink space; -->shrink成功
- Table altered.
- SQL> exec show_space('BIG_TABLE','SCOTT'); -->从下面的结果中可以看到块数以及总大小已经变小
- Unformatted Blocks ..................... 0
- FS1 Blocks (0-25) ...................... 1
- FS2 Blocks (25-50) ..................... 1
- FS3 Blocks (50-75) ..................... 0
- FS4 Blocks (75-100)..................... 0
- Full Blocks ............................ 14,318
- Total Blocks............................ 14,488
- Total Bytes............................. 118,685,696
- Total MBytes............................ 113
- Unused Blocks........................... 5
- Unused Bytes............................ 40,960
- Last Used Ext FileId.................... 4
- Last Used Ext BlockId................... 16,521
- Last Used Block......................... 147
- PL/SQL procedure successfully completed.
五、语法总结:SQL> delete from big_table where rownum<8000; -->再次删除一些记录 7999 rows deleted. SQL> alter table big_table shrink space compact; -->使用compact方式收缩表段 Table altered. SQL> exec show_space('BIG_TABLE','SCOTT'); Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 1 FS2 Blocks (25-50) ..................... 2 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 103 Full Blocks ............................ 14,214 --仅有的变化为14318-14214=104块,即完全填满的数据块减少了104块 Total Blocks............................ 14,488 --数据的总块数及总大小并没有减少,即未移动高水位线 Total Bytes............................. 118,685,696 Total MBytes............................ 113 Unused Blocks........................... 5 Unused Bytes............................ 40,960 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 16,521 Last Used Block......................... 147 PL/SQL procedure successfully completed. SQL> alter table big_table shrink space cascade; -->使用cascade方式收缩, Table altered. SQL> exec show_space('BIG_TABLE','SCOTT'); Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 1 FS2 Blocks (25-50) ..................... 2 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 14,214 Total Blocks............................ 14,384 -->总块数及总大小均已减少 Total Bytes............................. 117,833,728 Total MBytes............................ 112 Unused Blocks........................... 4 Unused Bytes............................ 32,768 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 16,521 Last Used Block......................... 44 PL/SQL procedure successfully completed. -->收缩之后索引依然有效 SQL> select OWNER,INDEX_NAME,STATUS from dba_indexes where TABLE_NAME='BIG_TABLE'; OWNER INDEX_NAME STATUS ------------------------------ ------------------------------ -------- SCOTT BIG_TABLE_PK VALID
- SQL> delete from big_table where rownum<8000; -->再次删除一些记录
- 7999 rows deleted.
- SQL> alter table big_table shrink space compact; -->使用compact方式收缩表段
- Table altered.
- SQL> exec show_space('BIG_TABLE','SCOTT');
- Unformatted Blocks ..................... 0
- FS1 Blocks (0-25) ...................... 1
- FS2 Blocks (25-50) ..................... 2
- FS3 Blocks (50-75) ..................... 0
- FS4 Blocks (75-100)..................... 103
- Full Blocks ............................ 14,214 --仅有的变化为14318-14214=104块,即完全填满的数据块减少了104块
- Total Blocks............................ 14,488 --数据的总块数及总大小并没有减少,即未移动高水位线
- Total Bytes............................. 118,685,696
- Total MBytes............................ 113
- Unused Blocks........................... 5
- Unused Bytes............................ 40,960
- Last Used Ext FileId.................... 4
- Last Used Ext BlockId................... 16,521
- Last Used Block......................... 147
- PL/SQL procedure successfully completed.
- SQL> alter table big_table shrink space cascade; -->使用cascade方式收缩,
- Table altered.
- SQL> exec show_space('BIG_TABLE','SCOTT');
- Unformatted Blocks ..................... 0
- FS1 Blocks (0-25) ...................... 1
- FS2 Blocks (25-50) ..................... 2
- FS3 Blocks (50-75) ..................... 0
- FS4 Blocks (75-100)..................... 0
- Full Blocks ............................ 14,214
- Total Blocks............................ 14,384 -->总块数及总大小均已减少
- Total Bytes............................. 117,833,728
- Total MBytes............................ 112
- Unused Blocks........................... 4
- Unused Bytes............................ 32,768
- Last Used Ext FileId.................... 4
- Last Used Ext BlockId................... 16,521
- Last Used Block......................... 44
- PL/SQL procedure successfully completed.
- -->收缩之后索引依然有效
- SQL> select OWNER,INDEX_NAME,STATUS from dba_indexes where TABLE_NAME='BIG_TABLE';
- OWNER INDEX_NAME STATUS
- ------------------------------ ------------------------------ --------
- SCOTT BIG_TABLE_PK VALID
ALTER TABLE
- ALTER TABLE
ENABLE ROW MOVEMENT -->前提条件- ALTER TABLE
SHRINK SPACE [ <NULL> | COMPACT | CASCADE ];- ALTER TABLE
SHRINK SPACE COMPCAT; -->缩小表和索引,不移动高水位线,不释放空间- ALTER TABLE
SHRINK SPACE; -->收缩表,降低高水位线;- ALTER TABLE
SHRINK SPACE CASCADE; -->收缩表,降低高水位线,并且相关索引也要收缩一下- ALTER TABLE
MODIFY LOB (lob_column) (SHRINK SPACE); -->收缩LOB段 - ALTER INDEX IDXNAME SHRINK SPACE; -->索引段的收缩,同表段
ENABLE ROW MOVEMENT -->前提条件 ALTER TABLE SHRINK SPACE [ | COMPACT | CASCADE ]; ALTER TABLE SHRINK SPACE COMPCAT; -->缩小表和索引,不移动高水位线,不释放空间 ALTER TABLE SHRINK SPACE; -->收缩表,降低高水位线; ALTER TABLE SHRINK SPACE CASCADE; -->收缩表,降低高水位线,并且相关索引也要收缩一下 ALTER TABLE MODIFY LOB (lob_column) (SHRINK SPACE); -->收缩LOB段 ALTER INDEX IDXNAME SHRINK SPACE; -->索引段的收缩,同表段
2. 分区表的处理select'alter table '||table_name||' enable row movement;' ||chr(10)||'alter table '||table_name||' shrink space;'||chr(10)from user_tables; select'alter index '||index_name||' shrink space;'||chr(10)from user_indexes;
- select'alter table '||table_name||' enable row movement;'
- ||chr(10)||'alter table '||table_name||' shrink space;'||chr(10)from user_tables;
- select'alter index '||index_name||' shrink space;'||chr(10)from user_indexes;
3. 附show_space脚本(来自Tom大师)--根据相应需求修改下面的语句生产相应脚本 select 'alter table '||owner||'.'||table_name||' enable row movement;' ||chr(10)||'alter table '||owner||'.'||table_name||' shrink space;'||chr(10) from dba_tables where wner=upper('&input_owner'); select 'alter index '||owner||'.'||index_name||' shrink space;' ||chr(10) from dba_indexes where uniqueness='NONUNIQUE' ; select 'alter table '||owner||'.'||segment_name||' modify partition '||partition_name||' shrink space;' ||chr(10) from dba_segments where segment_type='TABLE PARTITION';
- --根据相应需求修改下面的语句生产相应脚本
- select 'alter table '||owner||'.'||table_name||' enable row movement;'
- ||chr(10)||'alter table '||owner||'.'||table_name||' shrink space;'||chr(10) from dba_tables
- where owner=upper('&input_owner');
- select 'alter index '||owner||'.'||index_name||' shrink space;'
- ||chr(10) from dba_indexes where uniqueness='NONUNIQUE' ;
- select 'alter table '||owner||'.'||segment_name||' modify partition '||partition_name||' shrink space;'
- ||chr(10) from dba_segments where segment_type='TABLE PARTITION';
- 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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22578826/viewspace-703509/,如需转载,请注明出处,否则将追究法律责任。