每天定时统计Oracle表空间的使用信息

最新推荐文章于 2021-06-23 11:18:26 发布

RomanticSwordsman

最新推荐文章于 2021-06-23 11:18:26 发布

阅读量775

点赞数

CC 4.0 BY-SA版权

分类专栏：高可用性文章标签： oracle statistics null exception date logging

本文链接：https://blog.youkuaiyun.com/RomanticSwordsman/article/details/7575102

高可用性专栏收录该内容

3 篇文章

订阅专栏

本文介绍如何创建Oracle表空间使用信息统计的存储过程、调度任务及执行流程，实现每日定时更新表空间使用情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

每天定时统计Oracle表空间的使用信息

1、创建表格

CREATE TABLE DBABC.TBS_INFO
(
  TABLESPACE_NAME  VARCHAR2(30 BYTE),
  MEGS_ALLOC       NUMBER,
  MEGS_FREE        NUMBER,
  MEGS_USED        NUMBER,
  PCT_FREE         NUMBER,
  PCT_USED         NUMBER,
  MAX              NUMBER,
  INFO_TIME        DATE                         DEFAULT SYSDATE
);

2、创建存储过程将表空间信息插入到表格中

CREATE OR REPLACE PROCEDURE SYS.SP_TBS_INFO
AS
 /***************************************
 Author: Liups@dbabc.net
 Time:2011-08-03
**************************************/

BEGIN
   INSERT INTO dbabc.tbs_info
      SELECT   a.tablespace_name,
               ROUND (a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
               ROUND (NVL (b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
               ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024,
                      2)
                  megs_used,
               ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100, 2)
                  Pct_Free,
               100
               - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100, 2)
                  Pct_used,
               ROUND (maxbytes / 1048576, 2) MAX,
               SYSDATE
        FROM   (  SELECT   f.tablespace_name,
                           SUM (f.bytes) bytes_alloc,
                           SUM (DECODE (f.autoextensible,
                                        'YES',
                                        f.maxbytes,
                                        'NO',
                                        f.bytes))
                              maxbytes
                    FROM   dba_data_files f
                GROUP BY   tablespace_name) a,
               (  SELECT   f.tablespace_name, SUM (f.bytes) bytes_free
                    FROM   dba_free_space f
                GROUP BY   tablespace_name) b
       WHERE   a.tablespace_name = b.tablespace_name(+)
      UNION ALL
        SELECT   h.tablespace_name,
                 ROUND (SUM (h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
                 ROUND(SUM (
                          (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0)
                       )
                       / 1048576)
                    megs_free,
                 ROUND (SUM (NVL (p.bytes_used, 0)) / 1048576) megs_used,
                 ROUND( (SUM( (h.bytes_free + h.bytes_used)
                             - NVL (p.bytes_used, 0))
                         / SUM (h.bytes_used + h.bytes_free))
                       * 100)
                    Pct_Free,
                 100
                 - ROUND( (SUM( (h.bytes_free + h.bytes_used)
                               - NVL (p.bytes_used, 0))
                           / SUM (h.bytes_used + h.bytes_free))
                         * 100)
                    pct_used,
                 ROUND (SUM (f.maxbytes) / 1048576) MAX,
                 SYSDATE
          FROM   sys.v_$TEMP_SPACE_HEADER h,
                 sys.v_$Temp_extent_pool p,
                 dba_temp_files f
         WHERE       p.file_id(+) = h.file_id
                 AND p.tablespace_name(+) = h.tablespace_name
                 AND f.file_id = h.file_id
                 AND f.tablespace_name = h.tablespace_name
      GROUP BY   h.tablespace_name
      ORDER BY   1;

   COMMIT;
END;
/

3、创建schedule job每天上午9点和下午3点运行job，统计表空间信息

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SYS.JOB_TBS_INFO'
      ,start_date      => TO_TIMESTAMP_TZ('2011/08/03 16:52:04.831002 +08:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=DAILY;  BYHOUR=9,15; BYMINUTE=0;BYSECOND=0'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'STORED_PROCEDURE'
      ,job_action      => 'SYS.SP_TBS_INFO'
      ,comments        => 'Statistics tablespace  information,exc at every day 9 am and 3pm'
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.JOB_TBS_INFO'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.JOB_TBS_INFO'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_RUNS);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYS.JOB_TBS_INFO'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYS.JOB_TBS_INFO'
     ,attribute => 'MAX_RUNS');
  BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( name      => 'SYS.JOB_TBS_INFO'
       ,attribute => 'STOP_ON_WINDOW_CLOSE'
       ,value     => FALSE);
  EXCEPTION
    -- could fail if program is of type EXECUTABLE...
    WHEN OTHERS THEN
      NULL;
  END;
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.JOB_TBS_INFO'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYS.JOB_TBS_INFO'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.JOB_TBS_INFO'
     ,attribute => 'AUTO_DROP'
     ,value     => FALSE);

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'SYS.JOB_TBS_INFO');
END;
/