Printable View
I inquired dba_free_space at one of our customer sites but didn't find free space information about three datafiles out of 54 listed in dba_data_files. May I know what could be the possible reason for it? Could it be that there is no free space in these datafiles so they are not listed in dba_free_space?
evidence?
Are missing datafiles related to a Temporary tablespace?
dba_free_space only reports Permanent tablespace free space.
As PAVB said, if they are not temporary tablespace data files, then for sure there is no free space left in those data files.
However, if any tablespace has multiple data files then few of the data files occupied 100%. Better user group by tablespace_name if your intention is to check the free space in any tablespace.
Thanks,
VNKTUMMALA, I also think the same. There is tablespace named DMS_BLOB that has 9 datafiles and all of them are nearly fully packed up. These are datafiles of permanent tablespace.
Just for evidence to Dave:
SQL> select file_id,tablespace_name,file_name,bytes/1024/1024/1024 from dba_data_files
2 where file_id not in (select file_id from dba_free_space)
3 /
FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024/1024
---------- ------------------------------ ---------------------------------------- --------------------
2 QUARKDMS_BLOB F:\DATABASE\QDMS\DMB4QDMS.ORA 3.90710449
39 QUARKDMS_BLOB F:\DATABASE\QDMS\DMB5QDMS.ORA 3.90637207
37 SYSTEM F:\DATABASE\QDMS\SYS2QDMS.ORA .195312