Wednesday, September 30, 2015

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.