Check tablespace ts_details.sql

set lines 300
col "Data File" for a75
variable ts_name varchar2(100);
exec :ts_name := '&&tablespace_name';
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (Mb)",max(fs.bytes) / (1024 * 1024) "Max Chunk Avail",
SUM(fs.bytes) / (1024 * 1024) "Free (Mb)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used",autoextensible
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes,autoextensible
FROM dba_data_files
GROUP BY tablespace_name,autoextensible) df
WHERE fs.tablespace_name (+) = df.tablespace_name
and df.tablespace_name = :ts_name
GROUP BY df.tablespace_name,df.bytes,autoextensible;
select file_name "Data File",bytes/1024/1024 "Size",status from dba_data_files where tablespace_name = :ts_name;
set pages 3000
spool /tmp/tmp.sql
set heading off
set term off
select '!ls -lrt ' || file_name ||'|'|| 'awk ''{print $9,$10,$11}''' from dba_data_files where tablespace_name = :ts_name;
spool off
set heading on
set term on
@/tmp/tmp.sql