tspuse_proc.sql

-- 
-- Title :       tspuse_proc.sql 
-- Description : To create procedure to 
--               display tablespace size and
--               usage of all tablespaces in 
--               the database. 
-- 
-- Usage/Notes : (1) Must run as a SYSDBA user.
--               (2) Pre-requisite to
--                   tspuse.sql script. 
-- 
-- Copyright :   ABCdba.com 2002 
-- 
 
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, '99,990.999' ) || ' .. ' || 
TO_CHAR( 
t_usedbytes, '99,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 
/ 
 
@save_env.sql 
host rm save_env.sql 
SET TERMOUT ON

© copyright 2001-2014 ABCdba.com | all rights reserved