Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, mustafa.

Asked: December 12, 2018 - 5:36 pm UTC

Last updated: December 13, 2018 - 6:21 am UTC

Version: 11G R2

Viewed 1000+ times

You Asked

how can List all the tablespaces in the ORCL database by displaying the available space, used space, status and type. Also permanent and temporary files related to those tablespaces in a separate query.

and Connor said...

Here's one that I've used in the past

select d.tablespace_name, lpad(round(d.tot_size/1024/1024)||'m',10) tot_size, lpad(round(f.tot_free/1024/1024)||'m',10) tot_free, round(100-100*tot_free/tot_size) pct_used
from
( select tablespace_name, sum(tot_free) tot_free
  from
  ( select tablespace_name, sum(bytes) tot_free
    from dba_free_space
    group by tablespace_name
    union all
    select tablespace_name, sum(case when autoextensible = 'YES' then greatest(maxbytes,bytes)-bytes else 0 end )
    from dba_data_files
    group by tablespace_name
    union all
    select tablespace_name, sum(case when autoextensible = 'YES' then greatest(maxbytes,bytes) else bytes end )
    from dba_temp_files
    group by tablespace_name
    union all
    select s.tablespace, -1*alloc*t.block_size
    from ( select /*+ NO_MERGE */ tablespace, sum(blocks) alloc
           from   v$sort_usage
           group by tablespace) s,
          dba_tablespaces t
    where t.tablespace_name = s.tablespace
  )
  group by tablespace_name
) f,
( select tablespace_name, sum(case when autoextensible = 'YES' then greatest(maxbytes,bytes) else bytes end ) tot_size
  from dba_data_files
  group by tablespace_name
  union all
  select tablespace_name, sum(case when autoextensible = 'YES' then greatest(maxbytes,bytes) else bytes end )
  from dba_temp_files
  group by tablespace_name
) d
where d.tablespace_name like nvl(upper('&tablespace_prefix'),d.tablespace_name)||'%'
and f.tablespace_name(+) = d.tablespace_name
order by pct_used


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, December 14, 2018 - 9:55 am UTC

My query... more than 85% and excluding autoextensible ;)


WITH tbs_auto AS
(SELECT DISTINCT tablespace_name, autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'),
files AS
(SELECT tablespace_name, COUNT (*) tbs_files,
SUM (BYTES) total_tbs_bytes
FROM dba_data_files
GROUP BY tablespace_name),
fragments AS
(SELECT tablespace_name, COUNT (*) tbs_fragments,
SUM (BYTES) total_tbs_free_bytes,
MAX (BYTES) max_free_chunk_bytes
FROM dba_free_space
GROUP BY tablespace_name),
AUTOEXTEND AS
(SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
FROM (SELECT tablespace_name, SUM (maxbytes) size_to_grow
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES) size_to_grow
FROM dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name)
SELECT
round(( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes) / files.total_tbs_bytes) * 100)) total_used_pct,
CASE tbs_auto.autoextensible
WHEN 'YES'
THEN 'YES'
ELSE 'NO'
END AS autoextensible,
a.tablespace_name,
fragments.total_tbs_free_bytes/1024/1024 total_tablespace_free_space
FROM dba_tablespaces a, files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
AND a.tablespace_name = fragments.tablespace_name
AND a.tablespace_name not in ('UNDOTBS')
AND a.tablespace_name = AUTOEXTEND.tablespace_name
AND a.tablespace_name = tbs_auto.tablespace_name(+)
and round(( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes) / files.total_tbs_bytes) * 100)) >= 85
order by 1 desc;

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database