0

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?

honk
  • 9,137
  • 11
  • 75
  • 83
  • You can look it up here: https://www.techonthenet.com/oracle/errors/ora00934.php You can do it for most errors you get, it will clarify you the meaning of error and possible reasons for it (as it completely states for this error). The answer user hotfix gave you is right. – Goran Kutlaca Aug 09 '18 at 14:41

1 Answers1

1

use a having clause

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"
having 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)))) < 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)  < 99
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;
hotfix
  • 3,376
  • 20
  • 36