newbie

[itwizzkid]

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;

[Mr.Hanky]

Is that datafile online?

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

MH