create table bk_tablespacemon
(
id int primary key,
insert_time date,
TABLESPACE_NAME varchar2(50),
DATAFILE_COUNT float,
SIZE_GB float,
FREE_GB float,
USED_GB float,
MAXFREE float,
PCT_USED float,
PCT_FREE float)

create sequence seq_bk_tablespacemon_id
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 30;

create or replace procedure p_i_tablespacemon
as
begin
insert into bk_tablespacemon
select seq_bk_tablespacemon_id.nextval,t.* from (SELECT sysdate,DF.TABLESPACE_NAME,
       COUNT(*) DATAFILE_COUNT,
       ROUND(SUM(DF.BYTES) / 1048576 / 1024, 2) SIZE_GB,
       ROUND(SUM(FREE.BYTES) / 1048576 / 1024, 2) FREE_GB,
       ROUND(SUM(DF.BYTES) / 1048576 / 1024 -
             SUM(FREE.BYTES) / 1048576 / 1024,
             2) USED_GB,
       ROUND(MAX(FREE.MAXBYTES) / 1048576 / 1024, 2) MAXFREE,
       100 - ROUND(100.0 * SUM(FREE.BYTES) / SUM(DF.BYTES), 2) PCT_USED,
       ROUND(100.0 * SUM(FREE.BYTES) / SUM(DF.BYTES), 2) PCT_FREE
FROM DBA_DATA_FILES DF,
       (SELECT TABLESPACE_NAME,
               FILE_ID,
               SUM(BYTES) BYTES,
               MAX(BYTES) MAXBYTES
          FROM DBA_FREE_SPACE
         WHERE BYTES > 1024 * 1024
         GROUP BY TABLESPACE_NAME, FILE_ID) FREE
WHERE DF.TABLESPACE_NAME = FREE.TABLESPACE_NAME(+)
   AND DF.FILE_ID = FREE.FILE_ID(+)
GROUP BY DF.TABLESPACE_NAME
ORDER BY 8)　t;
commit;
end;

begin
sys.dbms_scheduler.create_job(job_name            => 'sys.JOB_tablespacemon',
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'sys.p_i_tablespacemon',
                                start_date          => to_date('29-04-2015 01:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                                repeat_interval     => 'Freq=Daily;Interval=1',
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => '');
end;
/

ITPub博客

监控表空间

最新文章