在Zabbix下监控Oracle数据库的表空间使用情况，多租用户环境下，模板Oracle by ODBC中的SQL只能查询当前PDB的表空间使用情况，我想监控多租用户环境下所有PDB数据库的表空间使用情况，所以想用下面SQL来替换
set linesize 860 pages 100
col con_id for 9999
col con_name for a8
col tb_name for a12
col tb_size for 99,999.99 head "TB_SIZE|GB"
col tb_used_size for 99,999.99 head "TB_USED_SIZE|GB"
col tb_free_size for 99,999.99 head "TB_FREE_SIZE|GB"
col tb_extensible_size for 99,999.99 head "TB_EXTE_SIZE|GB"
col tb_used_rate for 99.99 head "TB_USED|_RATE%"
col tb_free_rate for 99.99 head "TB_FREE|_RATE%"
col tb_max_size for 999,999.99 head "TB_MAX|_SIZE|GB"
col max_used_rate for 999.99 head "MAX_USED_RATE|%"
col max_free_rate for 999.99 head "MAX_FREE_RATE|%"
BREAK ON CON_ID ON con_name
--
with cfs as
(
select cfs.con_id
, cfs.tablespace_name
, round(sum(cfs.bytes)/1024/1024/1024,3) as tb_free_size
from cdb_free_space cfs
group by cfs.con_id, cfs.tablespace_name
)
,cdf as
(
select cdf.con_id
, cdf.tablespace_name
, round(sum(cdf.bytes)/1024/1024/1024,3) tb_size
, round(sum(decode(cdf.maxbytes,0,cdf.bytes, cdf.maxbytes))/1024/1024/1024,2) as max_size
from cdb_data_files cdf
group by cdf.con_id,cdf.tablespace_name
)
,ctf as
(
select ctf.con_id
, ctf.tablespace_name
, round(sum(ctf.bytes)/1024/1024/1024,3) tb_size
, round(sum(decode(ctf.maxbytes,0, ctf.bytes, ctf.maxbytes))/1024/1024/1024,3) as max_size
from cdb_temp_files ctf
group by ctf.con_id,ctf.tablespace_name
)
,ctfs as
(
select ctfs.con_id
, ctfs.tablespace_name
, round(sum(ctfs.free_space)/1024/1024/1024,3) as tb_free_size
, round(sum(ctfs.tablespace_size)/1024/1024/1024,3) as tablespace_size
, round(sum(ctfs.allocated_space)/1024/1024/1024,3) as allocated_space
from cdb_temp_free_space ctfs
group by ctfs.con_id, ctfs.tablespace_name
)
select cdf.con_id as con_id
, c.name as con_name
, cdf.tablespace_name as tb_name
, cdf.tb_size as tb_size
, (cdf.tb_size - cfs.tb_free_size) as tb_used_size
, cfs.tb_free_size as tb_free_size
, (cdf.tb_size - cfs.tb_free_size)/cdf.tb_size*100 as tb_used_rate
, cfs.tb_free_size/cdf.tb_size*100 as tb_free_rate
, (cdf.max_size - cdf.tb_size ) as tb_extensible_size
, case
when cdf.max_size = 0 then 'No' else 'Yes'
end as autoextfile
, cdf.max_size as tb_max_size
, (cdf.tb_size - cfs.tb_free_size)/cdf.max_size*100
as max_used_rate
, (cdf.max_size - cdf.tb_size + cfs.tb_free_size)/cdf.max_size*100
as max_free_rate
from cfs, cdf, v$containers c
where cfs.con_id = cdf.con_id
and cfs.tablespace_name = cdf.tablespace_name
and c.con_id = cdf.con_id
union
select ctf.con_id as con_id
, c.name as con_name
, ctf.tablespace_name as tb_name
, ctfs.tablespace_size as tb_size
, (ctfs.tablespace_size - ctfs.tb_free_size) as tb_used_size
, ctfs.tb_free_size as tb_free_size
, (ctfs.tablespace_size - ctfs.tb_free_size)/ctfs.tablespace_size*100
as tb_used_rate
, ctfs.tb_free_size/ ctfs.tablespace_size*100 as tb_free_rate
, (ctf.max_size - ctfs.tablespace_size) as extensible_size
, case
when ctf.max_size = 0 then 'No' else 'Yes'
end as autoextfile
, ctf.max_size as tb_max_size
, round((ctfs.tablespace_size - ctfs.tb_free_size)/ctf.max_size*100,2)
as max_used_rate
, round((ctf.max_size -ctfs.tablespace_size+ctfs.tb_free_size)/ctf.max_size*100,2)
as max_free_rate
from ctf ,ctfs ,v$containers c
where ctf.con_id = ctfs.con_id
and ctf.con_id = c.con_id
order by con_id, con_name,max_free_rate;
但是测试过程中，居然发现Oracle 19c实例下TEMP表空间的有些指标出现负数情况，检查对比后发现cdb_temp_free_space统计TEMP表空间的数据不准确。如下所示
SQL> set linesize 1080;
SQL> col tablespace_name for a16;
SQL> col tb_free_size for 99999.99
SQL> select ctfs.con_id
2 , ctfs.tablespace_name
3 , round(sum(ctfs.free_space)/1024/1024/1024,3) as tb_free_size
4 , round(sum(ctfs.tablespace_size)/1024/1024/1024,3) as tablespace_size
5 , round(sum(ctfs.allocated_space)/1024/1024/1024,3) as allocated_space
6 from cdb_temp_free_space ctfs
7 group by ctfs.con_id, ctfs.tablespace_name;
TB_FREE_SIZE
CON_ID TABLESPACE_NAME GB TABLESPACE_SIZE ALLOCATED_SPACE
------ ---------------- ------------ --------------- ---------------
1 TEMP .05 .27 .27
3 TEMP 31.99 96 96
SQL>
CON_ID=3的PDB的数据库的表空间TEMP大小为96G，但是用下面SQL检查发现TEMP表空间的实际大小为32G
SQL> set pagesize 1000 linesize 480
SQL> tti 'Tablespace Usage Status'
SQL> col "TBS_NAME" for a16
SQL> col "TYPE" for a9
SQL> col "TOTAL(GB)" for 999,999.99
SQL> col "USAGE(GB)" for 999,999.99
SQL> col "FREE(GB)" for 999,999.99
SQL> col "EXTENSIBLE(GB)" for 999,999.99
SQL> col "MAX_SIZE(GB)" for 999,999.99
SQL> col "FREE PCT %" for 999.99
SQL> col "USED PCT OF MAX %" for 999.99
SQL> col "NO_AXF_NUM" for 9999
SQL> col "AXF_NUM" for 999
SQL> select d.tablespace_name "TBS_NAME"
2 ,d.contents "TYPE"
3 ,nvl(a.bytes /1024/1024/1024,0) "TOTAL(GB)"
4 ,nvl(t.bytes,0)/1024/1024/1024 "USAGE(GB)"
5 ,nvl(a.bytes - nvl(t.bytes,0),0)/1024/1024/1024 "FREE(GB)"
6 ,nvl(t.bytes/a.bytes * 100,0) "FREE PCT %"
7 ,nvl(a.ARTACAK,0)/1024/1024/1024 "EXTENSIBLE(GB)"
8 ,nvl(a.MAX_BYTES,0)/1024/1024/1024 "MAX_SIZE(GB)"
9 ,nvl(t.bytes/(a.bytes + nvl(a.ARTACAK,0)) * 100,0) "USED PCT OF MAX %"
10 ,a.NO_AXF_NUM
11 ,a.AXF_NUM
12 from sys.dba_tablespaces d,
13 (select tablespace_name
14 ,sum(bytes) bytes
15 ,sum(decode(autoextensible,'YES',MAXbytes - bytes,0 )) ARTACAK
16 ,count(decode(autoextensible,'NO',0)) NO_AXF_NUM
17 ,count(decode(autoextensible,'YES',0)) AXF_NUM
18 ,sum(decode(maxbytes, 0, BYTES, maxbytes)) MAX_BYTES
19 from dba_temp_files
20 group by tablespace_name
21 ) a,
22 (select tablespace_name
23 , sum(bytes_used) bytes
24 from v$temp_extent_pool
25 group by tablespace_name
26 ) t
27 where d.tablespace_name = a.tablespace_name(+)
28 and d.tablespace_name = t.tablespace_name(+)
29 and d.extent_management like 'LOCAL'
30 and d.contents like 'TEMPORARY%'
31 order by 3 desc;
Wed Dec 07 page 1
Tablespace Usage Status
TBS_NAME TYPE TOTAL(GB) USAGE(GB) FREE(GB) FREE PCT % EXTENSIBLE(GB) MAX_SIZE(GB) USED PCT OF MAX % NO_AXF_NUM AXF_NUM
---------------- --------- ----------- ----------- ----------- ---------- -------------- ------------ ----------------- ---------- -------
TEMP TEMPORARY 32.00 .00 32.00 .00 .00 32.00 .00 0 6
SQL>
既然视图cdb_temp_free_space的数据有问题，那么我们要分析看看视图的数据来源于那些视图或基表，最后发现cdb_temp_free_space这个视图的数据来源于"SYS"."DBA_TEMP_FREE_SPACE"
set linesize 1080;
col owner for a14;
col object_name for a30
select owner,object_name,object_type from dba_objects where object_name=upper('cdb_temp_free_space');
SQL> set linesize 1080;
SQL> col owner for a14;
SQL> col object_name for a30;
SQL> select owner,object_name,object_type from dba_objects where object_name=upper('cdb_temp_free_space');
OWNER OBJECT_NAME OBJECT_TYPE
-------------- ------------------------------ ---------------------------------------------------------------------
SYS CDB_TEMP_FREE_SPACE VIEW
PUBLIC CDB_TEMP_FREE_SPACE SYNONYM
SQL>
系统视图cdb_temp_free_space的具体定义如所示
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."CDB_TEMP_FREE_SPACE" CONTAINER_DATA (
"TABLESPACE_NAME",
"TABLESPACE_SIZE",
"ALLOCATED_SPACE",
"FREE_SPACE",
"SHARED",
"INST_ID",
"CON_ID",
"CON$NAME",
"CDB$NAME",
"CON$ERRNUM",
"CON$ERRMSG"
)
AS
SELECT
k."TABLESPACE_NAME",
k."TABLESPACE_SIZE",
k."ALLOCATED_SPACE",
k."FREE_SPACE",
k."SHARED",
k."INST_ID",
k."CON_ID",
k.con$name,
k.cdb$name,
k.con$errnum,
k.con$errmsg
FROM
CONTAINERS ( "SYS"."DBA_TEMP_FREE_SPACE" ) k
验证发现DBA_TEMP_FREE_SPACE的数据确实有问题，
SQL> SET LINESIZE 680;
SQL> COL TABLESPACE_NAME FOR A16;
SQL> COL SHARED FOR A10;
SQL> SELECT TABLESPACE_NAME,
2 ROUND(TABLESPACE_SIZE/1024/1024/1024,2) AS TABLESPACE_SIZE,
3 ROUND(ALLOCATED_SPACE/1024/1024/1024,2) AS ALLOCATED_SPACE,
4 ROUND(FREE_SPACE/1024/1024/1024,2) AS FREE_SPACE,
5 SHARED,INST_ID
6 FROM DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE SHARED INST_ID
---------------- --------------- --------------- ---------- ---------- ----------
TEMP 96 96 31.99 SHARED
SQL>
而DBA_TEMP_FREE_SPACE的数据部分来自gv$temp_space_header
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."DBA_TEMP_FREE_SPACE" ("TABLESPACE_NAME", "TABLESPACE_SIZE", "ALLOCATED_SPACE", "FREE_SPACE", "SHARED", "INST_ID") AS
SELECT tsh.tablespace_name,
tsh.total_bytes/tsh.inst_count,
tsh.bytes_used/tsh.inst_count,
(tsh.bytes_free/tsh.inst_count) + (nvl(ss.free_blocks, 0) * ts$.blocksize),
decode(bitand(ts$.flags, 18155135997837312),
140737488355328, 'LOCAL_ON_LEAF',
18155135997837312, 'LOCAL_ON_ALL', 'SHARED') shared,
null as inst_id
FROM (SELECT tablespace_name, sum(bytes_used + bytes_free) total_bytes,
sum(bytes_used) bytes_used, sum(bytes_free) bytes_free,
count(distinct inst_id) inst_count
FROM gv$temp_space_header
where (con_id is NULL or con_id = sys_context('USERENV', 'CON_ID'))
GROUP BY tablespace_name) tsh,
(SELECT tablespace_name, sum(free_blocks) free_blocks
FROM gv$sort_segment
where (con_id is NULL or con_id = sys_context('USERENV', 'CON_ID'))
GROUP BY tablespace_name) ss,
ts$
WHERE ts$.name = tsh.tablespace_name and
decode(bitand(ts$.flags, 18155135997837312),
140737488355328, 1,
18155135997837312, 1, 0) = 0 and
tsh.tablespace_name = ss.tablespace_name (+)
UNION
SELECT tsh.tablespace_name,
tsh.total_bytes,
tsh.bytes_used,
(tsh.bytes_free) + (nvl(ss.free_blocks, 0) * ts$.blocksize),
decode(bitand(ts$.flags, 18155135997837312),
140737488355328, 'LOCAL_ON_LEAF',
18155135997837312, 'LOCAL_ON_ALL', 'SHARED') shared,
tsh.inst_id
FROM (SELECT tablespace_name, inst_id, sum(bytes_used + bytes_free) total_bytes,
sum(bytes_used) bytes_used, sum(bytes_free) bytes_free
FROM gv$temp_space_header
WHERE (con_id is NULL or con_id = sys_context('USERENV', 'CON_ID'))
GROUP BY tablespace_name, inst_id) tsh,
(SELECT tablespace_name, inst_id, sum(free_blocks) free_blocks
FROM gv$sort_segment
WHERE (con_id is NULL or con_id = sys_context('USERENV', 'CON_ID'))
GROUP BY tablespace_name, inst_id) ss,
ts$
WHERE ts$.name = tsh.tablespace_name and
decode(bitand(ts$.flags, 18155135997837312),
140737488355328, 1,
18155135997837312, 1, 0) = 1 and
tsh.tablespace_name = ss.tablespace_name (+)
然后我搜索了一下metalink，发现这个是一个Bug来着，Oracle 18c & Oracle 19c都受影响，引起数据异常是Bug 30576120 - GV$TEMP_SPACE_HEADER SHOWING DUPLICATE ENTRIES FOR SINGLE TEMPFILE导致的，它输出临时表空间数据文件时出现了重复的记录，具体参考DBA_TEMP_FREE_SPACE Shows Wrong Information on 18c (Doc ID 2633068.1)[1]，如下所示：
In this Document
Symptoms
Changes
Cause
Solution
References
APPLIES TO:
Oracle Database - Enterprise Edition - Version 18.5.0.0.0 and later
Information in this document applies to any platform.
SYMPTOMS
++ dba_temp_free_space showing wrong information for temp tablespace in 18c and 19c multitenant environment.
++ Issue does not happen in 18c/19c non-multitenant environment.
++ After upgrade to 18c the Temporary tablespace continues to grow.
Before upgrade temp tablespace was < 10GB.
Has now grown to 35+ GB.
select (TABLESPACE_SIZE/1048576)/1024 ,(ALLOCATED_SPACE/1048576)/1024,(FREE_SPACE/1048576)/1024 from dba_temp_free_space;
(TABLESPACE_SIZE/1048576)/1024 (ALLOCATED_SPACE/1048576)/1024 (FREE_SPACE/1048576)/1024
------------------------------ ------------------------------ -------------------------
35.9972076 35.9972076 10.9228516
CHANGES
Upgrade to 18c or 19c
New database creation in 18c or 19c
CAUSE
++ Issue is happening on 18c and 19c multitenant environment.
++ Issue is related with duplicate entries in gv$temp_space_header
++ For a single tempfile in TEMP Tablespace, gv$temp_space_header is showing 3 rows in 18c and 19c. Whereas shows correctly in 12.2.0.1 as 1 row.
SQL> select TABLESPACE_NAME,(bytes_used+bytes_free),BYTES_USED,BYTES_FREE from gv$temp_space_header;
TABLESPACE_NAME (BYTES_USED+BYTES_FREE) BYTES_USED BYTES_FREE
------------------------------ ----------------------- ---------- ----------
TEMP 1.2884E+10 1.2884E+10 0
TEMP 1.2884E+10 1.2884E+10 0
TEMP 1.2884E+10 1.2884E+10 0
This issue is caused by below bug
Bug 30576120 - GV$TEMP_SPACE_HEADER SHOWING DUPLICATE ENTRIES FOR SINGLE TEMPFILE
which is closed as duplicate of unpublished bug
Bug 29751003 - RTI 21970752 CAUSED BY TRANSACTION ODESANTI_BUG-18339865
Bug:29751003 has been superseded by the fix in Bug:29956639,
Bug:29956639 has been superseded by the fix in Bug:30409201 which is not backportable.
SOLUTION
The issue is fixed in future release 20.1
REFERENCES
BUG:30576120 - GV$TEMP_SPACE_HEADER SHOWING DUPLICATE ENTRIES FOR SINGLE TEMPFILE
NOTE:166650.1 - Working Effectively With Oracle Support - Best Practices
NOTE:29751003.8 - Bug 29751003 - Wrong results returned from V$TEMPORARY_LOBS in multitenant DB
NOTE:29956639.8 - Bug 29956639 - Regression from bug 29751003
参考资料
Doc ID 2633068.1: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=289537428803067&id=2633068.1&_afrWindowMode=0&_adf.ctrl-state=3ew3bgmnm_129
文章转载自DBA闲思杂想录，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
