show_space

      这是Oracle大神TOM写的一个好工具SHOW_SPACE；它实际上就是一个存储过程，这个存储过程可以很高效的分析空间使用情况，有了此工具，就不用再通过写SQL语句来看每条记录或表占用表空间的大小了，使用起来非常方便。

一、创建存储过程

1. create or replace procedure show_space
   
2. ( p_segname_1 in varchar2,
   
3. p_owner_1 in varchar2 default user,
   
4. p_type_1 in varchar2 default 'TABLE',
   
5. p_space in varchar2 default 'AUTO',
   
6. p_analyzed in varchar2 default 'Y'
   
7. )
   
8. as
   
9. p_segname varchar2(100);
   
10. p_type varchar2(10);
    
11. p_owner varchar2(30);
    
12.  
    
13. l_unformatted_blocks number;
    
14. l_unformatted_bytes number;
    
15. l_fs1_blocks number;
    
16. l_fs1_bytes number;
    
17. l_fs2_blocks number;
    
18. l_fs2_bytes number;
    
19. l_fs3_blocks number;
    
20. l_fs3_bytes number;
    
21. l_fs4_blocks number;
    
22. l_fs4_bytes number;
    
23. l_full_blocks number;
    
24. l_full_bytes number;
    
25.  
    
26. l_free_blks number;
    
27. l_total_blocks number;
    
28. l_total_bytes number;
    
29. l_unused_blocks number;
    
30. l_unused_bytes number;
    
31. l_LastUsedExtFileId number;
    
32. l_LastUsedExtBlockId number;
    
33. l_LAST_USED_BLOCK number;
    
34.  
    
35. procedure p( p_label in varchar2, p_num in number )
    
36. is
    
37. begin
    
38. dbms_output.put_line( rpad(p_label,40,'.') ||
    
39. p_num );
    
40. end;
    
41. begin
    
42. p_segname := upper(p_segname_1); -- rainy changed
    
43. p_owner := upper(p_owner_1);
    
44. p_type := p_type_1;
    
45.  
    
46. if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed
    
47. p_type := 'INDEX';
    
48. end if;
    
49.  
    
50. if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed
    
51. p_type := 'TABLE';
    
52. end if;
    
53.  
    
54. if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed
    
55. p_type := 'CLUSTER';
    
56. end if;
    
57.  
    
58.  
    
59. dbms_space.unused_space
    
60. ( segment_owner => p_owner,
    
61. segment_name => p_segname,
    
62. segment_type => p_type,
    
63. total_blocks => l_total_blocks,
    
64. total_bytes => l_total_bytes,
    
65. unused_blocks => l_unused_blocks,
    
66. unused_bytes => l_unused_bytes,
    
67. LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
    
68. LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
    
69. LAST_USED_BLOCK => l_LAST_USED_BLOCK );
    
70.  
    
71. if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
    
72. dbms_space.free_blocks
    
73. ( segment_owner => p_owner,
    
74. segment_name => p_segname,
    
75. segment_type => p_type,
    
76. freelist_group_id => 0,
    
77. free_blks => l_free_blks );
    
78.  
    
79. p( 'Free Blocks', l_free_blks );
    
80. end if;
    
81.  
    
82. p( 'Total Blocks', l_total_blocks );
    
83. p( 'Total Bytes', l_total_bytes );
    
84. p( 'Unused Blocks', l_unused_blocks );
    
85. p( 'Unused Bytes', l_unused_bytes );
    
86. p( 'Last Used Ext FileId', l_LastUsedExtFileId );
    
87. p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
    
88. p( 'Last Used Block', l_LAST_USED_BLOCK );
    
89.  
    
90.  
    
91. /*IF the segment is analyzed */
    
92. if p_analyzed = 'Y' then
    
93. dbms_space.space_usage(segment_owner => p_owner ,
    
94. segment_name => p_segname ,
    
95. segment_type => p_type ,
    
96. unformatted_blocks => l_unformatted_blocks ,
    
97. unformatted_bytes => l_unformatted_bytes,
    
98. fs1_blocks => l_fs1_blocks,
    
99. fs1_bytes => l_fs1_bytes ,
    
100. fs2_blocks => l_fs2_blocks,
     
101. fs2_bytes => l_fs2_bytes,
     
102. fs3_blocks => l_fs3_blocks ,
     
103. fs3_bytes => l_fs3_bytes,
     
104. fs4_blocks => l_fs4_blocks,
     
105. fs4_bytes => l_fs4_bytes,
     
106. full_blocks => l_full_blocks,
     
107. full_bytes => l_full_bytes);
     
108. dbms_output.put_line(rpad(' ',50,'*'));
     
109. dbms_output.put_line('The segment is analyzed');
     
110. p( '0% -- 25% free space blocks', l_fs1_blocks);
     
111. p( '0% -- 25% free space bytes', l_fs1_bytes);
     
112. p( '25% -- 50% free space blocks', l_fs2_blocks);
     
113. p( '25% -- 50% free space bytes', l_fs2_bytes);
     
114. p( '50% -- 75% free space blocks', l_fs3_blocks);
     
115. p( '50% -- 75% free space bytes', l_fs3_bytes);
     
116. p( '75% -- 100% free space blocks', l_fs4_blocks);
     
117. p( '75% -- 100% free space bytes', l_fs4_bytes);
     
118. p( 'Unused Blocks', l_unformatted_blocks );
     
119. p( 'Unused Bytes', l_unformatted_bytes );
     
120. p( 'Total Blocks', l_full_blocks);
     
121. p( 'Total bytes', l_full_bytes);
     
122.  
     
123. end if;
     
124.  
     
125. end;
     
126. /

二、存储过程使用方法：

1. SQL> create table test01 as select * from all_objects;
   
2. 
   
3. Table created.
   
4. 
   
5. SQL> set serveroutput on;
   
6. 
   
7. SQL> exec show_space('TEST01');
   
8. 
   
9. Total Blocks............................1280
   
10. Total Bytes.............................10485760
    
11. Unused Blocks...........................38
    
12. Unused Bytes............................311296
    
13. Last Used Ext FileId....................5
    
14. Last Used Ext BlockId...................610304
    
15. Last Used Block.........................90
    
16. *************************************************
    
17. The segment is analyzed
    
18. 0% -- 25% free space blocks.............0
    
19. 0% -- 25% free space bytes..............0
    
20. 25% -- 50% free space blocks............0
    
21. 25% -- 50% free space bytes.............0
    
22. 50% -- 75% free space blocks............0
    
23. 50% -- 75% free space bytes.............0
    
24. 75% -- 100% free space blocks...........0
    
25. 75% -- 100% free space bytes............0
    
26. Unused Blocks...........................0
    
27. Unused Bytes............................0
    
28. Total Blocks............................1214
    
29. Total bytes.............................9945088
    
30. 
    
31. PL/SQL procedure successfully completed.
    
32. 
    
33. 
    
34. 
    
35. SQL> delete from TEST01;
    
36. 
    
37. 85054 rows deleted.
    
38. 
    
39. SQL> exec show_space('TEST01');
    
40. Total Blocks............................1280
    
41. Total Bytes.............................10485760
    
42. Unused Blocks...........................38
    
43. Unused Bytes............................311296
    
44. Last Used Ext FileId....................5
    
45. Last Used Ext BlockId...................610304
    
46. Last Used Block.........................90
    
47. *************************************************
    
48. The segment is analyzed
    
49. 0% -- 25% free space blocks.............0
    
50. 0% -- 25% free space bytes..............0
    
51. 25% -- 50% free space blocks............0
    
52. 25% -- 50% free space bytes.............0
    
53. 50% -- 75% free space blocks............0
    
54. 50% -- 75% free space bytes.............0
    
55. 75% -- 100% free space blocks...........1214
    
56. 75% -- 100% free space bytes............9945088
    
57. Unused Blocks...........................0
    
58. Unused Bytes............................0
    
59. Total Blocks............................0
    
60. Total bytes.............................0
    
61. 
    
62. PL/SQL procedure successfully completed.
    
63. 
    
64. 
    
65. SQL> alter table TEST01 move;
    
66. 
    
67. Table altered.
    
68. 
    
69. SQL> exec show_space('TEST01');
    
70. Total Blocks............................8
    
71. Total Bytes.............................65536
    
72. Unused Blocks...........................5
    
73. Unused Bytes............................40960
    
74. Last Used Ext FileId....................5
    
75. Last Used Ext BlockId...................609168
    
76. Last Used Block.........................3
    
77. *************************************************
    
78. The segment is analyzed
    
79. 0% -- 25% free space blocks.............0
    
80. 0% -- 25% free space bytes..............0
    
81. 25% -- 50% free space blocks............0
    
82. 25% -- 50% free space bytes.............0
    
83. 50% -- 75% free space blocks............0
    
84. 50% -- 75% free space bytes.............0
    
85. 75% -- 100% free space blocks...........0
    
86. 75% -- 100% free space bytes............0
    
87. Unused Blocks...........................0
    
88. Unused Bytes............................0
    
89. Total Blocks............................0
    
90. Total bytes.............................0
    
91. 
    
92. PL/SQL procedure successfully completed.
    
93. 
    
94. SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20674423/viewspace-2131884/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20674423/viewspace-2131884/