Home » RDBMS Server » Server Administration » Size details for files in Tablespace (Oracle 9i R2 on RHEL)
Size details for files in Tablespace [message #306065] Wed, 12 March 2008 13:13 Go to next message
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member

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,
Re: Size details for files in Tablespace [message #306067 is a reply to message #306065] Wed, 12 March 2008 13:18 Go to previous message
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Apologies !!
forgot to mention create tablespace script

create tablespace test datafile '/u05/oradata/DB01/test01.dbf' size 500M autoextend on maxsize 1024M;

Previous Topic: granting a user rights to another users account
Next Topic: Removing Row chaining
Goto Forum:

Current Time: Fri Aug 18 21:00:10 CDT 2017

Total time taken to generate the page: 0.45079 seconds