-
SQL query
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
-
Use Having Clause to restrict the 10% criteria...here is ur suggestion.
-
Can u show how this can be done pls?..as i am getting errors:(
Thanks in advance,
Chucks
-
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|