Re: How to calculate free space left in tablespace

From: Tony Adolph <tony.adolph_at_viaginterkom.de>
Date: Thu, 19 Aug 1999 14:06:00 GMT
Message-ID: <7ph2vt$sn9$1_at_nnrp1.deja.com>

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

group by tablespace_name
/

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

from

   fs,df
where

   fs.tablespace_name = df.tablespace_name
/

---

Then to get a pretty output create a scripts called fs.sql as follows:

---

compute sum of total_MB on report
compute sum of used_MB on report
compute sum of free_MB on report
break on report

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
/

---

Then as SYSTEM, type _at_fs

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 - 16:06:00 CEST