SHOW_SPACE過程

SHOW_SPACE例程用於列印資料庫段空間利用率資訊:

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;

/

建立如下:

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

9 authid current_user

10 as

11 l_free_blks number;

12 l_total_blocks number;

13 l_total_bytes 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 )

30 is

31 begin

32 dbms_output.put_line( rpad(p_label,40,'.') ||

33 to_char(p_num,'999,999,999,999') );

34 end;

35 begin

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

-- views!

40 41 -- this query determines if the object is an ASSM object or not

42 begin

43 execute immediate

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;

53 exception

54 when too_many_rows then

55 dbms_output.put_line

56 ( 'This must be a partitioned table, use p_partition => ');

57 return;

58 end;

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

74 else

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

82 end if;

83 -- and then the unused space API call to get the rest of the

84 -- information

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

105 end;

106 /

Procedure created.

show_space過程包含以下引數:

sys@DEVELOP> desc show_space

PROCEDURE show_space

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

P_SEGNAME VARCHAR2 IN

P_OWNER VARCHAR2 IN DEFAULT

P_TYPE VARCHAR2 IN DEFAULT

P_PARTITION VARCHAR2 IN DEFAULT

引數如下：

P_SEGNAME：段名（例如，表或索引名）。

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組，還需要修改這個指令碼。

自定義show_space過程來顯示資料段的利用資訊

相關文章