Script: Current Allocation and Usage of Tablespace

-- ========================================================================================
-- Find the current allocation and usage of tablespaces (comma separated)
-- List the data file names associated with specified tablespaces
-- ========================================================================================
SET pages 200
SET lines 200
COLUMN name     	FORMAT A20         HEAD "Tablespace Name"
COLUMN Pct_used 	FORMAT 999.9       HEAD "Percent Used(%)"
COLUMN kbytes_used  FORMAT 999,999,999 HEAD "Allocated Space (MB)"
COLUMN used     	FORMAT 999,999,999 HEAD "Used Space (MB)" 
COLUMN free     	FORMAT 999,999,999 HEAD "Free Space (MB)"

SELECT   
	  NVL(FULL.tablespace_name, nvl(FREE.tablespace_name, 'UNKOWN')) Name
    , kbytes_used                    
    , kbytes_used - NVL(kbytes_free,0) 	Used
    , NVL(kbytes_free,0)             	Free
    , ((kbytes_used-nvl(kbytes_free,0)) / kbytes_used)*100 Pct_used
FROM 
    ( 
		SELECT  
			  SUM(bytes)/(1024*1024)    kbytes_free
			, MAX(bytes)/(1024*1024)    max_free
            , tablespace_name
		FROM    
			sys.DBA_FREE_SPACE
		GROUP BY 
			tablespace_name ) FREE,
		( 
			SELECT  
				  SUM(bytes)/(1024*1024)    Kbytes_used
				, tablespace_name
			FROM    
				sys.DBA_DATA_FILES
			WHERE 
				tablespace_name in (&&TBS_NAMES_IN_QUOTE_COMMA_SEP)
			GROUP BY tablespace_name ) FULL
WHERE  
	FREE.tablespace_name (+) = FULL.tablespace_name
;

Enter value for tbs_names_in_quote_comma_sep: 'GO_DATA'
old  24:                                tablespace_name in (&&TBS_NAMES_IN_QUOTE_COMMA_SEP)
new  24:                                tablespace_name in ('GO_DATA')

Tablespace Allocated Space (MB) Used Space (MB) Free Space (MB) Percent Used(%)
---------- -------------------- --------------- --------------- ---------------
GO_DATA                   3,072           2,470             603            80.4

COLUMN tablespace_name     	FORMAT A10     
COLUMN file_name     		FORMAT A50     
select tablespace_name, file_name, bytes/1024/1024 "Size_MB", AUTOEXTENSIBLE from dba_data_files 
where tablespace_name in (&TBS_NAMES_IN_QUOTE_COMMA_SEP)
;

TABLESPACE FILE_NAME                                             Size_MB AUT
---------- -------------------------------------------------- ---------- ---
GO_DATA    +PRDW_DG1/prdw/datafile/go_data.285.730419055            3072 NO