Oracle Show_space 过程使用示例与注释

一．Show_space 过程源码

1.1 源码

Tom 大师的show_space过程，经pub某位同学完善之后，脚本如下：

CREATE OR REPLACE PROCEDURE show_space (

p_segname_1 IN VARCHAR2,

p_type_1 IN VARCHAR2 DEFAULT 'TABLE',

p_space IN VARCHAR2 DEFAULT'MANUAL',

p_analyzed IN VARCHAR2 DEFAULT 'N',

p_partition_1 IN VARCHAR2 DEFAULTNULL,

p_owner_1 IN VARCHAR2 DEFAULT USER)

AUTHID CURRENT_USER

p_segname VARCHAR2 (100);

p_type VARCHAR2 (30);

p_owner VARCHAR2 (30);

p_partition VARCHAR2 (50);

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;

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;

PROCEDURE p (p_label IN VARCHAR2,p_num IN NUMBER)

BEGIN

DBMS_OUTPUT.put_line (RPAD(p_label, 40, '.') || p_num);

END;

BEGIN

p_segname := UPPER (p_segname_1);

p_owner := UPPER (p_owner_1);

p_type := p_type_1;

p_partition := UPPER(p_partition_1);

IF (p_type_1 = 'i' OR p_type_1 ='I')

THEN

p_type := 'INDEX';

END IF;

IF (p_type_1 = 't' OR p_type_1 ='T')

THEN

p_type := 'TABLE';

END IF;

IF (p_type_1 = 'tp' OR p_type_1 ='TP')

THEN

p_type := 'TABLE PARTITION';

END IF;

IF (p_type_1 = 'ip' OR p_type_1 = 'IP')

THEN

p_type := 'INDEX PARTITION';

END IF;

IF (p_type_1 = 'c' OR p_type_1 ='C')

THEN

p_type := 'CLUSTER';

END IF;

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);

IF p_space = 'MANUAL' OR (p_space<> 'auto' AND p_space <> 'AUTO')

THEN

DBMS_SPACE.FREE_BLOCKS (segment_owner => p_owner,

segment_name =>p_segname,

segment_type => p_type,

partition_name =>p_partition,

freelist_group_id => 0,

free_blks =>l_free_blks);

p ('Free Blocks', l_free_blks);

END IF;

p ('Total Blocks',l_total_blocks);

p ('Total Bytes', l_total_bytes);

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);

/*IF the segment is analyzed */

IF p_analyzed = 'Y'

THEN

DBMS_SPACE.SPACE_USAGE(segment_owner => p_owner,

segment_name => p_segname,

segment_type => p_type,

partition_name =>p_partition,

unformatted_blocks => l_unformatted_blocks,

unformatted_bytes =>l_unformatted_bytes,

fs1_blocks =>l_fs1_blocks,

fs1_bytes =>l_fs1_bytes,

fs2_blocks =>l_fs2_blocks,

fs2_bytes => l_fs2_bytes,

fs3_blocks =>l_fs3_blocks,

fs3_bytes =>l_fs3_bytes,

fs4_blocks =>l_fs4_blocks,

fs4_bytes => l_fs4_bytes,

full_blocks =>l_full_blocks,

full_bytes =>l_full_bytes);

DBMS_OUTPUT.put_line (RPAD ('', 50, '*'));

DBMS_OUTPUT.put_line ('Thesegment is analyzed');

p ('0% -- 25% free spaceblocks', l_fs1_blocks);

p ('0% -- 25% free spacebytes', l_fs1_bytes);

p ('25% -- 50% free spaceblocks', l_fs2_blocks);

p ('25% -- 50% free spacebytes', l_fs2_bytes);

p ('50% -- 75% free spaceblocks', l_fs3_blocks);

p ('50% -- 75% free spacebytes', l_fs3_bytes);

p ('75% -- 100% free spaceblocks', l_fs4_blocks);

p ('75% -- 100% free spacebytes', l_fs4_bytes);

p ('Unused Blocks', l_unformatted_blocks);

p ('Unused Bytes',l_unformatted_bytes);

p ('Total Blocks',l_full_blocks);

p ('Total bytes',l_full_bytes);

END IF;

END;

1.2 使用示例

1.2.1 MSSM 管理表空间下示例

SQL> create tablespace mssm

2 datafile'/u01/app/oracle/oradata/dave/mssm01.dbf'

3 size 100m

4 extent management local

5 segment space managementmanual;

Tablespace created.

SQL> create table t_mssm tablespace mssmas select * from dba_objects;

Table created.

SQL> create index idx_mssm ont_mssm(object_id) tablespace mssm;

Index created.

SQL> set serveroutputon

SQL> execshow_space('T_MSSM','T','MANUAL');

FreeBlocks.............................0 --由DBMS_SPACE.FREE_BLOCKS输出

TotalBlocks............................1152 --以下由DBMS_SPACE.UNUSED_SPACE输出

TotalBytes.............................9437184

Unused Blocks...........................81

UnusedBytes............................663552

Last Used Ext FileId....................6

Last Used ExtBlockId...................1152

Last Used Block.........................47

PL/SQL procedure successfully completed.

SQL> execshow_space('IDX_MSSM','I','MANUAL');

Free Blocks.............................0

Total Blocks............................256

TotalBytes.............................2097152

Unused Blocks...........................87

UnusedBytes............................712704

Last Used Ext FileId....................6

Last Used Ext BlockId...................1408

Last Used Block.........................41

PL/SQL procedure successfully completed.

SQL> execshow_space('T_MSSM','T');

Free Blocks.............................0

TotalBlocks............................1152

TotalBytes.............................9437184

Unused Blocks...........................81

UnusedBytes............................663552

Last Used Ext FileId....................6

Last Used ExtBlockId...................1152

Last Used Block.........................47

PL/SQL procedure successfully completed.

1.2.2 ASSM 管理表空间下示例

SQL> create tablespace assm

2 datafile'/u01/app/oracle/oradata/dave/assm01.dbf'

3 size 100m

4 extent management local

5 segment space managementauto;

Tablespace created.

SQL> create table t_assm tablespace assmas select * from dba_objects;

Table created.

SQL> create index idx_assm ont_assm(object_id) tablespace assm;

Index created.

SQL> execshow_space('T_ASSM','AUTO','T');

TotalBlocks............................1152

Total Bytes.............................9437184

Unused Blocks...........................54

UnusedBytes............................442368

Last Used Ext FileId....................7

Last Used ExtBlockId...................1152

Last Used Block.........................74

PL/SQL procedure successfully completed.

SQL> exec show_space('IDX_ASSM','AUTO','I');

Total Blocks............................256

TotalBytes.............................2097152

Unused Blocks...........................76

UnusedBytes............................622592

Last Used Ext FileId....................7

Last Used ExtBlockId...................1408

Last Used Block.........................52

PL/SQL procedure successfully completed.

注意：脚本里并显示我们DBMS_SPACE.SPACE_USAGE的内容。因为我们之前有一个判断。

SQL> execshow_space('T_ASSM','AUTO','T',NULL,'Y');

TotalBlocks............................1152

TotalBytes.............................9437184

UnusedBlocks...........................54

Unused Bytes............................442368

Last Used ExtFileId....................7

Last Used ExtBlockId...................1152

Last UsedBlock.........................74

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free spacebytes..............0

25% -- 50% free spaceblocks............0

25% -- 50% free spacebytes.............0

50% -- 75% free spaceblocks............0

50% -- 75% free spacebytes.............0

75% -- 100% free space blocks...........0

75% -- 100% free spacebytes............0

UnusedBlocks...........................0

UnusedBytes............................0

TotalBlocks............................1072

Totalbytes.............................8781824

PL/SQL procedure successfullycompleted.

--删除部分数据后，在测试：

SQL> delete fromt_assm where rownum<100;

99 rows deleted.

SQL> commit;

Commit complete.

SQL> execshow_space('T_ASSM','T','AUTO','Y',NULL);

TotalBlocks............................1152

TotalBytes.............................9437184

Unused Blocks...........................54

UnusedBytes............................442368

Last Used Ext FileId....................7

Last Used ExtBlockId...................1152

Last Used Block.........................74

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............0

25% -- 50% free space bytes.............0

50% -- 75% free space blocks............0

50% -- 75% free space bytes.............0

75% -- 100% free space blocks...........1

75% -- 100% free spacebytes............8192

Unused Blocks...........................0

Unused Bytes............................0

TotalBlocks............................1071

Total bytes.............................8773632

PL/SQL procedure successfully completed.

--对表t_assm 收集统计信息：

SQL> execdbms_stats.gather_table_stats(ownname =>'&owner',tabname=>'&tablename',estimate_percent => &est_per ,method_opt =>'forall columns size 1',degree=>&degree,cascade => true);

Enter value for owner: sys

Enter value for tablename: t_assm

Enter value for est_per: 10

Enter value for degree: 8

PL/SQL procedure successfully completed.

SQL> exec show_space('T_ASSM','AUTO','T',NULL,'Y');

TotalBlocks............................1152

TotalBytes.............................9437184

Unused Blocks...........................54

UnusedBytes............................442368

Last Used Ext FileId....................7

Last Used ExtBlockId...................1152

Last Used Block.........................74

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............0

25% -- 50% free space bytes.............0

50% -- 75% free space blocks............0

50% -- 75% free space bytes.............0

75% -- 100% free space blocks...........1

75% -- 100% free spacebytes............8192

Unused Blocks...........................0

Unused Bytes............................0

TotalBlocks............................1071

Total bytes.............................8773632

PL/SQL procedure successfully completed.

--两次收集信息一致。

再次使用analyze 分析：

SQL> delete from t_assm;

75155 rows deleted.

SQL> commit;

Commit complete.

SQL> execshow_space('T_ASSM','T','AUTO','Y',NULL);

Total Blocks............................1152

TotalBytes.............................9437184

Unused Blocks...........................54

UnusedBytes............................442368

Last Used Ext FileId....................7

Last Used ExtBlockId...................1152

Last Used Block.........................74

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............0

25% -- 50% free space bytes.............0

50% -- 75% free space blocks............0

50% -- 75% free space bytes.............0

75% -- 100% free spaceblocks...........1072

75% -- 100% free spacebytes............8781824

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................0

Total bytes.............................0

PL/SQL procedure successfully completed.

SQL> analyzetable t_assm compute statistics;

Table analyzed.

SQL> exec show_space('T_ASSM','T','AUTO','Y',NULL);

TotalBlocks............................1152

TotalBytes.............................9437184

Unused Blocks...........................54

UnusedBytes............................442368

Last Used Ext FileId....................7

Last Used ExtBlockId...................1152

Last Used Block.........................74

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............0

25% -- 50% free space bytes.............0

50% -- 75% free space blocks............0

50% -- 75% free space bytes.............0

75% -- 100% free spaceblocks...........1072

75% -- 100% free spacebytes............8781824

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................0

Total bytes.............................0

PL/SQL procedure successfully completed.

我这里测试，两者值相同，但实际上，Analyze 会收集如下信息：EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT，而gather_table_stats则不会收集。

--测试分区表,查看某个分区的信息：

SQL> create table pt_assm

2 (

3 object_id number,

4 created date

5 ) tablespace assm

6 partition by hash(object_id)

7 (

8 partition part_01,

9 partition part_02,

10 partition part_03

11 );

Table created.

SQL> insert into pt_assm select object_id,createdfrom dba_objects;

75259 rows created.

SQL> commit;

Commit complete.

SQL> Create index idx_pt_assm onpt_assm(object_id) tablespace assm local;

Index created.

SQL> execshow_space('PT_ASSM','TP','AUTO','Y','PART_01');

TotalBlocks............................1024

TotalBytes.............................8388608

Unused Blocks...........................896

UnusedBytes............................7340032

Last Used Ext FileId....................7

Last Used Ext BlockId...................1536

Last Used Block.........................128

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............0

25% -- 50% free space bytes.............0

50% -- 75% free space blocks............1

50% -- 75% free spacebytes.............8192

75% -- 100% free space blocks...........15

75% -- 100% free spacebytes............122880

Unused Blocks...........................48

UnusedBytes............................393216

Total Blocks............................46

Totalbytes.............................376832

PL/SQL procedure successfully completed.

SQL> execshow_space('IDX_PT_ASSM','IP','AUTO','Y','PART_01');

Total Blocks............................48

TotalBytes.............................393216

Unused Blocks...........................0

Unused Bytes............................0

Last Used Ext FileId....................7

Last Used ExtBlockId...................4648

Last Used Block.........................8

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............1

25% -- 50% free spacebytes.............8192

50% -- 75% free space blocks............0

50% -- 75% free space bytes.............0

75% -- 100% free space blocks...........0

75% -- 100% free space bytes............0

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................42

Totalbytes.............................344064

PL/SQL procedure successfully completed.

二．相关知识点说明

2.1 表空间管理模式说明

在看show_space过程里函数之前，需要先了解Oracle 表空间的管理模式。

LogicalSpace Management分：Locally managed tablespaces (default) 和 Dictionary-managed tablespaces。其中Locallymanaged tablespaces 又分ASSM和MSSM。

自动段空间管理（ASSM），在ASSM中链接列表freelist(MSSM)被位图所取代，它是一个二进制的数组，能够迅速有效地管理存储扩展和剩余区块（free block），因此能够改善分段存储本质，ASSM表空间上创建的段还有另外一个称呼叫Bitmap Managed Segments（BMB 段）。

从使用区段空间管理自动参数创建tablespace开始：

create tablespace DAVE

datafile '/u01/data/dave01.dbf '

size 5m

EXTENT MANAGEMENT LOCAL -- Turn on LMT

SEGMENT SPACE MANAGEMENT AUTO -- Turn on ASSM;

一旦你定义好了tablespace，那么表和索引就能够使用各种方法很容易地被移动到新的tablespace里，带有ASSM的本地管理tablespace会略掉任何为PCTUSED、NEXT和FREELISTS所指定的值。

当表格或者索引被分配到这个tablespace以后，用于独立对象的PCTUSED的值会被忽略，而Oracle9i会使用位图数组来自动地管理tablespace里表和索引的freelist。

对于在LMT的tablespace内部创建的表格和索引而言，这个NEXT扩展子句是过时的，因为由本地管理的tablespace会管理它们。但是，INITIAL参数仍然是需要的，因为Oracle不可能提前知道初始表格加载的大小。对于ASSM而言，INITIAL最小的值是三个块。

新的管理机制(ASSM)用位图来跟踪或管理每个分配到对象的块，每个块有多少剩余空间根据位图的状态来确定，如>75%,50%-75%,25%-50%和<25%，也就是说位图其实采用了四个状态位来代替以前的pctused，什么时候该利用该数据块则由设定的pctfree来确定。

使用ASSM的一个巨大优势是，位图free list(MSSM)肯定能够减轻缓冲区忙等待（buffer busy wait）的负担，这个问题在Oracle9i以前的版本里曾是一个严重的问题。

关于Oracle 表空间的更多说明，参考：

Oracle 自动段空间管理(ASSM:auto segment space management)

http://blog.csdn.net/tianlesoftware/article/details/4958989

几个过程都是出自DBMS_SPACE包，Oracle 11gR2 官方文档中的说明，参考如下链接：

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_space.htm

2.2 DBMS_SPACE.UNUSED_SPACE 过程

Returns information about unused space in anobject (table, index, or cluster)

语法：

DBMS_SPACE.UNUSED_SPACE (

segment_owner IN VARCHAR2,

segment_name IN VARCHAR2,

segment_type IN VARCHAR2,

total_blocks OUTNUMBER,

total_bytes OUTNUMBER,

unused_blocks OUTNUMBER,

unused_bytes OUTNUMBER,

last_used_extent_file_id OUTNUMBER,

last_used_extent_block_id OUTNUMBER,

last_used_block OUTNUMBER,

partition_name IN VARCHAR2 DEFAULT NULL);

参数说明：

Parameter	Description
segment_owner	Schema name of the segment to be analyzed
segment_name	Segment name of the segment to be analyzed
segment_type	Type of the segment to be analyzed (TABLE, INDEX, or CLUSTER): TABLE TABLE PARTITION TABLE SUBPARTITION INDEX INDEX PARTITION INDEX SUBPARTITION CLUSTER LOB LOB PARTITION LOB SUBPARTITION
total_blocks	Returns total number of blocks in the segment
total_bytes	Returns total number of blocks in the segment, in bytes
unused_blocks	Returns number of blocks which are not used
unused_bytes	Returns, in bytes, number of blocks which are not used
last_used_extent_file_id	Returns the file ID of the last extent which contains data
last_used_extent_block_id	Returns the starting block ID of the last extent which contains data
last_used_block	Returns the last block within this extent which contains data
partition_name	Partition name of the segment to be analyzed. This is only used for partitioned tables; the name of subpartition should be used when partitioning is compose.

2.3. DBMS_SPACE.FREE_BLOCKS 过程（只用MSSM）

This procedure returns information about free blocks in an object (table, index,or cluster). 该过程只适用非ASSM 管理的对象（MSSM）。

用法：

DBMS_SPACE.FREE_BLOCKS (

segment_owner IN VARCHAR2,

segment_name IN VARCHAR2,

segment_type IN VARCHAR2,

freelist_group_id IN NUMBER,

free_blks OUT NUMBER,

scan_limit IN NUMBER DEFAULT NULL,

partition_name IN VARCHAR2 DEFAULT NULL);

参数说明：

Parameter	Description
segment_owner	Schema name of the segment to be analyzed
segment_name	Segment name of the segment to be analyzed
segment_type	Type of the segment to be analyzed (TABLE, INDEX, or CLUSTER): TABLE TABLE PARTITION TABLE SUBPARTITION INDEX INDEX PARTITION INDEX SUBPARTITION CLUSTER LOB LOB PARTITION LOB SUBPARTITION
freelist_group_id	Freelist group (instance) whose free list size is to be computed
free_blks	Returns count of free blocks for the specified group
scan_limit	Maximum number of free list blocks to read (optional). Use a scan limit of X you are interested only in the question, "Do I have X blocks on the free list?"
partition_name	Partition name of the segment to be analyzed. This is only used for partitioned tables. The name of subpartition should be used when partitioning is composite.

2.4. DBMS_SPACE.SPACE_USAGE过程（只用ASSM）

The first form of the procedure shows the space usage of datablocks under the segment High Water Mark. You cancalculate usagefor LOBs, LOB PARTITIONSand LOB SUBPARTITIONS. This procedure can only be used on tablespaces that arecreated with auto segment space management（该过程返回ASSM 管理的对象的free blocks信息（ASSM））. The bitmap blocks, segment header,and extent map blocks are not accounted for by this procedure. Notethat this overload cannot be used on SECUREFILE LOBs.

The second formof the procedure returns information about SECUREFILE LOB spaceusage. It will return the amount of space in blocks being used by all the SECUREFILE LOBsin the LOB segment. The procedure displays the space actively used bythe LOB column, freed space that has retention expired, and freed space thathas retention unexpired. Note that this overload can be used onlyon SECUREFILE LOBs.

语法：

DBMS_SPACE.SPACE_USAGE(

segment_owner IN VARCHAR2,

segment_name IN VARCHAR2,

segment_type IN VARCHAR2,

unformatted_blocks OUTNUMBER,

unformatted_bytes OUTNUMBER,

fs1_blocks OUTNUMBER,

fs1_bytes OUTNUMBER,

fs2_blocks OUTNUMBER,

fs2_bytes OUTNUMBER,

fs3_blocks OUTNUMBER,

fs3_bytes OUTNUMBER,

fs4_blocks OUTNUMBER,

fs4_bytes OUTNUMBER,

full_blocks OUTNUMBER,

full_bytes OUTNUMBER,

partition_name IN VARCHAR2 DEFAULT NULL);

或者：

DBMS_SPACE.SPACE_USAGE(

segment_owner IN VARCHAR2,

segment_name IN VARCHAR2,

segment_type IN VARCHAR2,

segment_size_blocks OUT NUMBER,

segment_size_bytes OUT NUMBER,

used_blocks OUT NUMBER,

used_bytes OUT NUMBER,

expired_blocks OUT NUMBER,

expired_bytes OUT NUMBER,

unexpired_blocks OUT NUMBER,

unexpired_bytes OUT NUMBER,

partition_name IN VARCHAR2 DEFAULT NULL);

参数说明：

Parameter	Description
segment_owner	Schema name of the segment to be analyzed
segment_name	Name of the segment to be analyzed
partition_name	Partition name of the segment to be analyzed
segment_type	Type of the segment to be analyzed (TABLE, INDEX, or CLUSTER): TABLE TABLE PARTITION TABLE SUBPARTITION INDEX INDEX PARTITION INDEX SUBPARTITION CLUSTER LOB LOB PARTITION LOB SUBPARTITION
unformatted_blocks	Total number of blocks unformatted
unformatted bytes	Total number of bytes unformatted
fs1_blocks	Number of blocks having at least 0 to 25% free space
fs1_bytes	Number of bytes having at least 0 to 25% free space
fs2_blocks	Number of blocks having at least 25 to 50% free space
fs2_bytes	Number of bytes having at least 25 to 50% free space
fs3_blocks	Number of blocks having at least 50 to 75% free space
fs3_bytes	Number of bytes having at least 50 to 75% free space
fs4_blocks	Number of blocks having at least 75 to 100% free space
fs4_bytes	Number of bytes having at least 75 to 100% free space
ful1_blocks	Total number of blocks full in the segment
full_bytes	Total number of bytes full in the segment
segment_size_blocks	Number of blocks allocated to the segment
segment_size_bytes	Number of bytes allocated to the segment
used_blocks	Number blocks allocated to the LOB that contains active data
used_bytes	Number bytes allocated to the LOB that contains active data
expired_blocks	Number of expired blocks used by the LOB to keep version data
expired_bytes	Number of expired bytes used by the LOB to keep version data
unexpired_blocks	Number of unexpired blocks used by the LOB to keep version data
unexpired_bytes	Number of unexpired bytes used by the LOB to keep version data
partition_name	Name of the partition (NULL if not a partition)