DBA_FREE_SPACE - Misunderstanding how Oracle populates it

One of my colleagues asked why some tablespaces are missing from DBA_FREE_SPACE.

You have to be sure that, below cases is not true for your tablespace, if it does not appear in DBA_FREE_SPACE:

1) The tablespace is a temporary tablespace.
2) The tablespace is offline.
3) The tablespace contains no free space. The minimum space required for the tablespace to be reported in the DBA_FREE_SPACE is that of the extent size of the tablespace.


Let us see example:


Case 1:

SQL> create temporary tablespace TEMP1 tempfile '/oracle/product/oradata/db1/temp1_1.dbf' size 5m;

Tablespace created.

SQL> select distinct tablespace_name from dba_free_space;

TABLESPACE_NAME
------------------------------
SYSAUX
UNDOTBS1
USERS
SYSTEM

Case 2:

SQL> create tablespace myts1 datafile '/oracle/product/oradata/db1/myts1_1.dbf' size 512k extent management local;

Tablespace created.

SQL> select distinct tablespace_name from dba_free_space;

TABLESPACE_NAME
------------------------------
SYSAUX
UNDOTBS1
USERS
SYSTEM
MYTS1

SQL> alter tablespace myts1 offline normal;

Tablespace altered.

SQL> select distinct tablespace_name from dba_free_space;

TABLESPACE_NAME
------------------------------
SYSAUX
UNDOTBS1
USERS
SYSTEM

SQL>  alter tablespace myts1 online;

Tablespace altered.

SQL> select distinct tablespace_name from dba_free_space;

TABLESPACE_NAME
------------------------------
SYSAUX
UNDOTBS1
USERS
SYSTEM
MYTS1

Case 3:

SQL> create table myt1 (x int) storage(initial 256k next 256k) tablespace myts1;

Table created.

SQL> select extent_id, bytes, blocks from dba_extents where segment_name = 'MYT1';

EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
0      65536          8
1      65536          8
2      65536          8
3      65536          8

SQL> alter table myt1 allocate extent;

Table altered.

SQL> /

Table altered.

SQL> /

Table altered.

SQL> /
alter table myt1 allocate extent
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.MYT1 by 8 in tablespace MYTS1

Now, there is no free space in datafile.

SQL> select distinct tablespace_name from dba_free_space;

TABLESPACE_NAME
------------------------------
SYSAUX
UNDOTBS1
USERS
SYSTEM

Now, adding space to datafile and tablespace will be reported in DBA_FREE_SPACE:

SQL> alter database datafile '/oracle/product/oradata/db1/myts1_1.dbf' resize 1024k;

Database altered.

SQL> select distinct tablespace_name from dba_free_space;

TABLESPACE_NAME
------------------------------
SYSAUX
UNDOTBS1
USERS
SYSTEM
MYTS1