DBA_TEMP_FREE_SPACE Might be Incorrect in Multitenant Environment

  • by

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 confused 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?

Leave a Reply

Your email address will not be published. Required fields are marked *