Oracle tablespace 监控项目版

主要实现功能：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 ";
$header .="From:<clement.ge@finalist.hk>" ." ";
$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;Oracle 安装报错 [INS-06101] IP address of localhost could not be determined 解决方法Oracle中SID的作用相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）