show_space

  • wuweilong

    2017-01-04 15:08:34
  • Oracle
  • 原创


      这是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;

  3. Table created.

  5. SQL> set serveroutput on;

  7. SQL> exec show_space('TEST01');

  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

  31. PL/SQL procedure successfully completed.



  35. SQL> delete from TEST01;

  37. 85054 rows deleted.

  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

  62. PL/SQL procedure successfully completed.


  65. SQL> alter table TEST01 move;

  67. Table altered.

  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

  92. PL/SQL procedure successfully completed.

  94. SQL>





请使用浏览器的分享功能分享到微信等