SQL query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SQL query

  1. #1
    Join Date
    Dec 2001
    Posts
    337

    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

  2. #2
    Join Date
    Sep 2005
    Posts
    278
    Use Having Clause to restrict the 10% criteria...here is ur suggestion.

  3. #3
    Join Date
    Dec 2001
    Posts
    337
    Can u show how this can be done pls?..as i am getting errors:(

    Thanks in advance,
    Chucks

  4. #4
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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;
    http://www.perf-engg.com
    A performance engineering forum

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width