前言:
而今同学们对“oracle添加栏位”都比较重视,朋友们都需要分析一些“oracle添加栏位”的相关内容。那么小编也在网络上搜集了一些对于“oracle添加栏位””的相关资讯,希望兄弟们能喜欢,姐妹们一起来了解一下吧!最早我安装的zabbix还是1.0版本,用了一段时间升级到了3.0,这个版本一直了用了好多年,也足够用了。没想到最近zabbix总是宕机,无奈只能重新安装了最新版本5。发现功能比以前强大了很多啊,网络上也出现很多新的监控方案,特别是自动发现IP和web监控的,很受用啊。其中对SAP环境下Oracle数据库监控,以前我是用orabbix这个插件,用起来挺好的,可以自己新增监控项目,只要你会写sql语句。而新版本直接增加了Template DB Oracle by ODBC这个模板,直接拿来用,简直爽歪歪啊。
zabbix5.0版本对oracle监控很好用,但对表空间tablespace,似乎少了一些东西,只有下面这些监控项。
而SAP事务代码db02的表空间显示如下图,对比下,大概zabbix只显示了其中4个栏位,如下图红色标记:
如果想监控其他的栏位,那得改写,无奈那个sql太复杂,看不懂啊,也不想重头写,于是就借用了SAP官方提供的脚本:Space_Tablespaces_11g+.txt,这个脚本可以去SAP网站下载哦。执行出来的结果如下图:
你会发现单位是GB的,zabbix建议使用单位B,至于为什么?因为在显示的时候它可以自动转义,B可以转为MB/GB/TB,如果你单位是GB,在转换TB时就变成了KGB,就看起来很别扭了。那我改改吧。
SELECT NULL TABLESPACE, NULL TABLESPACE_TYPE, NULL E, NULL C, NULL ALLOC_GB, NULL "DB_%", NULL AUTOEXT_GB, NULL USED_GB, NULL "USED_PCT", NULL FREE_GB, NULL FILES, NULL SEGMENTS, NULL EXTENTSFROM DUAL WHERE 1 = 0UNION ALL (SELECT NULL TABLESPACE, NULL TABLESPACE_TYPE, NULL E, NULL C, NULL ALLOC_GB, NULL "ALLOC_%", NULL AUTOEXT_GB, NULL USED_GB, NULL "USED_PCT", NULL FREE_GB, NULL FILES, NULL SEGMENTS, NULL EXTENTSFROM DUAL WHERE 1 = 0) UNION ALL (SELECT * FROM (WITH BASIS_INFO AS( SELECT /* 11g: Tablespace encryption */ ' ' INCLUDE_USAGE_METRICS, 'TABLESPACE' AGGREGATE_BY, 'SIZE' ORDER_BY, '%' TABLESPACE_PATTERN_1, '%' TABLESPACE_PATTERN_2, TO_DATE('01.01.1000 01:00:00', 'dd.mm.yyyy hh24:mi:ss') CREATED_MIN_TIME, TO_DATE('01.01.1000 01:00:00', 'dd.mm.yyyy hh24:mi:ss') LAST_DDL_MIN_TIME FROM DUAL),TABLESPACES AS( SELECT TS.TABLESPACE_NAME, TS.CONTENTS, SUBSTR(TS.ENCRYPTED, 1, 1) ENCRYPTED, DECODE(TS.COMPRESS_FOR, NULL, 'N', 'BASIC', 'B', 'OLTP', 'Y') COMPRESSED, COUNT(*) DATAFILES, SUM(DF.BYTES) BYTES, MAX(TUM.TABLESPACE_SIZE) * MAX(TS.BLOCK_SIZE) ALLOC_BYTES_USAGE_METRIC, MAX(TUM.USED_SPACE) * MAX(TS.BLOCK_SIZE) USED_BYTES_USAGE_METRIC, DECODE(TS.EXTENT_MANAGEMENT, 'DICTIONARY', 'DMTS', 'LMTS') || '/' || SUBSTR(TS.CONTENTS, 1, 1) || DECODE(TS.ALLOCATION_TYPE, 'SYSTEM', ' (SYS)', 'UNIFORM', ' (UNI ' || ROUND(TS.MIN_EXTLEN / 1024 / 1024) || 'M)') || DECODE(TS.SEGMENT_SPACE_MANAGEMENT, 'AUTO', ', ASSM') TABLESPACE_TYPE, SUM(DECODE(DF.AUTOEXTENSIBLE, 'NO', DF.BYTES, DF.MAXBYTES)) MAX_BYTES FROM BASIS_INFO BI, DBA_DATA_FILES DF, DBA_TABLESPACES TS, DBA_TABLESPACE_USAGE_METRICS TUM WHERE ( BI.TABLESPACE_PATTERN_1 = '%' AND BI.TABLESPACE_PATTERN_2 = '%' OR BI.TABLESPACE_PATTERN_1 != '%' AND DF.TABLESPACE_NAME LIKE BI.TABLESPACE_PATTERN_1 OR BI.TABLESPACE_PATTERN_2 != '%' AND DF.TABLESPACE_NAME LIKE BI.TABLESPACE_PATTERN_2 ) AND DF.TABLESPACE_NAME = TS.TABLESPACE_NAME AND DF.TABLESPACE_NAME = TUM.TABLESPACE_NAME (+) GROUP BY TS.TABLESPACE_NAME, TS.CONTENTS, TS.ENCRYPTED, TS.COMPRESS_FOR, TS.EXTENT_MANAGEMENT, TS.CONTENTS, TS.ALLOCATION_TYPE, TS.MIN_EXTLEN, TS.SEGMENT_SPACE_MANAGEMENT, BI.TABLESPACE_PATTERN_1, BI.TABLESPACE_PATTERN_2 UNION ALL ( SELECT TS.TABLESPACE_NAME, TS.CONTENTS, SUBSTR(TS.ENCRYPTED, 1, 1) ENCRYPTED, DECODE(TS.COMPRESS_FOR, NULL, 'N', 'BASIC', 'B', 'OLTP', 'Y') COMPRESSED, COUNT(*) DATAFILES, SUM(TF.BYTES) BYTES, MAX(TUM.TABLESPACE_SIZE) * MAX(TS.BLOCK_SIZE) ALLOC_BYTES_USAGE_METRIC, MAX(TUM.USED_SPACE) * MAX(TS.BLOCK_SIZE) USED_BYTES_USAGE_METRIC, DECODE(TS.EXTENT_MANAGEMENT, 'DICTIONARY', 'DMTS', 'LMTS') || '/' || SUBSTR(TS.CONTENTS, 1, 1) || DECODE(TS.ALLOCATION_TYPE, 'SYSTEM', ' (SYS)', 'UNIFORM', ' (UNI ' || ROUND(TS.MIN_EXTLEN / 1024 / 1024) || 'M)') || DECODE(TS.SEGMENT_SPACE_MANAGEMENT, 'AUTO', ', ASSM') TABLESPACE_TYPE, SUM(DECODE(AUTOEXTENSIBLE, 'NO', TF.BYTES, TF.MAXBYTES)) MAX_BYTES FROM BASIS_INFO BI, DBA_TEMP_FILES TF, DBA_TABLESPACES TS, DBA_TABLESPACE_USAGE_METRICS TUM WHERE ( BI.TABLESPACE_PATTERN_1 = '%' AND BI.TABLESPACE_PATTERN_2 = '%' OR BI.TABLESPACE_PATTERN_1 != '%' AND TF.TABLESPACE_NAME LIKE BI.TABLESPACE_PATTERN_1 OR BI.TABLESPACE_PATTERN_2 != '%' AND TF.TABLESPACE_NAME LIKE BI.TABLESPACE_PATTERN_2 ) AND TF.TABLESPACE_NAME = TS.TABLESPACE_NAME AND TF.TABLESPACE_NAME = TUM.TABLESPACE_NAME (+) GROUP BY TS.TABLESPACE_NAME, TS.CONTENTS, TS.ENCRYPTED, TS.COMPRESS_FOR, TS.EXTENT_MANAGEMENT, TS.CONTENTS, TS.ALLOCATION_TYPE, TS.MIN_EXTLEN, TS.SEGMENT_SPACE_MANAGEMENT, BI.TABLESPACE_PATTERN_1, BI.TABLESPACE_PATTERN_2 )),SEGMENTS AS( SELECT TABLESPACE_NAME, COUNT(*) SEGMENTS, SUM(BYTES) BYTES, SUM(EXTENTS) EXTENTS FROM ( SELECT S.TABLESPACE_NAME, S.BYTES, S.EXTENTS, O.CREATED, O.LAST_DDL_TIME, BI.CREATED_MIN_TIME, BI.LAST_DDL_MIN_TIME FROM BASIS_INFO BI, DBA_OBJECTS O, DBA_SEGMENTS S WHERE O.OWNER (+) = S.OWNER AND O.OBJECT_NAME (+) = S.SEGMENT_NAME AND O.OBJECT_TYPE (+) = S.SEGMENT_TYPE AND NVL(O.SUBOBJECT_NAME (+), ' ') = NVL(S.PARTITION_NAME, ' ') ) WHERE CREATED IS NULL OR /* undo segments are not recorded in DBA_OBJECTS, for example */ ( CREATED >= CREATED_MIN_TIME AND LAST_DDL_TIME >= LAST_DDL_MIN_TIME ) GROUP BY TABLESPACE_NAME UNION ALL ( SELECT TABLESPACE_NAME, 0 SEGMENTS, SUM(BYTES_USED) BYTES, SUM(EXTENTS_USED) EXTENTS FROM GV$TEMP_EXTENT_POOL GROUP BY TABLESPACE_NAME ))SELECT DECODE(BI.AGGREGATE_BY, 'TABLESPACE', T.TABLESPACE_NAME, 'CONTENT', T.CONTENTS) AREA, DECODE(BI.AGGREGATE_BY, 'TABLESPACE', T.TABLESPACE_TYPE, 'CONTENT', 'n/a') TABLESPACE_TYPE, DECODE(BI.AGGREGATE_BY, 'TABLESPACE', DECODE(T.ENCRYPTED, 'N', ' ', T.ENCRYPTED), 'CONTENT', 'n/a') E, DECODE(BI.AGGREGATE_BY, 'TABLESPACE', DECODE(T.COMPRESSED, 'N', ' ', T.COMPRESSED), 'CONTENT', 'n/a') C, TO_CHAR(SUM(T.BYTES) , 999999999999999990.99) ALLOC_GB, TO_CHAR(RATIO_TO_REPORT(SUM(T.BYTES)) OVER () * 100, 990.99) "DB_%", TO_CHAR(SUM(T.MAX_BYTES) , 9999999999999990.99) || DECODE(BI.INCLUDE_USAGE_METRICS, 'X', ' (' || DECODE(SUM(T.ALLOC_BYTES_USAGE_METRIC), NULL, ' n/a', TO_CHAR(SUM(T.ALLOC_BYTES_USAGE_METRIC) , 99999999999999999999.99)) || ')') AUTOEXT_GB, TO_CHAR(SUM(NVL(S.BYTES, 0)) , 999999999999999990.99) || DECODE(BI.INCLUDE_USAGE_METRICS, 'X', ' (' || DECODE(SUM(T.USED_BYTES_USAGE_METRIC), NULL, ' n/a', TO_CHAR(SUM(T.USED_BYTES_USAGE_METRIC), 9999999999999999990.99)) || ')') USED_GB, TO_CHAR(SUM(NVL(S.BYTES, 0)) / SUM(T.BYTES) * 100, 990.99) "USED_%", TO_CHAR((SUM(T.BYTES) - SUM(NVL(S.BYTES, 0))) , 9999999999990.99) FREE_GB, TO_CHAR(SUM(T.DATAFILES), 9990) FILES, TO_CHAR(SUM(NVL(S.SEGMENTS, 0)), 9999990) SEGMENTS, TO_CHAR(SUM(NVL(S.EXTENTS, 0)), 99999990) EXTENTSFROM TABLESPACES T, SEGMENTS S, BASIS_INFO BIWHERE T.TABLESPACE_NAME = S.TABLESPACE_NAME (+)GROUP BY BI.AGGREGATE_BY, T.CONTENTS, T.TABLESPACE_NAME, T.TABLESPACE_TYPE, T.ENCRYPTED, T.COMPRESSED, BI.INCLUDE_USAGE_METRICS, BI.ORDER_BYORDER BY DECODE(BI.ORDER_BY, 'SIZE', SUM(T.BYTES), 'AUTOEXT_SIZE', SUM(T.MAX_BYTES), 'FREESPACE', SUM(T.BYTES) - SUM(NVL(S.BYTES, 0)), 1) DESC, DECODE(BI.ORDER_BY, 'NAME', DECODE(BI.AGGREGATE_BY, 'TABLESPACE', T.TABLESPACE_NAME, 'CONTENT', T.CONTENTS))));
这么一大段的代码,是不是看起来很头疼呢?直接执行结果,单位就变成了B。
我们再去模版Template DB Oracle by ODBC新增监控项,至于怎么增加监控项?而且还是在discovery里边来添加,具体方法就不详细写了,自己去网络上搜索吧。
最终在last data可以看到自己想要的效果了。
标签: #oracle添加栏位