job调用

在数据库中创建视图

create or replace view dba_tablespace_free as
select a.tablespace_name,
       a.total_space_mb allocated_space_mb,
       round(b.free_space_mb, 2) free_space_mb,
       (a.max_space - a.total_space_mb) free_allocate_mb,
       round(a.max_space, 2) max_space_mb,
       round((a.total_space_mb - b.free_space_mb) / a.total_space_mb * 100,
             2) pct_usage,
       round(a.total_space_mb / a.max_space * 100, 2) pct_allocated
  from (select tablespace_name,
               sum(bytes) / 1024 / 1024 total_space_Mb,
               decode(sum(maxbytes / 1024 / 1024),
                      0,
                      sum(bytes) / 1024 / 1024,
                      sum(case
                            when AUTOEXTENSIBLE = 'YES' then
                             maxbytes
                            else
                             bytes
                          end) / 1024 / 1024) max_space
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum((bytes) / 1024 / 1024) free_space_Mb
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name

创建要表作为查询表

create table ts_usage as 
 select sysdate dt,c.TABLESPACE_NAME "Tablespace Name",
        c.TOT_GROOTTE_GB "Tablespace Size(G)",
        (c.TOT_GROOTTE_GB - f.total_bytes) "UsedSize(G)",
        round((c.TOT_GROOTTE_GB - f.total_bytes) / c.TOT_GROOTTE_GB * 100,
              2) "Rates(%)",
        f.total_bytes "Not Used(G)",
        (SELECT (free_space_mb + free_allocate_mb) / 1024
           FROM dba_tablespace_free a
          where a.tablespace_name = f.tablespace_name) "ThatYouCanUse(G)",
        F.MAX_BYTES "MaxFreeSpace(M)",
        F.TOTAL_NUM "FreeSpaceCount"
   from (select a.TABLESPACE_NAME,
                sum(BYTES) / 1024 / 1024 / 1024 TOTAL_BYTES,
                max(BYTES) / 1024 / 1024  MAX_BYTES,
                count(*) TOTAL_NUM
           from SYS.DBA_FREE_SPACE a
          group by a.tablespace_name) f,
        (select b.tablespace_name,
                sum(b.bytes) / 1024 / 1024 / 1024 TOT_GROOTTE_GB
           from SYS.DBA_DATA_FILES b
          group by b.tablespace_name) c
  where f.TABLESPACE_NAME = c.TABLESPACE_NAME
  order by 4 desc

定义job

insert into ts_usage
 select sysdate dt,c.TABLESPACE_NAME "Tablespace Name",
        c.TOT_GROOTTE_GB "Tablespace Size(G)",
        (c.TOT_GROOTTE_GB - f.total_bytes) "UsedSize(G)",
        round((c.TOT_GROOTTE_GB - f.total_bytes) / c.TOT_GROOTTE_GB * 100,
              2) "Rates(%)",
        f.total_bytes "Not Used(G)",
        (SELECT (free_space_mb + free_allocate_mb) / 1024
           FROM dba_tablespace_free a
          where a.tablespace_name = f.tablespace_name) "ThatYouCanUse(G)",
        F.MAX_BYTES "MaxFreeSpace(M)",
        F.TOTAL_NUM "FreeSpaceCount"
   from (select a.TABLESPACE_NAME,
                sum(BYTES) / 1024 / 1024 / 1024 TOTAL_BYTES,
                max(BYTES) / 1024 / 1024  MAX_BYTES,
                count(*) TOTAL_NUM
           from SYS.DBA_FREE_SPACE a
          group by a.tablespace_name) f,
        (select b.tablespace_name,
                sum(b.bytes) / 1024 / 1024 / 1024 TOT_GROOTTE_GB
           from SYS.DBA_DATA_FILES b
          group by b.tablespace_name) c
  where f.TABLESPACE_NAME = c.TABLESPACE_NAME
  order by 4 desc;
  
  commit;

通过dbms_jobs 调用


有的时间格式是这样的

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29990276/viewspace-1721965/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29990276/viewspace-1721965/