|Size details for files in Tablespace [message #306065]
||Wed, 12 March 2008 13:13
Registered: March 2008
Location: United Kingdom
Oracle 9i R2 on RHEL.
I am trying to figure out the space used and space available for files in a tablespace.
I created following 3 tables in TEST tablespace
select segment_name,bytes/1024/1024 "size(MB)" from dba_segments where tablespace_name='TEST';
(ddf.MAXBYTES/1024/1024 - ddf.BYTES/1024/1024 ) + dfs.BYTES/1024/1024 total_available_size,
ddf.BYTES/1024/1024- dfs.BYTES/1024/1024 actual_size_used
from dba_data_files ddf,
file_id total_size total_available_size actual_size_used
32 1024 399.9375 624.0625
However when I created one more table with 112 M size the above query resulted in 'No Rows Returned'
Since this time the entry with file_id 32 and tablespace TEST was missing from dba_free_space, thus failing condition
The actual size used (or consumed) at this stage was 624+112 = 736 M whereas total size of file was 1024M;
What could be the threshold for a file to have an entry in dba_free_space?
Is there an alternative to above query to get the sizes at file level?
I don't want to use dba_segments since it won't give us the details at file level.
Thanks and Regards,