• <ul id="mayc0"></ul>
    <ul id="mayc0"><center id="mayc0"></center></ul>
    <strike id="mayc0"><input id="mayc0"></input></strike>
    <ul id="mayc0"></ul>
  • 始創于2000年 股票代碼:831685
    咨詢熱線:0371-60135900 注冊有禮 登錄
    • 掛牌上市企業
    • 60秒人工響應
    • 99.99%連通率
    • 7*24h人工
    • 故障100倍補償
    您的位置: 網站首頁 > 幫助中心>文章內容

    Oracle tablespace 監控項目版

    發布時間:  2012/8/29 17:49:29

    主要實現功能:

    1、監控表空間,通用版

    2、采用PHP進行格式化輸入輸出,并下發監控郵件報表

    3、自動維護、優化涉及到的文件及表

    4、對特殊格式輸入進行demo說明

    目前該版本已有升級加強版,對于目前提供的腳本足夠大家使用,謝謝指正!

    #--SETUP.sql
    [Oracle@clement datafile_monitor]$ more SETUP.sql
    -
     

    /*DESCRIBE
      VI SETUP.sql
      REM INITIALIZATION FILE TO INSTALL THE MONITORING
      AUTHOR : Clement Ge
      MAIL: Clement.gejun@gmail.com
     */


    SET TIMING ON;
    SET SERVEROUTPUT ON; 
    TRUNCATE TABLE DATAFILES_GE_MONITORING;
    DROP TABLE DATAFILES_GE_MONITORING;
    /* Create table*/
    create table DATAFILES_GE_MONITORING
    (
      TABLESPACE_NAME    VARCHAR2(50) not null,
      FILE_NAME          VARCHAR2(200) not null,
      IGBYTES            NUMBER(16,6) not null,
      CURRENTDAYGBYTES   NUMBER(16,6) default 0 not null,
      LASTDAYSGBYTES     NUMBER(16,6) default 0 not null,
      CURRENTWEEKGBYTES  NUMBER(16,6) default 0 not null,
      LASTWEEKSGBYTES    NUMBER(16,6) default 0 not null,
      CURRENTMONTHGBYTES NUMBER(16,6) default 0 not null,
      LASTMONTHSGBYTES   NUMBER(16,6) default 0 not null,
      ALIVEVALUE         VARCHAR2(40) default (to_char(SYSDATE,'YYYY-MM-DD') || ',,5') not null,
      STATUS             CHAR(1) default 0 not null
    )
    /* The current default specified tablespace or whatever*/
    --tablespace USERS
      pctfree 40
      initrans 1
      maxtrans 255
      storage
      (
        initial 1M
        next 1M
        minextents 1
        maxextents unlimited
      );


    /* Create/Recreate primary, unique and foreign key constraints */
    ALTER TABLE DATAFILES_GE_MONITORING
      ADD CONSTRAINT UNK_DATAFILES_GE_MONITORING PRIMARY KEY (TABLESPACE_NAME, FILE_NAME)
      USING INDEX;
    /* KEEP TABLE*/
    ALTER TABLE DATAFILES_GE_MONITORING STORAGE (BUFFER_POOL KEEP);
    /* Initialization value is not less than 1G, and to be calculated in units of G*/
    INSERT INTO DATAFILES_GE_MONITORING
          SELECT T.TABLESPACE_NAME TABLESPACE_NAME,
                 T.FILE_NAME FILE_NAME,
                 T.BYTES / 1024 / 1024 / 1024 IGBYTES,
                 0 CURRENTDAYGBYTES,
                 0 LASTDAYSGBYTES,
                 0 CURRENTWEEKGBYTES,
                 0 LASTWEEKSGBYTES,
                 0 CURRENTMONTHGBYTES,
                 0 LASTMONTHSGBYTES,
                 (TO_CHAR(SYSDATE,'YYYY-MM-DD') || ',,5') ALIVEVALUE,
                 (CASE WHEN T.BYTES / 1024 / 1024 / 1024 > 31 THEN 6 ELSE 0 END) STATUS
            FROM DBA_DATA_FILES T
           WHERE EXISTS (SELECT 1
                    FROM DBA_DATA_FILES X
                   WHERE T.TABLESPACE_NAME = X.TABLESPACE_NAME
                     AND X.BYTES / 1024 / 1024 / 1024 >= 0)
           ORDER BY T.TABLESPACE_NAME ASC,T.BYTES DESC;
    SHOW ERRORS;      
    COMMIT;


    #--DATAFILES_GE_MONITORING.sh
    [Oracle@clement datafile_monitor]$ more DATAFILES_GE_MONITORING.sh
    #!/bin/sh
    source /home/Oracle/.bash_profile
    #select userenv('language') from dual
    #export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
    today=`date +%Y_%m_%d -d "$(echo -1) day"`
    dirs=`pwd`
    cd $dirs
    find $dirs -name '*_*_MonitoringDatafileSpace.html' -type f -mtime +7 |xargs rm -rf
    sed -e "/html/c\\spool "$dirs/$today"_MonitoringDatafileSpace.html" DATAFILES_GE_MONITORING.sql > TEMP_DATAFILES_GE_MONITORING.sql
    mv TEMP_DATAFILES_GE_MONITORING.sql DATAFILES_GE_MONITORING.sql
    #sqlplus scott\/\"scott\@\#\$331804\"\@ucc<<!
    sqlplus 'scott/tiger'<<!
    @DATAFILES_GE_MONITORING.sql
    exit
    !


    #-- sendDataFileSpaceMonitoring.php
    [Oracle@clement datafile_monitor]$ more sendDataFileSpaceMonitoring.php
    #!/usr/bin/php   -q
    <?
    $email="clement.ge@finalist.hk";
    $currenthour=date("H");
    $pathnames=dirname(__FILE__)."/".date('Y_m_d',mktime($currenthour-24))."_MonitoringDatafileSpace.html";
    $file=fopen("$pathnames","r");
    echo $file;
    send_email_tj($email,$file);
    function send_email_tj($email,$file){
    $header= "Content-type:text/html;charset=gb2312\r\n ";
    $header .='From:<clement.ge@finalist.hk>' ."\r\n ";
    $from= "clement.ge@finalist.hk";
    $subject= "日常數據庫數據文件監控列表(統計監控截至時間是當前執行時間)";
    $message=" <html><body> ";
    $message .= " <br> <br><h1><center>日常數據庫監控維護列表<center></h1> <br> <br> ";
    $message .=$file;
    while (!feof($file))
      {
      $message .=fgetc($file);
      }
    fclose($file);
    $message .= "<br></body></html> ";
    mail($email,$subject,$message,$header) or die( "Sorry,Failure ");
    }
    ?>


    #--DATAFILES_GE_MONITORING.sql
    [Oracle@clement datafile_monitor]$ more DATAFILES_GE_MONITORING.sh
    #!/bin/sh
    source /home/Oracle/.bash_profile
    #select userenv('language') from dual
    #export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
    today=`date +%Y_%m_%d -d "$(echo -1) day"`
    dirs=`pwd`
    cd $dirs
    find $dirs -name '(*_*_)每日數據維護工作一.html' -type f -mtime +7 |xargs rm -rf
    sed -e "/txt/c\\spool "$dirs/$today"_每日數據維護工作一.txt" DATAFILES_GE_MONITORING.sql > TEMP_DATAFILES_GE_MONITORING.sql
    mv TEMP_DATAFILES_GE_MONITORING.sql DATAFILES_GE_MONITORING.sql
    #sqlplus scott\/\"scott\@\#\$331804\"\@ucc<<!
    sqlplus 'scott/tiger'<<!
    --SET LINESIZE 1000
    --SET TERM OFF VERIFY OFF FEEDBACK OFF PAGESIZE 999
    --SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF
    @DATAFILES_GE_MONITORING.sql
    exit
    !


    --DATAFILES_GE_MONITORING.sql
    [Oracle@clement datafile_monitor]$ more DATAFILES_GE_MONITORING.sql
    /*  DESCRIBE
      vi DATAFILES_GE_MONITORING.sql
      REM Monitoring analyze
      REM Deployment CRON use Oracle user
      AUTHOR : Clement Ge*/
     /* Insert into datas */
    MERGE INTO DATAFILES_GE_MONITORING S
    USING (SELECT /*+ NO_CPU_COSTING */ T.TABLESPACE_NAME TABLESPACE_NAME,
                 T.FILE_NAME FILE_NAME,
                 T.BYTES / 1024 / 1024 / 1024 IGBYTES
            FROM DBA_DATA_FILES T
           WHERE EXISTS (SELECT 1
                    FROM DBA_DATA_FILES W
                   WHERE T.TABLESPACE_NAME = W.TABLESPACE_NAME
                     AND W.BYTES / 1024 / 1024 / 1024 BETWEEN 1 AND 32)
           ORDER BY T.TABLESPACE_NAME ASC,T.BYTES DESC) X
    ON (S.TABLESPACE_NAME = X.TABLESPACE_NAME
        AND S.FILE_NAME = X.FILE_NAME)
    WHEN MATCHED THEN
      UPDATE SET S.IGBYTES = X.IGBYTES,
                 S.CURRENTDAYGBYTES = X.IGBYTES - S.IGBYTES,
                 S.LASTDAYSGBYTES = S.CURRENTDAYGBYTES,
                 S.CURRENTWEEKGBYTES = DECODE(TO_CHAR(SYSDATE-1,'DD'),'00',S.LASTDAYSGBYTES,S.CURRENTWEEKGBYTES + S.LASTDAYSGBYTES),
                 S.LASTWEEKSGBYTES = DECODE(TO_CHAR(SYSDATE-1,'DD'),'00',S.CURRENTWEEKGBYTES,S.LASTWEEKSGBYTES),
                 S.CURRENTMONTHGBYTES = DECODE(TO_CHAR(SYSDATE-1,'DD'),'01',
                   S.LASTDAYSGBYTES,S.CURRENTMONTHGBYTES + S.CURRENTWEEKGBYTES),
                 S.LASTMONTHSGBYTES = DECODE(TO_CHAR(SYSDATE-1,'DD'),'01',S.CURRENTMONTHGBYTES,S.LASTMONTHSGBYTES),
                 S.ALIVEVALUE = (CASE WHEN REPLACE(SUBSTR(S.CURRENTDAYGBYTES,1,7),'-','') > 0 THEN
                                           TO_CHAR(SYSDATE,'YYYY-MM-DD') || ',,' || SUBSTR(S.ALIVEVALUE,INSTR(S.ALIVEVALUE,',',1,2)+1)
                                      ELSE
                                        (CASE WHEN NVL(SUBSTR(S.ALIVEVALUE,INSTR(S.ALIVEVALUE,',',1)+1,
                                           INSTR(S.ALIVEVALUE,',',1,2)-INSTR(S.ALIVEVALUE,',',1)-1),0)
                                                   BETWEEN 0 AND SUBSTR(S.ALIVEVALUE,INSTR(S.ALIVEVALUE,',',1,2)+1) - 1 THEN
                                                     SUBSTR(S.ALIVEVALUE,1,INSTR(S.ALIVEVALUE,',',1)) ||
                                                     (NVL(SUBSTR(S.ALIVEVALUE,INSTR(S.ALIVEVALUE,',',1)+1,
                                                      INSTR(S.ALIVEVALUE,',',1,2)-INSTR(S.ALIVEVALUE,',',1)-1),0) + 1)||
                                                     SUBSTR(S.ALIVEVALUE,INSTR(S.ALIVEVALUE,',',1,2))
                                               ELSE
                                                   S.ALIVEVALUE
                                           END)
                                      END)          
       WHERE S.STATUS < '9'
    WHEN NOT MATCHED THEN
      INSERT (S.TABLESPACE_NAME,S.FILE_NAME,S.IGBYTES,S.CURRENTDAYGBYTES,S.LASTDAYSGBYTES,S.CURRENTWEEKGBYTES,S.LASTWEEKSGBYTES,
              S.CURRENTMONTHGBYTES,S.LASTMONTHSGBYTES,S.ALIVEVALUE,S.STATUS)
      VALUES (X.TABLESPACE_NAME,X.FILE_NAME,X.IGBYTES,0,0,0,0,0,0,(SYSDATE || ',,5'),
              (CASE WHEN X.IGBYTES / 1024 / 1024 / 1024 > 31 THEN 2 ELSE 0 END));
    /* Update Status*/
    UPDATE DATAFILES_GE_MONITORING UNM
       SET UNM.STATUS = (CASE
                             WHEN UNM.IGBYTES < 26 AND NOT EXISTS (SELECT '1'
                                       FROM DATAFILES_GE_MONITORING UNM1
                                       WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME having count(*) > 1 ) THEN
                                  (CASE WHEN (NVL(SUBSTR(UNM.ALIVEVALUE,INSTR(UNM.ALIVEVALUE,',',1)+1,
                                                         INSTR(UNM.ALIVEVALUE,',',1,2)-INSTR(UNM.ALIVEVALUE,',',1)-1),0) >=
                                              SUBSTR(UNM.ALIVEVALUE,INSTR(UNM.ALIVEVALUE,',',1,2)+1) - 1)
                                                 AND (UNM.STATUS = '0' OR UNM.STATUS = '1') THEN
                                             '1' 
                                        ELSE
                                            '0'
                                   END)
                             WHEN (SELECT COUNT(UNM1.FILE_NAME) * 31.998 - SUM(UNM1.IGBYTES)
                                   FROM DATAFILES_GE_MONITORING UNM1 WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME) > 6 AND
                                   EXISTS (SELECT '1' FROM DATAFILES_GE_MONITORING UNM1
                                   WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME having count(*) > 1 ) THEN
                                  (CASE WHEN (SELECT NVL(MIN(NVL(SUBSTR(UNM1.ALIVEVALUE,INSTR(UNM1.ALIVEVALUE,',',1)+1,
                                                         INSTR(UNM1.ALIVEVALUE,',',1,2)-INSTR(UNM1.ALIVEVALUE,',',1)-1),0)),0)
                                              FROM DATAFILES_GE_MONITORING UNM1
                                              WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME
                                                AND UNM1.STATUS NOT IN ('2','4')) >=
                                              SUBSTR(UNM.ALIVEVALUE,INSTR(UNM.ALIVEVALUE,',',1,2)+1) - 1   THEN
                                              '1'
                                        WHEN NVL(SUBSTR(UNM.ALIVEVALUE,INSTR(UNM.ALIVEVALUE,',',1)+1,
                                                         INSTR(UNM.ALIVEVALUE,',',1,2)-INSTR(UNM.ALIVEVALUE,',',1)-1),0) >=
                                             SUBSTR(UNM.ALIVEVALUE,INSTR(UNM.ALIVEVALUE,',',1,2)+1) - 1 THEN
                                             '3'
                                        ELSE
                                             '0'
                                   END)
                             WHEN (SELECT COUNT(UNM1.FILE_NAME) * 31.998 - SUM(UNM1.IGBYTES) FROM
                                   DATAFILES_GE_MONITORING UNM1
                                   WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME) BETWEEN 3 AND 6 THEN '5'
                             WHEN (SELECT COUNT(UNM1.FILE_NAME) * 31.998 - SUM(UNM1.IGBYTES)
                                   FROM DATAFILES_GE_MONITORING UNM1
                                   WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME) BETWEEN 0 AND 2 THEN '6'
                           ELSE UNM.STATUS
                          END)
    WHERE UNM.STATUS < '9';
    COMMIT;
    SET LINESIZE 2000;
    SET TERM OFF VERIFY OFF FEEDBACK OFF PAGESIZE 2000;
    SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF;
    spool /wins/test/datafile_monitor/2011_05_13_每日數據維護工作一.html;

    SELECT ALARM.TABLESPACE_NAME "需處理的表空間名稱",
           DENSE_RANK() OVER(PARTITION BY ALARM.STATUS,ALARM.TABLESPACE_NAME ORDER BY ALARM.STATUS DESC,
           NVL(SUBSTR(ALARM.ALIVEVALUE,INSTR(ALARM.ALIVEVALUE,',',1)+1,
                      INSTR(ALARM.ALIVEVALUE,',',1,2)-INSTR(ALARM.ALIVEVALUE,',',1)-1),0) ASC,
                      ALARM.IGBYTES DESC) "處理優先級",
           (CASE WHEN ALARM.STATUS = '6' THEN
                      (CASE WHEN (SELECT COUNT(UNM1.FILE_NAME) * 31.998 - SUM(UNM1.IGBYTES)
                                  FROM DATAFILES_GE_MONITORING UNM1
                                  WHERE UNM1.TABLESPACE_NAME = ALARM.TABLESPACE_NAME) > 0 THEN                  
                               (CASE WHEN NVL(SUBSTR(ALARM.ALIVEVALUE,INSTR(ALARM.ALIVEVALUE,',',1)+1,
                                              INSTR(ALARM.ALIVEVALUE,',',1,2)-INSTR(ALARM.ALIVEVALUE,',',1)-1),0) > 0 THEN
                                          '報警!需立即處理,否則影響數據正常入庫,目前已'|| SUBSTR(ALARM.ALIVEVALUE,
                                             INSTR(ALARM.ALIVEVALUE,',',1)+1,
                                                  INSTR(ALARM.ALIVEVALUE,',',1,2)-INSTR(ALARM.ALIVEVALUE,',',1)-1) ||'天未增長!'
                                    ELSE
                                          '報警!!請立即處理!!!'
                               END)
                           ELSE '報警!!!請立即處理,該空間達到或超過最大文件自動擴展空間界限,將要影響該空間所有數據正常運行!'
                        END)
           --警告提示級別統計
                 ELSE
                      (CASE WHEN (SELECT COUNT(UNM1.FILE_NAME) * 31.998 - SUM(UNM1.IGBYTES)
                                  FROM DATAFILES_GE_MONITORING UNM1
                                  WHERE UNM1.TABLESPACE_NAME = ALARM.TABLESPACE_NAME) > 3 THEN
                                (CASE WHEN NVL(SUBSTR(ALARM.ALIVEVALUE,INSTR(ALARM.ALIVEVALUE,',',1)+1,
                                           INSTR(ALARM.ALIVEVALUE,',',1,2)-INSTR(ALARM.ALIVEVALUE,',',1)-1),0) > 0 THEN
                                           '警告 !需按照業務增長量確定是否立刻調整,目前已'|| SUBSTR(ALARM.ALIVEVALUE,
                                            INSTR(ALARM.ALIVEVALUE,',',1)+1,
                                           INSTR(ALARM.ALIVEVALUE,',',1,2)-INSTR(ALARM.ALIVEVALUE,',',1)-1) ||'天未增長!'
                                      ELSE
                                          '警告 !!可能需要立即調整!'
                                  END)
                            ELSE '警告!!!該空間達到或超過警告最大文件空間界限,為了不影響數據的正常運行,請立即處理!'
                        END)
             END) "監控描述信息",
           ALARM.IGBYTES "數據文件大小(G)",
           ALARM.FILE_NAME "數據文件路徑"  
    FROM DATAFILES_GE_MONITORING ALARM
    WHERE ALARM.STATUS IN ('5','6');

    UPDATE DATAFILES_GE_MONITORING UNM
       SET UNM.STATUS = (CASE WHEN (SELECT COUNT(UNM1.FILE_NAME) * 31.998 - SUM(UNM1.IGBYTES)
                                    FROM DATAFILES_GE_MONITORING UNM1
                                    WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME) < 0 THEN
                                    '4'
                              ELSE
                                   UNM.STATUS
                         END)
    WHERE UNM.STATUS IN ('5','6');
    COMMIT;

    WITH ESTIMATE_01 AS (SELECT DECODE(TX.STATUS,'0','監控','1','數據未增長','3','暫時空閑','4','暫停監控','5','警告','6','報警') DS,
                               TX.TABLESPACE_NAME TB,
                               TX.IGBYTES IG,
                               --SUM(TX.IGBYTES) OVER (ORDER BY TX.FILE_NAME) "表空間累計監控值(G)",
                               TX.CURRENTDAYGBYTES CD,
                               TX.LASTDAYSGBYTES LD,
                               (CASE WHEN TX.LASTDAYSGBYTES = 0 THEN
                                          (CASE WHEN TX.CURRENTDAYGBYTES >= 0 THEN
                                                     'Up Rate : ' || ROUND(TX.CURRENTDAYGBYTES*100,4) || '%'
                                                ELSE
                                                     'Down Rate : ' || ROUND(TX.CURRENTDAYGBYTES*100,4) || '%'
                                           END)
                                      WHEN TX.CURRENTDAYGBYTES = 0 THEN
                                           (CASE WHEN TX.LASTDAYSGBYTES > 0 THEN
                                                     'Down Rate : ' || ROUND(TX.LASTDAYSGBYTES*100,4) || '%'
                                                ELSE
                                                     'Up Rate : ' || ROUND(TX.LASTDAYSGBYTES*100,4) || '%'
                                           END)
                                      ELSE
                                          (CASE WHEN TX.CURRENTDAYGBYTES > 0 THEN
                                                     'Up Rate : ' || ROUND(TX.CURRENTDAYGBYTES/TX.LASTDAYSGBYTES*100,4) || '%'
                                                ELSE
                                                     'Down Rate : ' || ROUND(TX.CURRENTDAYGBYTES/TX.LASTDAYSGBYTES*100,4) || '%'
                                            END)
                                 END) CLD,
                               TX.CURRENTWEEKGBYTES CW,
                               TX.LASTWEEKSGBYTES LW,
                               (CASE WHEN TX.LASTWEEKSGBYTES = 0 THEN
                                          (CASE WHEN TX.CURRENTWEEKGBYTES >= 0 THEN
                                                     'Up Rate : ' || ROUND(TX.CURRENTWEEKGBYTES*100,4) || '%'
                                                ELSE
                                                     'Down Rate : ' || ROUND(TX.CURRENTWEEKGBYTES*100,4) || '%'
                                           END)
                                      WHEN TX.CURRENTWEEKGBYTES = 0 THEN
                                           (CASE WHEN TX.LASTWEEKSGBYTES > 0 THEN
                                                     'Down Rate : ' || ROUND(TX.LASTWEEKSGBYTES*100,4) || '%'
                                                ELSE
                                                     'Up Rate : ' || ROUND(TX.LASTWEEKSGBYTES*100,4) || '%'
                                           END)
                                      ELSE
                                          (CASE WHEN TX.CURRENTWEEKGBYTES > 0 THEN
                                                     'Up Rate : ' || ROUND(TX.CURRENTWEEKGBYTES/TX.LASTWEEKSGBYTES*100,4) || '%'
                                                ELSE
                                                     'Down Rate : ' || ROUND(TX.CURRENTWEEKGBYTES/TX.LASTWEEKSGBYTES*100,4) || '%'
                                            END)
                                 END) CLW,
                               TX.CURRENTMONTHGBYTES  CM,
                               TX.LASTMONTHSGBYTES LM,
                               (CASE WHEN TX.LASTMONTHSGBYTES = 0 THEN
                                          (CASE WHEN TX.CURRENTMONTHGBYTES >= 0 THEN
                                                     'Up Rate : ' || ROUND(TX.CURRENTMONTHGBYTES*100,4) || '%'
                                                ELSE
                                                     'Down Rate : ' || ROUND(TX.CURRENTMONTHGBYTES*100,4) || '%'
                                           END)
                                      WHEN TX.CURRENTMONTHGBYTES = 0 THEN
                                           (CASE WHEN TX.LASTMONTHSGBYTES > 0 THEN
                                                     'Down Rate : ' || ROUND(TX.LASTMONTHSGBYTES*100,4) || '%'
                                                ELSE
                                                     'Up Rate : ' || ROUND(TX.LASTMONTHSGBYTES*100,4) || '%'
                                           END)
                                      ELSE
                                          (CASE WHEN TX.CURRENTMONTHGBYTES > 0 THEN
                                                     'Up Rate : ' || ROUND(TX.CURRENTMONTHGBYTES/TX.LASTMONTHSGBYTES*100,4) || '%'
                                                ELSE
                                                     'Down Rate : ' || ROUND(TX.CURRENTMONTHGBYTES/TX.LASTMONTHSGBYTES*100,4) || '%'
                                            END)
                                 END) CLM
                          FROM DATAFILES_GE_MONITORING TX
                          WHERE TX.STATUS < '9'
                          ORDER BY TX.STATUS DESC,TX.IGBYTES DESC)
    select DS "文件狀態",TB "表空間名稱",IG || '' "當前數據文件大小(G)",
           CD || '' "當天數據量(G)",LD || '' "昨天數據量(G)",
           CLD "前/后兩天比率",
           CW || '' "本周累計總值(G)",LW || '' "上周總值(G)",
           CLW "前/后兩周比率",
           CM || '' "當月累計總值(G)",LM || '' "上月總值(G)",
           CLM "前/后兩月比率"
    from ESTIMATE_01
    UNION ALL
    SELECT '統計匯總 ','監控總計:' || ESTIMATE_02.SUMS,'當前監控總計:' || SUM(ESTIMATE_01.IG),
           '當天增值總計:' || SUM(ESTIMATE_01.CD),'昨天增值總計:' || SUM(ESTIMATE_01.LD),
           '停止監控總計:' || ESTIMATE_02.UNM,
           '本周累計總計:' || SUM(ESTIMATE_01.CW),'上周增值總計:' || SUM(ESTIMATE_01.LW),
           '空間總計:' || ESTIMATE_03.UNCM,
           '本月累計總計:' || SUM(ESTIMATE_01.CM),'上月增值總計:' || SUM(ESTIMATE_01.LM),
           '未加入監控總計:' || TRUNC((ESTIMATE_03.UNCM - ESTIMATE_02.SUMS),6)
    FROM ESTIMATE_01,
         (SELECT SUM(TT.IGBYTES)|| '' SUMS,TRUNC(SUM(DECODE(TT.STATUS,'9',TT.IGBYTES,0)),6)|| '' UNM
          FROM DATAFILES_GE_MONITORING TT) ESTIMATE_02,
         (SELECT TRUNC(SUM(BYTES)/ 1024 / 1024 / 1024,6) || '' UNCM FROM DBA_DATA_FILES) ESTIMATE_03
    GROUP BY ESTIMATE_02.SUMS,ESTIMATE_02.UNM,ESTIMATE_03.UNCM;
    SPOOL OFF;
    ANALYZE TABLE DATAFILES_GE_MONITORING COMPUTE STATISTICS;


    本文出自:億恩科技【www.vbseamall.com】

    服務器租用/服務器托管中國五強!虛擬主機域名注冊頂級提供商!15年品質保障!--億恩科技[ENKJ.COM]

  • 您可能在找
  • 億恩北京公司:
  • 經營性ICP/ISP證:京B2-20150015
  • 億恩鄭州公司:
  • 經營性ICP/ISP/IDC證:豫B1.B2-20060070
  • 億恩南昌公司:
  • 經營性ICP/ISP證:贛B2-20080012
  • 服務器/云主機 24小時售后服務電話:0371-60135900
  • 虛擬主機/智能建站 24小時售后服務電話:0371-60135900
  • 專注服務器托管17年
    掃掃關注-微信公眾號
    0371-60135900
    Copyright© 1999-2019 ENKJ All Rights Reserved 億恩科技 版權所有  地址:鄭州市高新區翠竹街1號總部企業基地億恩大廈  法律顧問:河南亞太人律師事務所郝建鋒、杜慧月律師   京公網安備41019702002023號
      0
     
     
     
     

    0371-60135900
    7*24小時客服服務熱線