1/* 2** Zabbix 3** Copyright (C) 2001-2021 Zabbix SIA 4** 5** This program is free software; you can redistribute it and/or modify 6** it under the terms of the GNU General Public License as published by 7** the Free Software Foundation; either version 2 of the License, or 8** (at your option) any later version. 9** 10** This program is distributed in the hope that it will be useful, 11** but WITHOUT ANY WARRANTY; without even the implied warranty of 12** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 13** GNU General Public License for more details. 14** 15** You should have received a copy of the GNU General Public License 16** along with this program; if not, write to the Free Software 17** Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. 18**/ 19 20package oracle 21 22import ( 23 "context" 24 "strings" 25 26 "zabbix.com/pkg/zbxerr" 27) 28 29func tablespacesHandler(ctx context.Context, conn OraClient, params map[string]string, 30 _ ...string) (interface{}, error) { 31 var tablespaces string 32 33 row, err := conn.QueryRow(ctx, ` 34 SELECT 35 JSON_ARRAYAGG( 36 JSON_OBJECT(TABLESPACE_NAME VALUE 37 JSON_OBJECT( 38 'contents' VALUE CONTENTS, 39 'file_bytes' VALUE FILE_BYTES, 40 'max_bytes' VALUE MAX_BYTES, 41 'free_bytes' VALUE FREE_BYTES, 42 'used_bytes' VALUE USED_BYTES, 43 'used_pct_max' VALUE USED_PCT_MAX, 44 'used_file_pct' VALUE USED_FILE_PCT, 45 'status' VALUE STATUS 46 ) 47 ) RETURNING CLOB 48 ) 49 FROM 50 ( 51 SELECT 52 df.TABLESPACE_NAME AS TABLESPACE_NAME, 53 df.CONTENTS AS CONTENTS, 54 NVL(SUM(df.BYTES), 0) AS FILE_BYTES, 55 NVL(SUM(df.MAX_BYTES), 0) AS MAX_BYTES, 56 NVL(SUM(f.FREE), 0) AS FREE_BYTES, 57 SUM(df.BYTES)-SUM(f.FREE) AS USED_BYTES, 58 ROUND(DECODE(SUM(df.MAX_BYTES), 0, 0, (SUM(df.BYTES) / SUM(df.MAX_BYTES) * 100)), 2) AS USED_PCT_MAX, 59 ROUND(DECODE(SUM(df.BYTES), 0, 0, (SUM(df.BYTES)-SUM(f.FREE)) / SUM(df.BYTES)* 100), 2) AS USED_FILE_PCT, 60 DECODE(df.STATUS, 'ONLINE', 1, 'OFFLINE', 2, 'READ ONLY', 3, 0) AS STATUS 61 FROM 62 ( 63 SELECT 64 ddf.FILE_ID, 65 dt.CONTENTS, 66 dt.STATUS, 67 ddf.FILE_NAME, 68 ddf.TABLESPACE_NAME, 69 TRUNC(ddf.BYTES) AS BYTES, 70 TRUNC(GREATEST(ddf.BYTES, ddf.MAXBYTES)) AS MAX_BYTES 71 FROM 72 DBA_DATA_FILES ddf, 73 DBA_TABLESPACES dt 74 WHERE 75 ddf.TABLESPACE_NAME = dt.TABLESPACE_NAME 76 ) df, 77 ( 78 SELECT 79 TRUNC(SUM(BYTES)) AS FREE, 80 FILE_ID 81 FROM 82 DBA_FREE_SPACE 83 GROUP BY 84 FILE_ID 85 ) f 86 WHERE 87 df.FILE_ID = f.FILE_ID (+) 88 GROUP BY 89 df.TABLESPACE_NAME, df.CONTENTS, df.STATUS 90 UNION ALL 91 SELECT 92 Y.NAME AS TABLESPACE_NAME, 93 Y.CONTENTS AS CONTENTS, 94 NVL(SUM(Y.BYTES), 0) AS FILE_BYTES, 95 NVL(SUM(Y.MAX_BYTES), 0) AS MAX_BYTES, 96 NVL(MAX(NVL(Y.FREE_BYTES, 0)), 0) AS FREE, 97 SUM(Y.BYTES)-SUM(Y.FREE_BYTES) AS USED_BYTES, 98 ROUND(DECODE(SUM(Y.MAX_BYTES), 0, 0, (SUM(Y.BYTES) / SUM(Y.MAX_BYTES) * 100)), 2) AS USED_PCT_MAX, 99 ROUND(DECODE(SUM(Y.BYTES), 0, 0, (SUM(Y.BYTES)-SUM(Y.FREE_BYTES)) / SUM(Y.BYTES)* 100), 2) AS USED_FILE_PCT, 100 DECODE(Y.TBS_STATUS, 'ONLINE', 1, 'OFFLINE', 2, 'READ ONLY', 3, 0) AS STATUS 101 FROM 102 ( 103 SELECT 104 dtf.TABLESPACE_NAME AS NAME, 105 dt.CONTENTS, 106 dt.STATUS AS TBS_STATUS, 107 dtf.STATUS AS STATUS, 108 dtf.BYTES AS BYTES, 109 ( 110 SELECT 111 ((f.TOTAL_BLOCKS - s.TOT_USED_BLOCKS) * vp.VALUE) 112 FROM 113 ( 114 SELECT 115 TABLESPACE_NAME, SUM(USED_BLOCKS) TOT_USED_BLOCKS 116 FROM 117 GV$SORT_SEGMENT 118 WHERE 119 TABLESPACE_NAME != 'DUMMY' 120 GROUP BY 121 TABLESPACE_NAME) s, ( 122 SELECT 123 TABLESPACE_NAME, SUM(BLOCKS) TOTAL_BLOCKS 124 FROM 125 DBA_TEMP_FILES 126 WHERE 127 TABLESPACE_NAME != 'DUMMY' 128 GROUP BY 129 TABLESPACE_NAME) f, ( 130 SELECT 131 VALUE 132 FROM 133 V$PARAMETER 134 WHERE 135 NAME = 'db_block_size') vp 136 WHERE 137 f.TABLESPACE_NAME = s.TABLESPACE_NAME 138 AND f.TABLESPACE_NAME = dtf.TABLESPACE_NAME 139 ) AS FREE_BYTES, 140 CASE 141 WHEN dtf.MAXBYTES = 0 THEN dtf.BYTES 142 ELSE dtf.MAXBYTES 143 END AS MAX_BYTES 144 FROM 145 sys.DBA_TEMP_FILES dtf, 146 sys.DBA_TABLESPACES dt 147 WHERE 148 dtf.TABLESPACE_NAME = dt.TABLESPACE_NAME ) Y 149 GROUP BY 150 Y.NAME, Y.CONTENTS, Y.TBS_STATUS 151 ) 152 `) 153 if err != nil { 154 return nil, zbxerr.ErrorCannotFetchData.Wrap(err) 155 } 156 157 err = row.Scan(&tablespaces) 158 if err != nil { 159 return nil, zbxerr.ErrorCannotFetchData.Wrap(err) 160 } 161 162 // Add leading zeros for floats: ".03" -> "0.03". 163 // Oracle JSON functions are not RFC 4627 compliant. 164 // There should be a better way to do that, but I haven't come up with it ¯\_(ツ)_/¯ 165 tablespaces = strings.ReplaceAll(tablespaces, "\":.", "\":0.") 166 167 return tablespaces, nil 168} 169