| Tablespace % used query [message #372215] |
Fri, 19 January 2001 00:38  |
Sajit Nair
Messages: 6
Registered: January 2001
|
Junior Member |
|
|
Pls. run this query to get the % utilisation of tablespaces. I have more than one data files for each tablespace. I get the free space column correct but not the Total size. I know that its a sum operation on two diff tables and I am still grouping it.
Suggest if pl/sql is needed to dump the sum into a table and then pick the same to process the final output.
set pagesize 72
set linesize 100
column "Free Space MB" format 999,999,999.99
column "Total Space MB" format 999,999,999.99
column "%Used" format 999.99
select substr(a.tablespace_name,1,15) "Tablespace B",
sum(a.bytes/1024/1024) "Total Space MB",
sum(b.bytes/1024/1024) "Free Space MB",(sum(b.bytes)/sum(a.bytes)*100) "%Used"
from dba_data_files a, dba_free_space b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name
order by 4 desc;
|
|
|
|
| Re: Tablespace % used query [message #372231 is a reply to message #372215] |
Fri, 19 January 2001 15:33  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
This query seems to have similar results. Could there be a difference between allocated filesize and available space due to some overhead??
set pagesize 100
column Tablespace heading "Tablespace" format a18
column "Used Mb" format 99,990
column "Free Mb" format 99,990
column "Pct free" format 990
column "Fragments" heading "Free|Fragments" format 999
column Largest heading "Largest|Fragment|(bytes)" format 999,999,999
column Smallest heading "Smallest|Fragment|(bytes)" format 999,999,999
select s.tablespace_name Tablespace,
s.bytes/1024/1024 "Used Mb",
fs.sum_bytes/1024/1024 "Free Mb",
fs.sum_bytes/(s.bytes + fs.sum_bytes) * 100 "Pct free",
fs.fragments Fragments,
fs.max_bytes Largest,
fs.min_bytes Smallest
from user_ts_quotas s, (select tablespace_name, max(bytes) max_bytes,
sum(bytes) sum_bytes, count(bytes) fragments, min(bytes) min_bytes
from user_free_space
group by tablespace_name) fs
where s.tablespace_name = fs.tablespace_name;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
