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