Tablespace missing in DBA_FREE_SPACE

Today i came across one of the interesting issue in my development database. Developer was loading the data and he got the ORA-01653: unable to extend table XXX.YYYYY by 128 in tablespace ZZZZ  error and data load got failed.  Immediately, he called me and reported this error.  I went and checked the DBA_FREE_SPACE and not able to find the tablespace... 

hmmm... interesting!

I found that tablespace will disappear in DBA_FREE_SPACE for the following scenario.

1. The tablespace has no free space. 
2. Tablespace is offline
3. Tablespace is temporary tablespace

Let us test the first case.  

 Create the tablespace.

SQL> create tablespace test

datafile  '/u02/oradata/test.dbf' size 10M;    2

Tablespace created.

Verify if the tablespace available in DBA_FREE_SPACE

SQL>

select tablespace_name from dba_free_space

where tablespace_name='TEST';  SQL>   2

TABLESPACE_NAME

------------------------------

TEST

Load the data  until it runs out of space

SQL>

create table gt_objects as select *

from dba_objects;

SQL>   2

Table created.

SQL>

SQL> alter table gt_objects move tablespace test;

Table altered.

SQL>

begin

for i in 1..1000000 loop

insert into gt_objects select * from dba_objects;

commit;

end  loop;

end;

/

SQL>   2    3    4    5    6    7  begin

*

ERROR at line 1:

ORA-01653: unable to extend table SYS.GT_OBJECTS by 128 in tablespace TEST

ORA-06512: at line 3

 Check if the tablespace disappeard in DBA_FREE_SPACE

SQL> SQL> select tablespace_name from dba_free_space

  2  where tablespace_name='TEST';

no rows selected

SQL>

Drop the table and see if it appears in DBA_FREE_SPACE

SQL> drop table gt_objects;

Table dropped.

SQL> select tablespace_name from dba_free_space

  2   where tablespace_name='TEST';

TABLESPACE_NAME

------------------------------

TEST

SQL>

Drop the tablespace

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL>

Conclusion :  The minimum space required for the tablespace to be reported in the DBA_FREE_SPACE is that of the extent size of the tablespace. We have to be careful if we use DBA_FREE_SPACE for tablespace monitoring query.