Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> tablespace usage query, ts$, v$filespace_usage

tablespace usage query, ts$, v$filespace_usage

From: ntalbs <ntalbs_at_gmail.com>
Date: 13 Feb 2006 04:34:10 -0800
Message-ID: <1139834050.749843.284880@z14g2000cwz.googlegroups.com>


Hello.
I found several SQL scripts which show tablespace usage. The scripts usually query dba_free_space to get free space of tablespaces,
which is very slow.

I was trying to improve the query or find better way to get tablespace usage, and found
v$filespace_usage view. So I wrote this query which uses v$filespace_usage and ts$.

I couldn't find any information about v$filespace_usage nor ts$, the meaning of
contents$ and online$ columns are just my conjecture. I queried ts$ and found
the following:

contents$: 0 for permanent tablespace, 1 for temporary tablespace online$: 1 for online, 2 for offline, 3 for dropped tablespace

So the complete query I wrote is:

select name,

    round(total/1024/1024) "Total (MB)",
    round(used/1024/1024) "Used (MB)",
    round((total-used)/1024/1024) "Free (MB)",
    round(used/total*100) "Usage (%)"

from (

    select a.name,

        sum(b.file_size * a.blocksize) total,
        sum(b.allocated_space * a.blocksize) used
    from ts$ a, v$filespace_usage b
    where a.ts# = b.tablespace_id(+)
      and a.contents$ = 0 -- exclude temp tablespaces
      and a.online$ < 3   -- exclude dropped tablespaces
    group by a.name
    union all
    select sh.tablespace_name,
    	sum(sh.bytes_used + sh.bytes_free) total_mb,
    	sum(sh.bytes_used) used_mb

    from v$temp_space_header sh
    group by tablespace_name
    )
order by 1;

I'm not sure this query is correct. Normally, it shows the correct usage of tablespaces,
however, sometimes it shows incorrect usage especially right after add big datafile to
tablespaces.

Anyone who can explain about ts$, v$filespace_usage? Can I use above query safely?

Thanks in advance. Received on Mon Feb 13 2006 - 06:34:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US