表空间统计报告 Tablespace growth Report

  1. SQL> select TS# from v$tablespace where name='ABC' ;
  3.        TS#
  4. ----------
  5.          6
  7. set serverout on
  8. set verify off
  9. set lines 200
  10. set pages 2000
  11. DECLARE
  12. v_ts_id number;
  13. not_in_awr EXCEPTION;
  14. v_ts_name varchar2(200) := UPPER('&Tablespace_Name');
  15. v_ts_block_size number;
  16. v_begin_snap_id number;
  17. v_end_snap_id number;
  18. v_begin_snap_date date;
  19. v_end_snap_date date;
  20. v_numdays number;
  21. v_ts_begin_size number;
  22. v_ts_end_size number;
  23. v_ts_growth number;
  24. v_count number;
  25. v_ts_begin_allocated_space number;
  26. v_ts_end_allocated_space number;
  27. BEGIN
  28. SELECT ts# into v_ts_id FROM v$tablespace where name = v_ts_name;
  29. SELECT count(*) INTO v_count FROM dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
  30. IF v_count = 0 THEN
  31. RAISE not_in_awr;
  32. END IF ;
  33. SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_ts_name;
  34. SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
  35. into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
  36. v_numdays := v_end_snap_date - v_begin_snap_date;
  38. SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
  39. SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
  40. SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
  41. SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
  42. v_ts_growth := v_ts_end_size - v_ts_begin_size;
  43. DBMS_OUTPUT.PUT_LINE(CHR(10));
  44. DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
  45. DBMS_OUTPUT.PUT_LINE('---------------------------');
  46. DBMS_OUTPUT.PUT_LINE(CHR(10));
  47. DBMS_OUTPUT.PUT_LINE('Summary');
  48. DBMS_OUTPUT.PUT_LINE('========');
  49. DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
  50. DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)');
  51. DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_end_allocated_space*100,2)||' %');
  52. DBMS_OUTPUT.PUT_LINE(CHR(10));
  53. DBMS_OUTPUT.PUT_LINE('History');
  54. DBMS_OUTPUT.PUT_LINE('========');
  55. DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_ts_begin_allocated_space||' MB'||' ('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
  56. DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
  57. DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
  58. DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)' );
  59. DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_ts_growth||' MB'||' ('||round(v_ts_growth/1024,2)||' GB)');
  60. IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
  61. DBMS_OUTPUT.PUT_LINE(CHR(10));
  62. DBMS_OUTPUT.PUT_LINE('!!! NO DATA GROWTH WAS FOUND FOR TABLESPCE '||V_TS_NAME||' !!!');
  63. ELSE
  64. DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' MB'||' ('||round((v_ts_growth/v_numdays)/1024,2)||' GB)');
  65. DBMS_OUTPUT.PUT_LINE(CHR(10));
  66. DBMS_OUTPUT.PUT_LINE('Expected Growth');
  67. DBMS_OUTPUT.PUT_LINE('===============');
  68. DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
  69. DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
  70. DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
  71. END IF;
  72. EXCEPTION
  73. WHEN NO_DATA_FOUND THEN
  74. DBMS_OUTPUT.PUT_LINE(CHR(10));
  75. DBMS_OUTPUT.PUT_LINE('!!! TABLESPACE DOES NOT EXIST !!!');
  76. WHEN NOT_IN_AWR THEN
  77. DBMS_OUTPUT.PUT_LINE(CHR(10));
  78. DBMS_OUTPUT.PUT_LINE('!!! TABLESPACE USAGE INFORMATION NOT FOUND IN AWR !!!');
  80. END;
  81. /

实际执行结果如下：





【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/haoxiaoyu/p/4950052.html