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 A10         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