ora_free_tabspace.sql

set pagesize 0
set numf '9999999.99'
SELECT
	NVL(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name,
	((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used,
	NVL(kbytes_alloc/1024,0) alloc,
	NVL(kbytes_free/1024,0) free,
	autoextensible
FROM
	(SELECT
		SUM(bytes)/1024 Kbytes_free,
		max(bytes)/1024 largest,
		tablespace_name
	FROM
		sys.dba_free_space
	GROUP BY
		tablespace_name
	) a,
	(SELECT SUM(bytes)/1024 Kbytes_alloc,
		tablespace_name
	FROM
		sys.dba_data_files
	GROUP BY
		tablespace_name
	) b,
	(SELECT
		tablespace_name,
		autoextensible
	FROM
		sys.dba_data_files
	GROUP BY
		tablespace_name,
		autoextensible
	HAVING
		autoextensible='YES'
	) c
	WHERE
		a.tablespace_name (+) =  b.tablespace_name
	AND
		c.tablespace_name (+) = b.tablespace_name
;
quit;