Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> tablespace usage query, ts$, v$filespace_usage
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 (%)"
select a.name,
sum(b.file_size * a.blocksize) total, sum(b.allocated_space * a.blocksize) usedfrom 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 tablespacesgroup by a.name
sum(sh.bytes_used + sh.bytes_free) total_mb, sum(sh.bytes_used) used_mb
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