DBA_TEMP_FREE_SPACE Shows Wrong Information on 19c

 

Recently we have seen on 19c  that DBA_TEMP_FREE_SPACE Shows Wrong Information on 19c . Its calculating more space then actual space . 

Thanks to Oracle  (Doc ID 2633068.1)  that   explains this  Gap .  Issue is related with duplicate entries in gv$temp_space_header

We  need to use dba_temp_files instead 

++ Issue is happening on 18c and 19c multitenant environment.

++ 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. 

++ 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

The issue is fixed in future release 20.1