tablespace size

[axe]

In our production DB, several tablespaces have the bytes column as null in dba_data_files. So, to find out the size of a tablespace, you would select sum(bytes) from dba_data_files where tablespace_name='xyz'. The result is a surprising null!

Anyone seen this behavior before and any suggestions on what is causing it?

[pando]

plz run

select file#, bytes, status from v$datafile
order by 1

and

select file_id, bytes, status from dba_data_files
order by 1

and paste here

[axe]

RESULTS:

v$datafile
bytes=0
status=OFFLINE

dba_data_file
bytes=null
status=AVAILABLE


note: the actual file was created as a 2GB DB file and still is this size at OS level; also, we did offline this tablespace recently.

[pando]

you put the datafile offline or the tablespace offline?

try put it online, I am pretty sure you will get errors

[axe]

Found out from Oracle Support that this is the expected behavior for locally-managed tablespaces. When the tablespace is put back online the size appears again in dba_data_files.