- Get link
- X
- Other Apps
Please find the query to find Tablespace usage above 80%.
set line 200
SELECT d.tablespace_name "TS Name", d.num_files "Num Files",
d.asize "Size MB", NVL (f.freebytes, 0) "Free MB",
(d.BYTES - NVL (f.freebytes, 0)) "Used MB",
((d.BYTES - NVL (f.freebytes, 0)) * 100) / d.asize "Percent Used"
FROM (SELECT tablespace_name, SUM (BYTES) / 1024 / 1024 freebytes
FROM dba_free_space
GROUP BY tablespace_name) f,
(SELECT tablespace_name, COUNT (DISTINCT (file_id)) num_files,
sum(greatest(maxbytes,bytes))/1024/1024 asize,
SUM (BYTES) / 1024 / 1024 BYTES
FROM dba_data_files
GROUP BY tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name not like '%UNDO%'
AND ROUND (((d.BYTES - NVL (f.freebytes, 0)) * 100) / d.asize) > 80;
Comments
Post a Comment