SHOW_SPACE例程用于打印数据库段空间利用率信息:
create or replace procedure show_space
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
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
procedure p( p_label in varchar2, p_num in number )
dbms_output.put_line( rpad(p_label,40,'.') ||
to_char(p_num,'999,999,999,999') );
-- 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
-- this query determines if the object is an ASSM object or not
'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.tablespace_name = ts.tablespace_name'
into l_segment_space_mgmt
using p_segname, p_partition, p_partition, p_owner;
( 'This must be a partitioned table, use p_partition => ');
-- 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
( 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 );
segment_owner => p_owner,
segment_name => p_segname,
free_blks => l_free_blks);
p( 'Free Blocks', l_free_blks );
-- and then the unused space API call to get the rest of the
( segment_owner => p_owner,
segment_name => p_segname,
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 );
sys@DEVELOP> create or replace procedure show_space
2 ( p_segname in varchar2,
3 p_owner in varchar2 default user,
4 p_type in varchar2 default 'TABLE',
5 p_partition in varchar2 default NULL )
6 -- this procedure uses authid current user so it can query DBA_*
7 -- views using privileges from a ROLE, and so it can be installed
8 -- once per database, instead of once per user who wanted to use it
12 l_total_blocks number;
14 l_unused_blocks number;
15 l_unused_bytes number;
16 l_LastUsedExtFileId number;
17 l_LastUsedExtBlockId number;
18 l_LAST_USED_BLOCK number;
19 l_segment_space_mgmt varchar2(255);
20 l_unformatted_blocks number;
21 l_unformatted_bytes number;
22 l_fs1_blocks number; l_fs1_bytes number;
23 l_fs2_blocks number; l_fs2_bytes number;
24 l_fs3_blocks number; l_fs3_bytes number;
25 l_fs4_blocks number; l_fs4_bytes number;
26 l_full_blocks number; l_full_bytes number;
27 -- inline procedure to print out numbers nicely formatted
28 -- with a simple label
29 procedure p( p_label in varchar2, p_num in number )
32 dbms_output.put_line( rpad(p_label,40,'.') ||
33 to_char(p_num,'999,999,999,999') );
36 -- this query is executed dynamically in order to allow this procedure
37 -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
38 -- via a role as is customary.
39 -- NOTE: at runtime, the invoker MUST have access to these two
40 41 -- this query determines if the object is an ASSM object or not
44 'select ts.segment_space_management
45 from dba_segments seg, dba_tablespaces ts
46 where seg.segment_name = :p_segname
and (:p_partition is null or
47 48 seg.partition_name = :p_partition)
49 and seg.owner = :p_owner
50 and seg.tablespace_name = ts.tablespace_name'
51 into l_segment_space_mgmt
52 using p_segname, p_partition, p_partition, p_owner;
54 when too_many_rows then
56 ( 'This must be a partitioned table, use p_partition => ');
59 -- if the object is in an ASSM tablespace, we must use this API
60 -- call to get space information, otherwise we use the FREE_BLOCKS
61 -- API for the user-managed segments
62 if l_segment_space_mgmt = 'AUTO' then
63 dbms_space.space_usage
64 ( p_owner, p_segname, p_type, l_unformatted_blocks,
65 l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
66 l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
67 l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
68 p( 'Unformatted Blocks ', l_unformatted_blocks );
69 p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
70 p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
71 p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
72 p( 'FS4 Blocks (75-100)', l_fs4_blocks );
73 p( 'Full Blocks ', l_full_blocks );
75 dbms_space.free_blocks(
76 segment_owner => p_owner,
77 segment_name => p_segname,
78 segment_type => p_type,
79 freelist_group_id => 0,
80 free_blks => l_free_blks);
81 p( 'Free Blocks', l_free_blks );
83 -- and then the unused space API call to get the rest of the
85 dbms_space.unused_space
86 ( segment_owner => p_owner,
87 segment_name => p_segname,
88 segment_type => p_type,
89 partition_name => p_partition,
90 total_blocks => l_total_blocks,
91 total_bytes => l_total_bytes,
92 unused_blocks => l_unused_blocks,
93 unused_bytes => l_unused_bytes,
94 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
95 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
96 LAST_USED_BLOCK => l_LAST_USED_BLOCK );
97 p( 'Total Blocks', l_total_blocks );
98 p( 'Total Bytes', l_total_bytes );
99 p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
100 p( 'Unused Blocks', l_unused_blocks );
101 p( 'Unused Bytes', l_unused_bytes );
102 p( 'Last Used Ext FileId', l_LastUsedExtFileId );
103 p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
104 p( 'Last Used Block', l_LAST_USED_BLOCK );
sys@DEVELOP> desc show_space
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_OWNER VARCHAR2 IN DEFAULT
P_TYPE VARCHAR2 IN DEFAULT
P_PARTITION VARCHAR2 IN DEFAULT
P_OWNER：默认为当前用户，不过也可以使用这个例程查看另外某个用户。
P_TYPE：默认为TABLE，这个参数表示查看哪种类型的对象（段）。例如，SELECT DISTINCT SEGMENT_TYPE FROM DBA_SEGMENTS 会列出合法的段类型。
?P_PARTITION：显示分区对象的空间时所用的分区名。SHOW_SPACE一次只显示一个分区的空间利用率。
这个过程的输出如下，这里段位于一个自动段空间管理（Automatic Segment Space Management， ASSM）表空间中：
sys@DEVELOP> exec show_space('AC01','HNSIC');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 1
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 12
Full Blocks ............................ 16,305
Total Blocks............................ 146,304
Total Bytes............................. 1,198,522,368
Total MBytes............................ 1,143
Unused Blocks........................... 129,920
Unused Bytes............................ 1,064,304,640
Last Used Ext FileId.................... 10
Last Used Ext BlockId................... 348,800
Last Used Block......................... 8,192
PL/SQL procedure successfully completed.
Unformatted Blocks：为表分配的位于高水位线（high-water mark, HWM）之下但未用的块数。把未格式化和未用的块加在一 起，就是已为表分配但从未用于保存ASSM对象数据的总块数。
FS1 Blocks-FS4 Blocks：包含数据的格式化块。项名后的数字区间表示各块的“空闲度”。例如，（0-25）是指空闲度为 0~25%的块数。
Full Blocks：已满的块数，不能再对这些执行插入。
Total Blocks、Total bytes、Total Mbytes：为所查看的段分配的总空间量，单位分别是数据库块、字节和兆字节。
Unused Blocks、Unused Bytes：表示未用空间所占的比例（未用空间量）。这些块已经分配给所查看的段，但目前在段的HWM 之上。
Last Used Ext FileId：最后使用的文件的文件ID，该文件包含最后一个含数据的区段（extent）。
Last Used Ext BlockId：最后一个区段开始处的块ID；这是最后使用的文件中的块ID。
Last Used Block：最后一个区段中最后一个块的偏移量。
如果对象在用户空间管理的表空间中，使用SHOW_SPACE查看时，输出如下：
sys@DEVELOP> exec show_space('T1');
Free Blocks............................. 3
Total Blocks............................ 17,408
Total Bytes............................. 142,606,336
Total MBytes............................ 136
Unused Blocks........................... 869
Unused Bytes............................ 7,118,848
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 1,696,896
Last Used Block......................... 155
PL/SQL procedure successfully completed.
这里惟一的区别是报告中最前面的Free Blocks项。这是段的第一个freelist（自由列表）组中的块数。
脚本只测试了第一个freelist组。如果想测试多个freelist组，还需要修改这个脚本。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26015009/viewspace-769312/，如需转载，请注明出处，否则将追究法律责任。