Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to calculate free space left in tablespace
Hello,
Here's a bit of SQL that gives you a freespace status of all of your tablespaces. It also tells you the size of the largest free extent in each tablespace. This can be useful, sometimes you get an error that there's not enough space, but when you look there seems to be plenty. Its because the system wanted a "NEXT EXTENT" that was larger than the largest free extent in the tablespace. Unlike some of the "cleaver" scripts I have seen that do the same thing (or similar), it deals with mutiple datafiles per tablespace. Not rocket science, but works. Run the following as SYSTEM, or a user with select privilidge on the dba_ tables:
create or replace view fs as
select
tablespace_name, sum(bytes) free, max(bytes) max_extent from dba_free_space group by tablespace_name
create or replace view df as
select
tablespace_name, sum(bytes) total from dba_data_files
create or replace view freespace as
select
fs.tablespace_name, total/1024/1024 Total_MB, (total - free)/1024/1024 Used_MB, ((total - free)/ total) * 100 pct_used, free/1024/1024 free_MB, (free / total ) * 100 pct_free, max_extent/1024/1024 max_extent_size
fs,df
where
fs.tablespace_name = df.tablespace_name
/
Then to get a pretty output create a scripts called fs.sql as follows:
column tablespace_name format a12 heading "TABLESPACE"
column pct_free format 999.99
column pct_used format 999.99
column used_MB format 999,999
column free_MB format 999,999
column max_extent_size format 999.99
select TABLESPACE_NAME, TOTAL_MB, USED_MB, FREE_MB, MAX_EXTENT_SIZE
from freespace
/
Hope this helps.
Regards
Tony Adolph
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Thu Aug 19 1999 - 09:06:00 CDT