/*
 *
 * Disclaimer: Contents are not reviewed for correctness and 
 * are not endorsed or recommended by ITtoolbox. 
 * We make no representations as to the accuracy, quality, 
 * timeliness, availability, or completeness of the source code and 
 * you  should not rely upon them. Use at your own risk. 
 * This code appeared in the OpenITx Oracle-DB-L discussion
 * (http://oracle.ittoolbox.com/groups/groups.asp?v=ORACLE-DB-L).
 *
 * Excerpted from a message by Todd Sheetz on Tuesday, October 01, 2002
 * http://oracle.ittoolbox.com/groups/groups.asp?v=ORACLE-DB-L&i=147995
 *
 *
 *Oracle Database Size
 *
 *
 */


Run at the SQL Plus Prompt:

column %FREE format 999.99
column FILE_ID format 9999999
column NEXTBYTES format 999,999,999
column bytes format 99,999,999,999
column maxbytes format 9,999,999,999
column freebytes format 99,999,999,999
column next_extend format 9,999,999,999
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF bytes freebytes ON REPORT


SELECT
  SUBSTR(a.tablespace_name,1,15) tablespace_name, 
  a.file_id, 
  SUBSTR(a.file_name,1,50) file_name, 
  a.bytes,
  SUM(b.bytes) freebytes,
  SUM(b.bytes)/a.bytes*100 "%FREE",
  a.autoextensible, 
  DECODE (a.autoextensible, 'YES', a.increment_by*8192, null) NEXTBYTES,
  DECODE (a.autoextensible, 'YES', a.maxbytes, null) maxbytes,
  DECODE (a.autoextensible, 'YES', 
                  a.maxbytes - ((a.increment_by*c.value)+a.bytes), null) next_extend
FROM dba_data_files a, dba_free_space b, v$parameter c
WHERE a.file_id = b.file_id(+)
AND c.name = 'db_block_size'
GROUP BY 
	SUBSTR(a.tablespace_name,1,15), 
	a.file_id, 
	SUBSTR(a.file_name,1,50), 
	a.bytes,
	a.autoextensible, 
	a.increment_by,
	a.maxbytes, 
	a.maxbytes - ((a.increment_by*c.value)+a.bytes)
ORDER BY 1,2