#!/bin/sh

 
#
# (c) spd_at_daphne.cps.unizar.es
# Fri Oct  7 11:14:43 MEST 2005
# License: Cantoware - Si te gusta ya te puedes dar con un canto en los dientes
#
# This script list tablespaces
# Needs: root privileges to be able to "su oracle"
# Use: ./ora-df.sh
# Depends: ora-common.sh
#

. $HOME/usr/sbin/ora-common.sh

su $ORACLE_OWNER -c "$CMD $ARGS" 2>&1 <<++
$INIT
SET pages 40
SET lines 132
		
COLUMN tablespace_name format a15
COLUMN file_name format a30

select tablespace_name,
	to_char(sum(bytes)/1048576, '99,999,990.99') as MB_FREE, 
count(*) as FRAGMENTS,
	to_char(max(bytes)/1048576, '99,999,990.99') as BIGGEST_BIT
from dba_free_space
group by tablespace_name;

SELECT 	fs.tablespace_name, df.file_name, COUNT(*) AS fragments, 
	ROUND(SUM(fs.bytes)/1024,2) AS total_kb, ROUND(MAX(fs.bytes)/1024,2) AS biggest_kb
FROM 	DBA_FREE_SPACE fs, DBA_DATA_FILES df
WHERE 	fs.file_id(+) = df.file_id
GROUP BY fs.tablespace_name,df.file_name;
++