tablespace kullanım durumu

CREATE OR REPLACE PROCEDURE ORHAN.PROC_TABLESPACE_SIZE
IS
— tablespace alanlarının kullanım durumları,dolu ve boş alanlar,yüzdeleri
— Orhan Eripek 16.01.2009

v_dyntask VARCHAR2 (2000);
BEGIN
EXECUTE IMMEDIATE ‘TRUNCATE TABLE ORHAN.TABLESPACE_SIZE_DAILY’;

COMMIT;
v_dyntask :=
‘INSERT /*+ APPEND NOLOGGING */ALL
INTO ORHAN.TABLESPACE_SIZE_DAILY
(log_as_date, tablespace_name, BYTES_SIZE, BYTES_FREE, BYTES_USED,PERCENT_USED)
INTO ORHAN.TABLESPACE_SIZE_HIST
(log_as_date, tablespace_name,BYTES_SIZE, BYTES_FREE, BYTES_USED,PERCENT_USED)
SELECT log_as_date, tablespace_name, BYTES_SIZE, BYTES_FREE, BYTES_USED,PERCENT_USED
FROM (SELECT systarih AS log_as_date, tablespace_name, BYTES_SIZE, BYTES_FREE, BYTES_USED,PERCENT_USED
FROM (SELECT SYSDATE AS systarih, a.tablespace_name,
a.BYTES AS BYTES_SIZE, b.BYTES AS BYTES_FREE,(a.BYTES-b.BYTES) AS BYTES_USED,
ROUND (((a.BYTES – b.BYTES) / a.BYTES) * 100, 2 ) as PERCENT_USED
FROM (SELECT tablespace_name, SUM (BYTES) BYTES
FROM SYS.dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (BYTES) BYTES,
MAX (BYTES) max_free_area
FROM SYS.dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY ((a.BYTES – b.BYTES) / a.BYTES) DESC))’;
DBMS_OUTPUT.put_line (v_dyntask);