How to check free space in tablespaces
February 8th, 2006 By Karl Reitschuster
The following query shows tablespace usage and lists tablspace names including the database name:
select d.status,
db.name dbname,
d.tablespace_name tsname,
d.extent_management,
d.allocation_type,
to_char(nvl(d.min_extlen / 1024, 0),
'99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
"ALLOC_SIZE (K)",
d.contents "Type",
case
when(d.contents = 'TEMPORARY') then
to_char(nvl(a.bytes / 1024 / 1024, 0),
'99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
else
to_char(nvl(t.bytes / 1024 / 1024, 0),
'99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
end as "Size (M)",
to_char(nvl((a.bytes - nvl(f.bytes, 0)) / 1024 / 1024, 0),
'99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
"Used (M)",
to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),
'990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
"Used (%)"
from sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name)
a,
(select tablespace_name,
sum(bytes) bytes
from dba_temp_files
group by tablespace_name)
t,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name)
f,
v$database db
where d.tablespace_name = a.tablespace_name(+)
and d.tablespace_name = f.tablespace_name(+)
and d.tablespace_name = t.tablespace_name(+)
order by 10 desc;
HTH
Karl
February 10th, 2006 at 1:20 pm
Hello Karl,
everyone seems to have his scripts.
I sometimes use http://asktom.oracle.com/~tkyte/Misc/free.html for freespace listing.
BR,
Martin
February 11th, 2006 at 10:58 am
Hi Martin
I captured the script from OEM Enterprise Manager :-)
but next week is want to introduce a very usefull script of finding not indexed foreign keys – and this script is by Tom Kyte too!
Greetings
Karl
November 10th, 2010 at 4:40 am
Hello Karl,
I think the query needs to be slightly modified as below.
case
when(d.contents = ‘TEMPORARY’) then
to_char(nvl(t.bytes / 1024 / 1024, 0),
’99G999G990D90′, ‘NLS_NUMERIC_CHARACTERS = ”,.” ‘)
else
to_char(nvl(a.bytes / 1024 / 1024, 0),
’99G999G990D90′, ‘NLS_NUMERIC_CHARACTERS = ”,.” ‘)
end as “Size (M)”,
** I believe the columns t.bytes and a.bytes were reversed in your query.
- Chanjal