I have the following query:
select a.tablespace_name "Tab_name",
ROUND(SUM(a.bytes)/1024/1024/1024, 2) "Sum_files_GB",
ROUND(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)), 2) "Max_size_GB",
ROUND((SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)) - (SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))), 2) "Free_GB",
round(100*(SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)))) "%_used"
from dba_data_files a, sys.filext$ b, (SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free",D.BLOCK_SIZE FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name, D.BLOCK_SIZE) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"
order by round(100*(SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)))) desc;
I want to add the following conditions:
and round(100*(SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)))) "%_used" < 30
and ROUND((SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)) - (SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))), 2) "Free_GB" < 99
As a result, the query looks as follows:
select a.tablespace_name "Tab_name",
ROUND(SUM(a.bytes)/1024/1024/1024, 2) "Sum_files_GB",
ROUND(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)), 2) "Max_size_GB",
ROUND((SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)) - (SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))), 2) "Free_GB",
round(100*(SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)))) "%_used"
from dba_data_files a, sys.filext$ b, (SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free",D.BLOCK_SIZE FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name, D.BLOCK_SIZE) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
and round(100*(SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)))) "%_used" < 30
and ROUND((SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)) - (SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))), 2) "Free_GB" < 99
GROUP by a.tablespace_name, c."Free"
order by round(100*(SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*C.BLOCK_SIZE/1024/1024/1024)))) desc;
But this query gives me the error message ORA-00934. So, how can include the above conditions in the original query?