| Size details for files in Tablespace [message #306065] |
Wed, 12 March 2008 13:13  |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hi,
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';
segment_name size(MB)
Tab1 400
Tab2 112
Tab3 112
select ddf.file_id,
ddf.MAXBYTES/1024/1024 total_size,
(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,
dba_free_space dfs
where ddf.tablespace_name='TEST'
and ddf.tablespace_name=dfs.tablespace_name
and ddf.file_id=dfs.file_id;
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
and ddf.tablespace_name=dfs.tablespace_name.
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,
OraKaran
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
