I consistently get the wrong figures about free space of temporary tablespace in database tools, so I made a research.
First of all, it's a multitenant database.
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
ORCLPDB
And it's a 19c RAC database.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> select description from dba_registry_sqlpatch;
DESCRIPTION
--------------------------------------------------------------------------------
Database Release Update : 19.3.0.0.190416 (29517242)
DBA_TEMP_FILES
By the traditional way, we can check the size of temp files by querying DBA_TEMP_FILES.
SQL> column gb format 9999.99;
SQL> select sum(bytes)/1024/1024/1024 gb from dba_temp_files where tablespace_name = 'TEMP';
GB
--------
464.20
It's correct for 16 temp files which are all nearly full. But when we checked DBA_TEMP_FREE_SPACE, we got very confusing information:
DBA_TEMP_FREE_SPACE
SQL> select sum(allocated_space)/1024/1024/1024 gb from dba_temp_free_space where tablespace_name = 'TEMP';
GB
--------
723.74
I don't get it, for a small-file tablespace based on 8KB blocks, the max size of a file is 32GB, so the max size of this tablespace containing 16 files is no more than 512GB. Where did 723.74GB come from? Is it a bug?
Yes it’s a a bug 30409201 desribed in DBA_TEMP_FREE_SPACE Shows Wrong Information on 18c (Doc ID 2633068.1) fixed in 20.1, not backportable.
Thanks for your feedback!