久久精品国产亚洲怮怮_奇米网777色在线精品_亚洲色中文字幕制服丝袜_久久精品人人做人人看

始創(chuàng)于2000年 股票代碼：831685
咨詢熱線：0371-60135900 注冊(cè)有禮 登錄
  • 掛牌上市企業(yè)
  • 60秒人工響應(yīng)
  • 99.99%連通率
  • 7*24h人工
  • 故障100倍補(bǔ)償
全部產(chǎn)品
您的位置： 網(wǎng)站首頁 > 幫助中心>文章內(nèi)容

Oracle tablespace 監(jiān)控項(xiàng)目版
發(fā)布時(shí)間:  2012/8/29 17:49:29

主要實(shí)現(xiàn)功能：

1、監(jiān)控表空間，通用版

2、采用PHP進(jìn)行格式化輸入輸出，并下發(fā)監(jiān)控郵件報(bào)表

3、自動(dòng)維護(hù)、優(yōu)化涉及到的文件及表

4、對(duì)特殊格式輸入進(jìn)行demo說明

目前該版本已有升級(jí)加強(qiáng)版，對(duì)于目前提供的腳本足夠大家使用，謝謝指正！

#--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= "日常數(shù)據(jù)庫(kù)數(shù)據(jù)文件監(jiān)控列表(統(tǒng)計(jì)監(jiān)控截至?xí)r間是當(dāng)前執(zhí)行時(shí)間)";
$message=" <html><body> ";
$message .= " <br> <br><h1><center>日常數(shù)據(jù)庫(kù)監(jiān)控維護(hù)列表<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 '(*_*_)每日數(shù)據(jù)維護(hù)工作一.html' -type f -mtime +7 |xargs rm -rf
sed -e "/txt/c\\spool "$dirs/$today"_每日數(shù)據(jù)維護(hù)工作一.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_每日數(shù)據(jù)維護(hù)工作一.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) "處理優(yōu)先級(jí)",
       (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
                                      '報(bào)警！需立即處理,否則影響數(shù)據(jù)正常入庫(kù),目前已'|| SUBSTR(ALARM.ALIVEVALUE,
                                         INSTR(ALARM.ALIVEVALUE,',',1)+1,
                                              INSTR(ALARM.ALIVEVALUE,',',1,2)-INSTR(ALARM.ALIVEVALUE,',',1)-1) ||'天未增長(zhǎng)！'
                                ELSE
                                      '報(bào)警��！請(qǐng)立即處理！��！'
                           END)
                       ELSE '報(bào)警�。�！請(qǐng)立即處理,該空間達(dá)到或超過最大文件自動(dòng)擴(kuò)展空間界限,將要影響該空間所有數(shù)據(jù)正常運(yùn)行！'
                    END)
       --警告提示級(jí)別統(tǒng)計(jì)
             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
                                       '警告 ！需按照業(yè)務(wù)增長(zhǎng)量確定是否立刻調(diào)整,目前已'|| SUBSTR(ALARM.ALIVEVALUE,
                                        INSTR(ALARM.ALIVEVALUE,',',1)+1,
                                       INSTR(ALARM.ALIVEVALUE,',',1,2)-INSTR(ALARM.ALIVEVALUE,',',1)-1) ||'天未增長(zhǎng)！'
                                  ELSE
                                      '警告 ��！可能需要立即調(diào)整！'
                              END)
                        ELSE '警告�。�！該空間達(dá)到或超過警告最大文件空間界限,為了不影響數(shù)據(jù)的正常運(yùn)行,請(qǐng)立即處理！'
                    END)
         END) "監(jiān)控描述信息",
       ALARM.IGBYTES "數(shù)據(jù)文件大小(G)",
       ALARM.FILE_NAME "數(shù)據(jù)文件路徑"  
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','監(jiān)控','1','數(shù)據(jù)未增長(zhǎng)','3','暫時(shí)空閑','4','暫停監(jiān)控','5','警告','6','報(bào)警') DS,
                           TX.TABLESPACE_NAME TB,
                           TX.IGBYTES IG,
                           --SUM(TX.IGBYTES) OVER (ORDER BY TX.FILE_NAME) "表空間累計(jì)監(jiān)控值(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 "文件狀態(tài)",TB "表空間名稱",IG || '' "當(dāng)前數(shù)據(jù)文件大小(G)",
       CD || '' "當(dāng)天數(shù)據(jù)量(G)",LD || '' "昨天數(shù)據(jù)量(G)",
       CLD "前/后兩天比率",
       CW || '' "本周累計(jì)總值(G)",LW || '' "上周總值(G)",
       CLW "前/后兩周比率",
       CM || '' "當(dāng)月累計(jì)總值(G)",LM || '' "上月總值(G)",
       CLM "前/后兩月比率"
from ESTIMATE_01
UNION ALL
SELECT '統(tǒng)計(jì)匯總 ','監(jiān)控總計(jì)：' || ESTIMATE_02.SUMS,'當(dāng)前監(jiān)控總計(jì)：' || SUM(ESTIMATE_01.IG),
       '當(dāng)天增值總計(jì)：' || SUM(ESTIMATE_01.CD),'昨天增值總計(jì)：' || SUM(ESTIMATE_01.LD),
       '停止監(jiān)控總計(jì)：' || ESTIMATE_02.UNM,
       '本周累計(jì)總計(jì)：' || SUM(ESTIMATE_01.CW),'上周增值總計(jì)：' || SUM(ESTIMATE_01.LW),
       '空間總計(jì)：' || ESTIMATE_03.UNCM,
       '本月累計(jì)總計(jì)：' || SUM(ESTIMATE_01.CM),'上月增值總計(jì)：' || SUM(ESTIMATE_01.LM),
       '未加入監(jiān)控總計(jì)：' || 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;


本文出自：億恩科技【prubsntakaful.com】

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]
  • 您可能在找
  • 億恩北京公司：
  • 經(jīng)營(yíng)性ICP/ISP證：京B2-20150015
    •
  • 億恩鄭州公司：
  • 經(jīng)營(yíng)性ICP/ISP/IDC證：豫B1.B2-20060070
    •
  • 億恩南昌公司：
  • 經(jīng)營(yíng)性ICP/ISP證：贛B2-20080012
    •
  • 服務(wù)器/云主機(jī) 24小時(shí)售后服務(wù)電話：0371-60135900
  • 虛擬主機(jī)/智能建站 24小時(shí)售后服務(wù)電話：0371-60135900
    •
    專注服務(wù)器托管17年
    掃掃關(guān)注-微信公眾號(hào)
    0371-60135900
    Copyright© 1999-2019 ENKJ All Rights Reserved 億恩科技 版權(quán)所有　 地址：鄭州市高新區(qū)翠竹街1號(hào)總部企業(yè)基地億恩大廈　 法律顧問：河南亞太人律師事務(wù)所郝建鋒、杜慧月律師   京公網(wǎng)安備41019702002023號(hào)
      0
     
     
     
     

    0371-60135900
    7*24小時(shí)客服服務(wù)熱線