zabbix监控oracle 12c

3231212 · 发表于 2016-1-8 09:47:41

前言某公司需要监控Oracle 12C，采用orabbix是zabbix监控oracle数据库的插件，修改一些地方就可以监控了。

一.安装配置mkdir /opt/orabbix
cd /opt/orabbix
mv conf/config.props.sample conf/config.props
cp init.d/orabbix /etc/init.d/
chmod +x /etc/init.d/orabbix
chkconfig orabbix on

二.修改orabbix配置注意：红色是修地方
vi conf/config.props

#comma separed list of Zabbix servers
ZabbixServerList=ZabbixServer

ZabbixServer.Address=0.0.0.0 #zabbix监控IP
ZabbixServer.Port=10051 #zabbix监控端口

#pidFile
OrabbixDaemon.PidFile=./logs/orabbix.pid
#frequency of item's refresh
OrabbixDaemon.Sleep=60
#MaxThreadNumber should be >= than the number ofyour databases
OrabbixDaemon.MaxThreadNumber=200

#put here your databases in a comma separated list
DatabaseList=10.78.136.18,10.78.207.22
#被监控ORACALEIP地址，采用，分开

#Configuration of Connection pool
#if not specified Orabbis is going to use defaultvalues (hardcoded)
#Maximum number of active connection inside pool
DatabaseList.MaxActive=10
#The maximum number of milliseconds that the poolwill wait
#(when there are no available connections) for aconnection to be returned
#before throwing an exception, or <= 0 to waitindefinitely.
DatabaseList.MaxWait=100
DatabaseList.MaxIdle=1

#define here your connection string foreach database
10.78.136.18.Url=jdbc:oracle:thin:@10.78.136.18:1521/syslog
10.78.136.18.User=zabbix
10.78.136.18.Password=zabbix
#Those values are optionals if notspecified Orabbix is going to use the general values
10.78.136.18.MaxActive=10
10.78.136.18.MaxWait=100
10.78.136.18.MaxIdle=1
10.78.136.18.QueryListFile=./conf/query.props

#define here your connection string foreach database
10.78.207.22.Url=jdbc:oracle:thin:@10.78.207.22:1521/sjyyt
10.78.207.22.User=zabbix
10.78.207.22.Password=zabbix
#Those values are optionals if notspecified Orabbix is going to use the general values
10.78.207.22.MaxActive=10
10.78.207.22.MaxWait=100
10.78.207.22.MaxIdle=1
10.78.207.22.QueryListFile=./conf/query.props

execdbms_network_acl_admin.create_acl(acl=> 'resolve.xml',description =>'resolve acl', principal =>'ZABBIX',is_grant => true, privilege =>'resolve');

execdbms_network_acl_admin.assign_acl(acl=> 'resolve.xml', host =>'*');

commit;

三.创建ORACLE 监控账户1.登录ORACLE 命令行
su– oracale
sqlplus/ as sydba
selectinstance_name from v$instance; # 查看实例

2、创建用户

CREATE USER ZABBIX

IDENTIFIED BY ZABBIX

DEFAULT TABLESPACE SYSTEM

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

3、赋予角色权限

GRANT CONNECT TO ZABBIX;

GRANT RESOURCE TO ZABBIX;

ALTER USER ZABBIX DEFAULT ROLEALL;

4、赋予系统权限

GRANT SELECT ANY TABLE TO ZABBIX;

GRANT CREATE SESSION TO ZABBIX;

GRANT SELECT ANY DICTIONARY TO ZABBIX;

GRANT UNLIMITED TABLESPACE TO ZABBIX;

GRANT SELECT ANY DICTIONARY TO ZABBIX;

四.启动/etc/init.d/orabbix start

五、导入模板 QQ截图20160108094412.jpg

组态-》模板-》汇入
QQ截图20160108094421.jpg

点击汇入

六、配置主机 QQ截图20160108094438.jpg

注意：主机名一定与orabbix配置一样

QQ截图20160108094457.jpg

点击添加–》存档

七．增加SQL查询修改vi conf/query.props
DefaultQueryPeriod=2

QueryList=archive,audit,dbblockgets,dbconsistentgets,dbhitratio,dbphysicalread,dbversion,hitratio_body,hitratio_sqlarea,hitratio_table_proc,\
lio_current_read,locks,maxprocs,maxsession,miss_latch,pga_aggregate_target,pga,phio_datafile_reads,phio_datafile_writes,phio_redo_writes,pinhitratio_body,pinhitratio_sqlarea,pinhitratio_table-proc,pinhitratio_trigger,\
pool_dict_cache,pool_free_mem,pool_lib_cache,pool_misc,pool_sql_area,procnum,session_active,session_inactive,session,session_system,sga_buffer_cache,\
sga_fixed,sga_java_pool,sga_large_pool,sga_log_buffer,sga_shared_pool,tbl_space,userconn,waits_controfileio,waits_directpath_read,\
waits_file_io,waits_latch,waits_logwrite,waits_multiblock_read,waits_singleblock_read,hitratio_trigger,lio_block_changes,lio_consistent_read,waits_other,waits_sqlnet,users_locked,uptime,config_session,connect_session,active_session,Lactch_Enq_Event,User_IO,System_IO,,Administrative,Scheduler,Configuration,Rac,Application,Queueing,Network,Commit,Idle,dbfilesize,dbsize

DataGuardPrimaryQueryList=dg_error,dg_sequence_number
DataGuardStandbyQueryList=dg_sequence_number_stby
RmanQueryList=rman_check_status

rman_check_status.Query=select ' DBNAME->'||DB_NAME||'- ROW TYPE->'||ROW_TYPE||'- STARTTIME->'||to_char(start_time, 'Dy DD-Mon-YYYY HH24:MI:SS') ||'- ENDTIME->'||to_char(end_time, 'Dy DD-Mon-YYYY HH24:MI:SS')||'- MBYTESPROCESSED->'||MBYTES_PROCESSED||'- OBJECT TYPE->'||OBJECT_TYPE||'-STATUS->'||STATUS||'- OUTPUT DEVICE->'||OUTPUT_DEVICE_TYPE||'- INPUTMB->'||INPUT_BYTES/1048576||'- OUT MB'||OUTPUT_BYTES/1048576 \
 FROM rc_rman_status \
 WHERE start_time > SYSDATE - 1\
 AND ( STATUS like '%FAILED%' \
 OR STATUS like '%ERROR%') \
 ORDER BY END_TIME

rman_check_status.NoDataFound=none

uptime.Query=selectto_char((sysdate-startup_time)*86400, 'FM99999999999999990') retvalue fromv$instance

users_locked.Query=SELECT username||' '||lock_date ||' '|| account_status FROM dba_users where ACCOUNT_STATUS like'EXPIRED(GRACE)' or ACCOUNT_STATUS like 'LOCKED(TIMED)'
users_locked.NoDataFound=none

archive.Query=selectround(A.LOGS*B.AVG/1024/1024/10) from ( SELECT COUNT (*) LOGS FROM V$LOG_HISTORY WHERE FIRST_TIME>= (sysdate -10/60/24)) A, ( SELECT Avg(BYTES) AVG, Count(1), Max(BYTES) Max_Bytes, Min(BYTES)Min_Bytes FROM v$log) B
archive.RaceConditionQuery=select valuefrom v$parameter where name='log_archive_start'
archive.RaceConditionValue=FALSE

audit.Query=select username"username", \
 to_char(timestamp,'DD-MON-YYYYHH24:MI:SS') "time_stamp", \
 action_name"statement", \
 os_username"os_username", \
 userhost"userhost", \
 returncode||decode(returncode,'1004','-WrongConnection','1005','-NULL Password','1017','-WrongPassword','1045','-Insufficient Priviledge','0','-Login Accepted','--')"returncode" \
 fromsys.dba_audit_session \
 where(sysdate - timestamp)*24 < 1 and returncode <> 0 \
 orderby timestamp
audit.NoDataFound=none

dbblockgets.Query=selectto_char(sum(decode(name,'db block gets', value,0))) "block_gets" \
 FROMv$sysstat

dbconsistentgets.Query=selectto_char(sum(decode(name,'consistent gets', value,0)))"consistent_gets" \
 FROMv$sysstat

dbhitratio.Query=select ( \
 sum(decode(name,'consistentgets', value,0)) + sum(decode(name,'db block gets', value,0)) -sum(decode(name,'physical reads', value,0))) / (sum(decode(name,'consistentgets', value,0)) + sum(decode(name,'db block gets', value,0)) ) * 100"hit_ratio" \
 FROMv$sysstat
dbphysicalread.Query=selectsum(decode(name,'physical reads', value,0)) "phys_reads" FROMv$sysstat

dbversion.Query=select COMP_ID||''||COMP_NAME||' '||VERSION||' '||STATUS||' ' from dba_registryunion SELECT ' - SERVERNAME = '||UTL_INADDR.get_host_name||' - SERVERADDRESS ='||UTL_INADDR.get_host_address||' 'from dualunion SELECT ' - DB_NAME = '||SYS_CONTEXT ('USERENV', 'DB_NAME')||' - INSTANCE_NAME = ' ||SYS_CONTEXT ('USERENV','INSTANCE_NAME')||' ' FROM dual

sqlnotindexed.Query=SELECT SUM(DECODE(NAME,'table scans (long tables)', VALUE, 0))/ (SUM(DECODE(NAME, 'table scans (longtables)', VALUE, 0))+SUM(DECODE(NAME, 'table scans (short tables)', VALUE,0)))*100 SQL_NOT_INDEXED FROM V$SYSSTAT WHERE 1=1 AND ( NAME IN ('table scans(long tables)','table scans (short tables)') )

hitratio_body.Query=select gethitratio*100"get_pct" FROM v$librarycache where namespace ='BODY'
hitratio_sqlarea.Query=selectgethitratio*100 "get_pct" FROM v$librarycache where namespace ='SQLAREA'
hitratio_trigger.Query=selectgethitratio*100 "get_pct" FROM v$librarycache where namespace='TRIGGER'
hitratio_table_proc.Query=selectgethitratio*100 "get_pct" FROM v$librarycache where namespace ='TABLE/PROCEDURE'

lio_block_changes.Query=SELECTto_char(SUM(DECODE(NAME,'db block changes',VALUE,0))) \
FROM V$SYSSTAT \
WHERE NAME ='db block changes'

lio_consistent_read.Query=SELECTto_char(sum(decode(name,'consistent gets',value,0))) FROM V$SYSSTAT WHERE NAME='consistent gets'
lio_current_read.Query=SELECTto_char(sum(decode(name,'db block gets',value,0))) FROM V$SYSSTAT WHERE NAME='db block gets'

locks.Query=SELECT b.session_id AS sid, \
 NVL(b.oracle_username, '(oracle)') AS username, \
 a.owner AS object_owner, \
 a.object_name, \
 Decode(b.locked_mode, 0, 'None', \
 1, 'Null (NULL)', \
 2, 'Row-S (SS)', \
 3, 'Row-X (SX)', \
 4, 'Share (S)', \
 5, 'S/Row-X(SSX)', \
 6, 'Exclusive(X)', \
 b.locked_mode)locked_mode, \
 b.os_user_name \
FROM dba_objects a, \
 v$locked_object b \
WHERE a.object_id = b.object_id \
ORDER BY 1, 2, 3, 4

#locks.Query=select sn.USERNAME ||'@'||sn.machine, \
 '|SID->'|| m.SID, \
 '|Serial->'||sn.SERIAL#, \
 '|LockType->'||m.TYPE, \
 decode(LMODE,\
 1,'Null', \
 2,'Row-S (SS)', \
 3,'Row-X (SX)', \
 4,'Share', \
 5,'S/Row-X (SSX)', \
 6,'Exclusive') lock_type, \
 decode(REQUEST,\
 0,'None', \
 1,'Null', \
 2,'Row-S (SS)', \
 3,'Row-X (SX)', \
 4, 'Share', \
 5,'S/Row-X (SSX)', \
 6,'Exclusive') lock_requested, \
 '|Time(Sec)->'||m.CTIME "Time(sec)", \
 '|ID1->'||m.ID1,\
 '|ID2->'||m.ID2,\
 '|SQLText->'||t.SQL_TEXT \
from v$sessionsn, \
 v$lockm , \
 v$sqltextt \
where t.ADDRESS=sn.SQL_ADDRESS \
and t.HASH_VALUE=sn.SQL_HASH_VALUE \
and ((sn.SID=m.SID and m.REQUEST !=0) \
or (sn.SID=m.SID and m.REQUEST =0 and LMODE !=4 and (ID1, ID2) in \
(select s.ID1, s.ID2 \
from v$lock S \
where REQUEST !=0 \
 and s.ctime > 5 \
and s.ID1 =m.ID1 \
and s.ID2 =m.ID2))) \
order by sn.USERNAME, sn.SID, t.PIECE
locks.NoDataFound=none

maxprocs.Query=select value"maxprocs" from v$parameter where name ='processes'
maxsession.Query=select value"maxsess" from v$parameter where name ='sessions'
miss_latch.Query=SELECT SUM(misses) FROMV$LATCH
pga_aggregate_target.Query=selectto_char(decode( unit,'bytes', value/1024/1024, value),'999999999.9') value fromV$PGASTAT where name in 'aggregate PGA target parameter'
pga.Query=select to_char(decode(unit,'bytes', value/1024/1024, value),'999999999.9') value from V$PGASTAT wherename in 'total PGA inuse'
phio_datafile_reads.Query=selectto_char(sum(decode(name,'physical reads direct',value,0))) FROM V$SYSSTAT wherename ='physical reads direct'
phio_datafile_writes.Query=selectto_char(sum(decode(name,'physical writes direct',value,0))) FROM V$SYSSTATwhere name ='physical writes direct'
phio_redo_writes.Query=select to_char(sum(decode(name,'redowrites',value,0))) FROM V$SYSSTAT where name ='redo writes'
pinhitratio_body.Query=selectpins/(pins+reloads)*100 "pin_hit ratio" FROM v$librarycache wherenamespace ='BODY'
pinhitratio_sqlarea.Query=selectpins/(pins+reloads)*100 "pin_hit ratio" FROM v$librarycache wherenamespace ='SQL AREA'
pinhitratio_table-proc.Query=selectpins/(pins+reloads)*100 "pin_hit ratio" FROM v$librarycache wherenamespace ='TABLE/PROCEDURE'
pinhitratio_trigger.Query=selectpins/(pins+reloads)*100 "pin_hit ratio" FROM v$librarycache wherenamespace ='TRIGGER'
pool_dict_cache.Query=SELECTTO_CHAR(ROUND(SUM(decode(pool,'shared pool',decode(name,'dictionarycache',(bytes)/(1024*1024),0),0)),2)) pool_dict_cache FROM V$SGASTAT
pool_free_mem.Query=SELECT TO_CHAR(ROUND(SUM(decode(pool,'sharedpool',decode(name,'free memory',(bytes)/(1024*1024),0),0)),2)) pool_free_memFROM V$SGASTAT
pool_lib_cache.Query=SELECTTO_CHAR(ROUND(SUM(decode(pool,'shared pool',decode(name,'librarycache',(bytes)/(1024*1024),0),0)),2)) pool_lib_cache FROM V$SGASTAT
pool_misc.Query=SELECTTO_CHAR(ROUND(SUM(decode(pool,'shared pool',decode(name,'librarycache',0,'dictionary cache',0,'free memory',0,'sql area',0,(bytes)/(1024*1024)),0)),2)) pool_misc FROM V$SGASTAT
pool_sql_area.Query=SELECT TO_CHAR(ROUND(SUM(decode(pool,'sharedpool',decode(name,'sql area',(bytes)/(1024*1024),0),0)),2)) pool_sql_area FROMV$SGASTAT
procnum.Query=select count(*)"procnum" from v$process
session_active.Query=select count(*) fromv$session where TYPE!='BACKGROUND' and status='ACTIVE'
session_inactive.Query=selectSUM(Decode(Type, 'BACKGROUND', 0, Decode(Status, 'ACTIVE', 0, 1))) FROMV$SESSION
session.Query=select count(*) fromv$session
session_system.Query=selectSUM(Decode(Type, 'BACKGROUND', 1, 0)) system_sessions FROM V$SESSION
sga_buffer_cache.Query=SELECTto_char(ROUND(SUM(decode(pool,NULL,decode(name,'db_block_buffers',(bytes)/(1024*1024),'buffer_cache',(bytes)/(1024*1024),0),0)),2))sga_bufcache FROM V$SGASTAT
sga_fixed.Query=SELECT TO_CHAR(ROUND(SUM(decode(pool,NULL,decode(name,'fixed_sga',(bytes)/(1024*1024),0),0)),2))sga_fixed FROM V$SGASTAT
sga_java_pool.Query=SELECTto_char(ROUND(SUM(decode(pool,'java pool',(bytes)/(1024*1024),0)),2)) sga_jpoolFROM V$SGASTAT
sga_large_pool.Query=SELECTto_char(ROUND(SUM(decode(pool,'large pool',(bytes)/(1024*1024),0)),2))sga_lpool FROM V$SGASTAT
sga_log_buffer.Query=SELECTTO_CHAR(ROUND(SUM(decode(pool,NULL,decode(name,'log_buffer',(bytes)/(1024*1024),0),0)),2))sga_lbuffer FROM V$SGASTAT
sga_shared_pool.Query=SELECTTO_CHAR(ROUND(SUM(decode(pool,'shared pool',decode(name,'librarycache',0,'dictionary cache',0,'free memory',0,'sqlarea',0,(bytes)/(1024*1024)),0)),2)) pool_misc FROM V$SGASTAT

tbl_space.Query=SELECT * FROM ( \
select '- Tablespace->',t.tablespace_name ktablespace, \
 '- Type->',substr(t.contents, 1, 1) tipo, \
 '- Used(MB)->',trunc((d.tbs_size-nvl(s.free_space, 0))/1024/1024)ktbs_em_uso, \
 '- ActualSize(MB)->',trunc(d.tbs_size/1024/1024) ktbs_size, \
 '- MaxSize(MB)->',trunc(d.tbs_maxsize/1024/1024) ktbs_maxsize, \
 '- FreeSpace(MB)->',trunc(nvl(s.free_space, 0)/1024/1024)kfree_space, \
 '- Space->',trunc((d.tbs_maxsize - d.tbs_size + nvl(s.free_space,0))/1024/1024) kspace, \
 '- Perc->',decode(d.tbs_maxsize, 0, 0,trunc((d.tbs_size-nvl(s.free_space, 0))*100/d.tbs_maxsize)) kperc \
from \
 (select SUM(bytes) tbs_size, \
 SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize,tablespace_name tablespace \
from ( select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes,tablespace_name \
from dba_data_files \
union all \
select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name \
from dba_temp_files \
)\
group by tablespace_name \
)d, \
(select SUM(bytes) free_space, \
tablespace_name tablespace \
from dba_free_space \
group by tablespace_name \
)s, \
dba_tablespaces t \
where t.tablespace_name = d.tablespace(+) and \
t.tablespace_name = s.tablespace(+) \
order by 8) \
where kperc > 93 \
and tipo <>'T' \
and tipo <>'U'
tbl_space.NoDataFound=none

userconn.Query=select count(username) fromv$session where username is not null
waits_controfileio.Query=SELECTto_char(sum(decode(event,'control file sequential read', total_waits, 'controlfile single write', total_waits, 'control file parallel write',total_waits,0)))ControlFileIO FROM V$system_event WHERE 1=1 AND event not in ( 'SQL*Net messagefrom client', 'SQL*Net more data from client','pmon timer', 'rdbms ipcmessage', 'rdbms ipc reply', 'smon timer')

waits_directpath_read.Query=SELECTto_char(sum(decode(event,'direct path read',total_waits,0))) DirectPathReadFROM V$system_event WHERE 1=1 AND event not in ( 'SQL*Net message from ', 'SQL*Net more datafrom client','pmon timer', 'rdbms ipc message', 'rdbms ipc reply', 'smontimer')

waits_file_io.Query=SELECTto_char(sum(decode(event,'file identify',total_waits, 'fileopen',total_waits,0))) FileIO FROM V$system_event WHERE 1=1 AND event not in( 'SQL*Net message from client', 'SQL*Net more data from client', 'pmontimer', 'rdbms ipc message', 'rdbms ipc reply', 'smon timer')

waits_latch.Query=SELECTto_char(sum(decode(event,'control file sequential read', total_waits, \
'control file single write', total_waits,'control file parallel write',total_waits,0))) ControlFileIO \
FROM V$system_event WHERE 1=1 AND event notin ( \
'SQL*Net message from client', \
'SQL*Net more data from client', \
'pmon timer', 'rdbms ipc message', \
'rdbms ipc reply', 'smon timer')

waits_logwrite.Query=SELECTto_char(sum(decode(event,'log file single write',total_waits, 'log fileparallel write',total_waits,0))) LogWrite \
FROM V$system_event WHERE 1=1 AND event notin ( \
'SQL*Net message from client', \
'SQL*Net more data from client', \
'pmon timer', 'rdbms ipc message', \
'rdbms ipc reply', 'smon timer')

waits_multiblock_read.Query=SELECTto_char(sum(decode(event,'db file scattered read',total_waits,0)))MultiBlockRead \
FROM V$system_event WHERE 1=1 AND event notin ( \
'SQL*Net message from client', \
'SQL*Net more data from client', \
'pmon timer', 'rdbms ipc message', \
'rdbms ipc reply', 'smon timer')

waits_other.Query=SELECTto_char(sum(decode(event,'control file sequential read',0,'control file singlewrite',0,'control file parallel write',0,'db file sequential read',0,'db filescattered read',0,'direct path read',0,'file identify',0,'file open',0,'SQL*Netmessage to client',0,'SQL*Net message to dblink',0, 'SQL*Net more data toclient',0,'SQL*Net more data to dblink',0, 'SQL*Net break/reset toclient',0,'SQL*Net break/reset to dblink',0, 'log file single write',0,'logfile parallel write',0,total_waits))) Other FROM V$system_event WHERE 1=1 ANDevent not in ( 'SQL*Net message fromclient', 'SQL*Net more data from client', 'pmon timer', 'rdbms ipcmessage', 'rdbms ipc reply', 'smontimer')

waits_singleblock_read.Query=SELECTto_char(sum(decode(event,'db file sequential read',total_waits,0)))SingleBlockRead \
FROM V$system_event WHERE 1=1 AND event notin ( \
'SQL*Net message from client', \
'SQL*Net more data from client', \
'pmon timer', 'rdbms ipc message', \
'rdbms ipc reply', 'smon timer')

waits_sqlnet.Query=SELECTto_char(sum(decode(event,'SQL*Net message to client',total_waits,'SQL*Netmessage to dblink',total_waits,'SQL*Net more data toclient',total_waits,'SQL*Net more data to dblink',total_waits,'SQL*Netbreak/reset to client',total_waits,'SQL*Net break/reset todblink',total_waits,0))) SQLNET FROM V$system_event WHERE 1=1 \
AND event not in ( 'SQL*Net message fromclient','SQL*Net more data from client','pmon timer','rdbms ipc message','rdbmsipc reply', 'smon timer')

dg_error.Query=SELECT ERROR_CODE, SEVERITY,MESSAGE, TO_CHAR(TIMESTAMP, 'DD-MON-RR HH24:MI:SS') TIMESTAMP FROMV$DATAGUARD_STATUS WHERE CALLOUT='YES' AND TIMESTAMP > SYSDATE-1
dg_error.NoDataFound=none

dg_sequence_number.Query=SELECT MAX(sequence#) FROM v$log_history

#dg_sequence_number_stby.Query=SELECT MAX(sequence#) last_log_applied FROM v$log_history
dg_sequence_number_stby.Query= selectmax(sequence#) from v$archived_log

config_session.Query=select valueconfig_session from v$parameter where name='sessions'
connect_session.Query=select count(*)connect_session from v$session where username is not null
active_session.Query=select count(*)active_session from v$session where username is not null and status='ACTIVE'

Lactch_Enq_Event.Query=select count(*)Latch_Enq_Event from v$session wherewait_class in ('Concurrency','Other') and username is not null
User_IO.Query=select count(*) User_IO from v$session where wait_class ='User I/O'and username is not null
System_IO.Query=select count(*)System_IO from v$session wherewait_class ='System I/O' and username is not null
Administrative.Query=select count(*)Administrative from v$session wherewait_class ='Administrative' and username is not null
Scheduler.Query=select count(*)Scheduler from v$session where wait_class='Scheduler' and username is not null
Configuration.Query=select count(*)Configuration from v$session wherewait_class ='Configuration' and username is not null
Rac.Query=select count(*) Rac from v$session where wait_class ='Cluster'and username is not null
Application.Query=select count(*)Application from v$session wherewait_class ='Application' and username is not null
Queueing.Query=select count(*)Queueing from v$session where wait_class='Queueing' and username is not null

Network.Query=select count(*) Network from v$session where wait_class='Network' and username is not null
Commit.Query=select count(*) Commit from v$session where wait_class ='Commit' andusername is not null
Idle.Query=select count(*) Idle from v$session where wait_class ='Idle' andusername is not null and event not in ('SQL*Net message from client')

dbfilesize.Query=selectto_char(sum(bytes/1024/1024/1024), 'FM99999999999999990') retvalue fromdba_data_files
dbsize.Query=SELECTto_char(sum( NVL(a.bytes/1024/1024/1024 - NVL(f.bytes/1024/1024/1024, 0), 0)),'FM99999999999999990') retvalue \
FROM sys.dba_tablespacesd, \
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a, \
(select tablespace_name,sum(bytes) bytes from dba_free_space group by tablespace_name) f \
WHERE d.tablespace_name =a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) \
AND NOT(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')

以上找到QueryList= 增加dbfilesize
最下面增加查询SQL语句
格式如dbfilesize.Query=

模板里增加dbfilesize
QQ截图20160108094512.jpg