newbie
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: newbie

  1. #1
    Join Date
    Feb 2001
    Posts
    107
    The sql below works fine except that 'ORD' tablespace has two data files ord.dbf and ord1.dbf. Ord.dbf is 100% full. When I run the query, it is only picking the stats for the ord1.bf datafile. I will like it to pickup both dbf files and give me a statistic for both ie combine them together. If anyone can show me it to amend the script will appreciate it.

    Thanks in advance

    set pagesize 66
    set line 132

    clear breaks
    clear computes

    column "Total Bytes" format 9,999,999,999
    column "SQL Blocks" format 999,999,999
    column "Bytes Free" format 9,999,999,999
    column "Bytes Used" format 9,999,999,999
    column "% Free" format 9999.999
    column "% Used" format 9999.999
    break on report
    compute sum of "Total Bytes" on report
    compute sum of "SQL Blocks" on report
    compute sum of "VMS Blocks" on report
    compute sum of "Bytes Free" on report
    compute sum of "Bytes Used" on report
    compute avg of "% Free" on report
    compute avg of "% Used" on report
    SELECT SUBSTR(fs.FILE_ID,1,3) "ID#",
    fs.tablespace_name,
    df.bytes "Total Bytes",
    df.blocks "SQL Blocks",
    sum(fs.bytes) "Bytes Free",
    (100*((sum(fs.bytes))/df.bytes)) "% Free",
    df.bytes-sum(fs.bytes) "Bytes Used",
    (100*((df.bytes-sum(fs.bytes))/df.bytes)) "% Used"
    FROM SYS.dba_data_files df, SYS.dba_free_space fs
    WHERE df.file_id(+) = fs.file_id
    AND fs.tablespace_name = 'ORD'
    GROUP BY fs.FILE_ID, fs.tablespace_name, df.bytes, df.blocks
    ORDER BY fs.tablespace_name;

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    Is that datafile online?

    SQL> select FILE_NAME, TABLESPACE_NAME, STATUS
    2 from dba_data_files;

    MH

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