ITPub博客

首页 > 数据库 > Oracle > show_space

show_space

原创 Oracle 作者:wuweilong 时间:2017-01-04 15:08:34 0 删除 编辑


      这是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. Table created.

  3. SQL> set serveroutput on;

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

  5. Total Blocks............................1280
  6. Total Bytes.............................10485760
  7. Unused Blocks...........................38
  8. Unused Bytes............................311296
  9. Last Used Ext FileId....................5
  10. Last Used Ext BlockId...................610304
  11. Last Used Block.........................90
  12. *************************************************
  13. The segment is analyzed
  14. 0% -- 25% free space blocks.............0
  15. 0% -- 25% free space bytes..............0
  16. 25% -- 50% free space blocks............0
  17. 25% -- 50% free space bytes.............0
  18. 50% -- 75% free space blocks............0
  19. 50% -- 75% free space bytes.............0
  20. 75% -- 100% free space blocks...........0
  21. 75% -- 100% free space bytes............0
  22. Unused Blocks...........................0
  23. Unused Bytes............................0
  24. Total Blocks............................1214
  25. Total bytes.............................9945088

  26. PL/SQL procedure successfully completed.



  27. SQL> delete from TEST01;

  28. 85054 rows deleted.

  29. SQL> exec show_space('TEST01');
  30. Total Blocks............................1280
  31. Total Bytes.............................10485760
  32. Unused Blocks...........................38
  33. Unused Bytes............................311296
  34. Last Used Ext FileId....................5
  35. Last Used Ext BlockId...................610304
  36. Last Used Block.........................90
  37. *************************************************
  38. The segment is analyzed
  39. 0% -- 25% free space blocks.............0
  40. 0% -- 25% free space bytes..............0
  41. 25% -- 50% free space blocks............0
  42. 25% -- 50% free space bytes.............0
  43. 50% -- 75% free space blocks............0
  44. 50% -- 75% free space bytes.............0
  45. 75% -- 100% free space blocks...........1214
  46. 75% -- 100% free space bytes............9945088
  47. Unused Blocks...........................0
  48. Unused Bytes............................0
  49. Total Blocks............................0
  50. Total bytes.............................0

  51. PL/SQL procedure successfully completed.


  52. SQL> alter table TEST01 move;

  53. Table altered.

  54. SQL> exec show_space('TEST01');
  55. Total Blocks............................8
  56. Total Bytes.............................65536
  57. Unused Blocks...........................5
  58. Unused Bytes............................40960
  59. Last Used Ext FileId....................5
  60. Last Used Ext BlockId...................609168
  61. Last Used Block.........................3
  62. *************************************************
  63. The segment is analyzed
  64. 0% -- 25% free space blocks.............0
  65. 0% -- 25% free space bytes..............0
  66. 25% -- 50% free space blocks............0
  67. 25% -- 50% free space bytes.............0
  68. 50% -- 75% free space blocks............0
  69. 50% -- 75% free space bytes.............0
  70. 75% -- 100% free space blocks...........0
  71. 75% -- 100% free space bytes............0
  72. Unused Blocks...........................0
  73. Unused Bytes............................0
  74. Total Blocks............................0
  75. Total bytes.............................0

  76. PL/SQL procedure successfully completed.

  77. SQL>





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

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2010-01-08

  • 博文量
    251
  • 访问量
    1981274