Script to check tablespace details.

 

column file_name format a55

set linesize 132

set pages 200

column tablespace_name format a11

set verify off

accept tbs prompt 'What is the Tablespace Name: '

SELECT  dts.tablespace_name, 

NVL(ddf.bytes / 1024 / 1024, 0) avail,

NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,

NVL(dfs.bytes / 1024 / 1024, 0) free,

TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00')

"Used %" ,

TO_CHAR(NVL((ddf.bytes - NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)) / ddf.bytes

* 100, 0), '990.00') free_pct,

decode(sign(

(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 /

1024, 0)),-1,0,(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 / 1024, 0))  "Required MB"

FROM 

sys.dba_tablespaces dts, 

(select tablespace_name, sum(bytes) bytes 

from dba_data_files group by tablespace_name) ddf, 

(select tablespace_name, sum(bytes) bytes 

from dba_free_space group by tablespace_name) dfs 

WHERE 

dts.tablespace_name = ddf.tablespace_name(+) 

AND dts.tablespace_name = dfs.tablespace_name(+) 

and dts.tablespace_name = '&tbs';

select file_name,bytes/1024/1024 from dba_data_files

where tablespace_name = '&tbs';