OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

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

3 Responses to “How to check free space in tablespaces”

  1. austrin Says:

    Hello Karl,
    everyone seems to have his scripts.
    I sometimes use http://asktom.oracle.com/~tkyte/Misc/free.html for freespace listing.
    BR,
    Martin

  2. Karl Reitschuster Says:

    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

  3. chanjaljayaram Says:

    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

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question