-
Incident report
-
Resolution: Unresolved
-
Trivial
-
None
-
6.0.38, 7.2.3
The discovered item Oracle '{#CON_NAME}' TBS '{#TABLESPACE}': Get tablespaces stats from Tablespace discovery in template Oracle by ODBC does not work on any tablespaces in a CDB when the PDB happens to be renamed in the past (ex. due to a clone).
This seems to be caused by a known Oracle bug as described in Wrong Results With Cdb_tablespaces After Renaming A PDB (Doc ID 2942507.1)
The problem is triggered when filtering on con$name:
Wrong results with cdb_tablespaces when filtering con$name column.
This is encountered when running a SQL like this:
select con$name, con_id
from cdb_tablespaces
where con$name='TESTPDB1';
However, also according to that document, con$name should not be used this way:
The Use case is not used as intended, the ideal way is to write the SQL as follows:
select con_id_to_con_name(con_id)
from containers(dual)
where con_id_to_con_name(con_id)='TESTPDB1';
Instead of:
select con$name, con_id
from cdb_tablespaces
where con$name='TESTPDB1';
Or
select con$name
from containers(dual)
where con$name = 'TESTPDB1';
Applying this advice to the query in the template indeed makes the template work again on a CDB where the PDB was renamed in the past:
SELECT df.CON_NAME, df.TABLESPACE_NAME AS TABLESPACE, df.CONTENTS AS TYPE, NVL(SUM(df.BYTES), 0) AS FILE_BYTES, NVL(SUM(df.MAX_BYTES), 0) AS MAX_BYTES, NVL(SUM(f.FREE), 0) AS FREE_BYTES, NVL(SUM(df.BYTES) - SUM(f.FREE), 0) AS USED_BYTES, ROUND(CASE SUM(df.MAX_BYTES) WHEN 0 THEN 0 ELSE (SUM(df.BYTES) / SUM(df.MAX_BYTES) * 100) END, 2) AS USED_PCT_MAX, ROUND(CASE SUM(df.BYTES) WHEN 0 THEN 0 ELSE (NVL(SUM(df.BYTES) - SUM(f.FREE), 0)) / SUM(df.BYTES) * 100 END, 2) AS USED_FILE_PCT, ROUND(CASE SUM(df.MAX_BYTES) WHEN 0 THEN 0 ELSE NVL(SUM(df.BYTES) - SUM(f.FREE), 0) / SUM(df.MAX_BYTES) * 100 END, 2) AS USED_FROM_MAX_PCT, CASE df.STATUS WHEN 'ONLINE' THEN 1 WHEN 'OFFLINE' THEN 2 WHEN 'READ ONLY' THEN 3 ELSE 0 END AS STATUS FROM (SELECT con_id_to_con_name(ct.CON_ID) AS CON_NAME, cdf.FILE_ID, ct.CONTENTS, ct.STATUS, cdf.FILE_NAME, cdf.TABLESPACE_NAME, TRUNC(cdf.BYTES) AS BYTES, TRUNC(GREATEST(cdf.BYTES, cdf.MAXBYTES)) AS MAX_BYTES FROM CDB_DATA_FILES cdf, CDB_TABLESPACES ct WHERE cdf.TABLESPACE_NAME = ct.TABLESPACE_NAME AND cdf.CON_ID = ct.CON_ID AND ((con_id_to_con_name(ct.CON_ID) = '{#CON_NAME}') or (con_id_to_con_name(ct.CON_ID) is null and ct.CON_ID = 0)) AND cdf.TABLESPACE_NAME = '{#TABLESPACE}' AND ct.CONTENTS = '{#CONTENTS}') df, (SELECT TRUNC(SUM(BYTES)) AS FREE, FILE_ID FROM CDB_FREE_SPACE GROUP BY FILE_ID) f WHERE df.FILE_ID = f.FILE_ID (+) GROUP BY df.CON_NAME, df.TABLESPACE_NAME, df.CONTENTS, df.STATUS UNION ALL SELECT Y.CON_NAME, Y.NAME AS TABLESPACE, Y.CONTENTS AS TYPE, NVL(SUM(Y.BYTES), 0) AS FILE_BYTES, NVL(SUM(Y.MAX_BYTES), 0) AS MAX_BYTES, NVL(MAX(NVL(Y.FREE_BYTES, 0)), 0) AS FREE_BYTES, NVL(SUM(Y.BYTES) - MAX(Y.FREE_BYTES), 0) AS USED_BYTES, ROUND(CASE SUM(Y.MAX_BYTES) WHEN 0 THEN 0 ELSE (SUM(Y.BYTES) / SUM(Y.MAX_BYTES) * 100) END, 2) AS USED_PCT_MAX, ROUND(CASE SUM(Y.BYTES) WHEN 0 THEN 0 ELSE (NVL(SUM(Y.BYTES) - MAX(Y.FREE_BYTES), 0)) / SUM(Y.BYTES) * 100 END, 2) AS USED_FILE_PCT, ROUND(CASE SUM(Y.MAX_BYTES) WHEN 0 THEN 0 ELSE (NVL(SUM(Y.BYTES) - MAX(Y.FREE_BYTES), 0)) / SUM(Y.MAX_BYTES) * 100 END, 2) AS USED_FROM_MAX_PCT, CASE Y.STATUS WHEN 'ONLINE' THEN 1 WHEN 'OFFLINE' THEN 2 WHEN 'READ ONLY' THEN 3 ELSE 0 END AS STATUS FROM (SELECT con_id_to_con_name(ct.CON_ID) AS CON_NAME, ctf.TABLESPACE_NAME AS NAME, ct.CONTENTS, ctf.STATUS AS STATUS, ctf.BYTES AS BYTES, (SELECT ((f.TOTAL_BLOCKS - s.TOT_USED_BLOCKS) * vp.VALUE) FROM (SELECT CON_ID, TABLESPACE_NAME, SUM(USED_BLOCKS) TOT_USED_BLOCKS FROM V$SORT_SEGMENT WHERE TABLESPACE_NAME != 'DUMMY' GROUP BY CON_ID, TABLESPACE_NAME) s, (SELECT CON_ID, TABLESPACE_NAME, SUM(BLOCKS) TOTAL_BLOCKS FROM CDB_TEMP_FILES WHERE TABLESPACE_NAME != 'DUMMY' GROUP BY CON_ID, TABLESPACE_NAME) f, (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size') vp WHERE f.TABLESPACE_NAME = s.TABLESPACE_NAME AND f.TABLESPACE_NAME = ctf.TABLESPACE_NAME AND f.CON_ID = s.CON_ID AND f.CON_ID = ct.CON_ID) AS FREE_BYTES, CASE WHEN ctf.MAXBYTES = 0 THEN ctf.BYTES ELSE ctf.MAXBYTES END AS MAX_BYTES FROM CDB_TEMP_FILES ctf, CDB_TABLESPACES ct WHERE ctf.TABLESPACE_NAME = ct.TABLESPACE_NAME AND ctf.CON_ID = ct.CON_ID AND ((con_id_to_con_name(ct.CON_ID) = '{#CON_NAME}') or (con_id_to_con_name(ct.CON_ID) is null and ct.CON_ID = 0)) AND ctf.TABLESPACE_NAME = '{#TABLESPACE}' AND ct.CONTENTS = '{#CONTENTS}') Y GROUP BY Y.CON_NAME, Y.NAME, Y.CONTENTS, Y.STATUS;