Tablespace Usage Report Procedure

Procedure Code

--
-- Tablespace Usage Procedure
--

CREATE OR REPLACE PROCEDURE tspuse
   ( t_threshold IN NUMBER := 80 ) AS
   c_newline       CHAR(1)      := CHR( 10 );
   c_horzline      CHAR(80)     := '+'||LPAD( '-', 78, '-' )||'+';
   c_vertline      CHAR(80)     := '|'|| LPAD( '|', 79 );
   t_date          DATE;
   t_dbname        VARCHAR2(10);
   t_tablespace    VARCHAR2(30);
   t_percentage    NUMBER       := 0;
   t_usedbytes     NUMBER(10,3) := 0;
   t_availbytes    NUMBER(10,3) := 0;
   t_availtotal    NUMBER(10,3) := 0;
   t_usedtotal     NUMBER(10,3) := 0;
   CURSOR t_usage_cur IS
      SELECT ddf.tablespace_name,
             SUM( distinct ddf.ddfbytes ) / 1048576 ambytecount,
             SUM( NVL( ds.dsbytes , 0 ) / 1048576 ) umbytecount
      FROM
         ( SELECT tablespace_name, SUM( bytes ) ddfbytes
           FROM sys.dba_data_files
           GROUP BY tablespace_name
           UNION
           SELECT tablespace_name, SUM( bytes )
           FROM sys.dba_temp_files
           GROUP BY tablespace_name ) ddf,
         ( SELECT tablespace_name, SUM( bytes ) dsbytes
           FROM sys.dba_segments
           GROUP BY tablespace_name
           UNION
           SELECT tablespace_name, SUM( bytes_used )
           FROM v$temp_space_header
           GROUP BY tablespace_name ) ds
      WHERE ddf.tablespace_name = ds.tablespace_name (+)
      GROUP BY ddf.tablespace_name;
BEGIN
   SELECT name
      INTO t_dbname
      FROM v$database;
   t_date := sysdate;
   DBMS_OUTPUT.put_line ( c_newline );
   DBMS_OUTPUT.put_line ( c_horzline );
   DBMS_OUTPUT.put_line ( c_vertline );
   DBMS_OUTPUT.put ( '|   Date : ' || INITCAP(SUBSTR(TO_CHAR( t_date, 'DAY' ), 1, 3 ) ) || ' ' || INITCAP(TO_CHAR( t_date, 'DD MON YYYY' ) ) || LPAD( ' ', 35 ) || 'Time : ' || TO_CHAR( t_date, 'HH24:MI:SS' ) || '   |' );
   DBMS_OUTPUT.new_line;
   DBMS_OUTPUT.put_line ( c_vertline );
   DBMS_OUTPUT.put_line ( c_vertline );
   DBMS_OUTPUT.put ( '|              TABLESPACE USAGE REPORT FOR DATABASE :  ' || LPAD( t_dbname, 10 ) || '              |' );
   DBMS_OUTPUT.new_line;
   DBMS_OUTPUT.put ( '|             ' ||  LPAD( '=', 52, '=' ) || '|' );
   DBMS_OUTPUT.new_line;
   DBMS_OUTPUT.put_line ( c_vertline );
   DBMS_OUTPUT.put_line ( c_horzline );
   DBMS_OUTPUT.put_line ( c_vertline );
   DBMS_OUTPUT.put ( '|         TABLESPACE NAME             AVAILABLE        USED       %age   >' || SUBSTR( TO_CHAR(t_threshold, '90' ), -2 ) || '%  |' );
   DBMS_OUTPUT.new_line;
   DBMS_OUTPUT.put ( '|                                     Megabytes      Megabytes    FULL   FULL  |' );
   DBMS_OUTPUT.new_line;
   DBMS_OUTPUT.put_line ( c_vertline );
   DBMS_OUTPUT.put_line ( c_horzline );
   DBMS_OUTPUT.put_line ( c_vertline );
   FOR usage_rec IN t_usage_cur LOOP
      t_tablespace := usage_rec.tablespace_name;
      t_availbytes := ROUND( usage_rec.ambytecount, 3 );
      t_usedbytes  := ROUND( usage_rec.umbytecount, 3 );
      t_availtotal := t_availtotal + t_availbytes;
      t_usedtotal  := t_usedtotal + t_usedbytes;
      t_percentage := ROUND ( ( t_usedbytes / t_availbytes ) * 100, 3 );
      DBMS_OUTPUT.put ( '| ' || RPAD( t_tablespace, 30, '.' ) || '.  ' || TO_CHAR( t_availbytes, '999,990.999' ) || ' ..' || TO_CHAR(t_usedbytes, '999,990.999' ) ||  ' ..' || TO_CHAR( t_percentage, '990' ) || '%  ' );
      IF ( t_usedbytes / t_availbytes ) * 100 >= t_threshold THEN
         DBMS_OUTPUT.put ( '*CHECK*|' );
      ELSE
         DBMS_OUTPUT.put ( '       |' );
      END IF;
      DBMS_OUTPUT.new_line;
   END LOOP;
   DBMS_OUTPUT.put_line ( c_vertline );
   DBMS_OUTPUT.put_line ( c_horzline );
   DBMS_OUTPUT.put_line ( c_vertline );
   DBMS_OUTPUT.put ( '|            TOTALS                ' || TO_CHAR( t_availtotal, '999,990.999' ) || '   ' || TO_CHAR(t_usedtotal, '999,990.999' ) || '            |' );
   DBMS_OUTPUT.new_line;
   DBMS_OUTPUT.put_line ( c_vertline );
   DBMS_OUTPUT.put_line ( c_horzline );
   DBMS_OUTPUT.put_line ( c_newline );
END;
/

GRANT execute ON tspuse TO PUBLIC
/

GRANT SELECT ON sys.dba_data_files to PUBLIC
/

GRANT SELECT ON sys.dba_segments to PUBLIC
/

GRANT SELECT ON sys.v_$database to PUBLIC
/

GRANT SELECT ON sys.dba_temp_files to PUBLIC
/

GRANT SELECT ON sys.v_$temp_space_header to PUBLIC
/

DROP PUBLIC SYNONYM tspuse
/

CREATE PUBLIC SYNONYM tspuse FOR tspuse
/

Procedure Call Code

--
-- Tablespace Usage Procedure Call
--

SET FEEDBACK OFF;

SET SERVEROUTPUT ON SIZE 20000;

EXEC TSPUSE (80);

SET FEEDBACK ON;