SQL query

[Chucks_k]

Hi all,

I am new to SQL so i was wondering i could get some help in tweaking this query i have:

SET PAGESIZE 30
COLUMN file_id HEADING "FILE|NO"
COLUMN tablespace_name HEADING "TABLESPACE|NAME" FORMAT A25
COLUMN no_mbytes HEADING "SIZE|MBYTES" FORMAT 999999.99
COLUMN mb_used HEADING "USED|MBYTES" FORMAT 999999.99
COLUMN free HEADING "FREE|MBYTES" FORMAT 999999.99
COLUMN %free HEADING "%FREE" FORMAT 999

SELECT b.file_id,
b.tablespace_name,
b.bytes/1048576 no_mbytes,
(b.bytes/1048576 - sum(nvl(a.bytes/1048576,0))) mb_used,
sum(nvl(a.bytes/1048576,0)) "free",
sum(nvl(a.bytes/1048576,0)) / (b.bytes/1048576) * 100 "%free"
FROM sys.dba_free_space a,
sys.dba_data_files b
WHERE a.file_id(+) = b.file_id
GROUP BY b.tablespace_name, b.file_id, b.bytes
order by "%free" asc;

This query displays tablespaces,mb used and % free. Which is fine. However i want it to display the tablespaces that are just 10% free. Is this possible? Any suggestions will be greatly appreciated.

Thanks in advance.

Chucks

[tabreaz]

Use Having Clause to restrict the 10% criteria...here is ur suggestion.

[Chucks_k]

Can u show how this can be done pls?..as i am getting errors:(

Thanks in advance,
Chucks

[malay_biswal]

Try this.

SELECT b.file_id,
b.tablespace_name,
b.bytes/1048576 no_mbytes,
(b.bytes/1048576 - sum(nvl(a.bytes/1048576,0))) mb_used,
sum(nvl(a.bytes/1048576,0)) "free",
sum(nvl(a.bytes/1048576,0)) / (b.bytes/1048576) * 100 "%free"
FROM sys.dba_free_space a,
sys.dba_data_files b
WHERE a.file_id(+) = b.file_id
GROUP BY b.tablespace_name, b.file_id, b.bytes
having sum(nvl(a.bytes/1048576,0)) / (b.bytes/1048576) * 100 <=10
order by "%free" asc;