Code:
set linesize 222
set pages 100
col file_name for a55
col tablespace_name for a20
select df.file_name, df.tablespace_name, df.bytes/1024/1024 mb,
df.AUTOEXTENSIBLE, df.maxbytes/1024/1024 max_mb,
(df.increment_by*p.blksize)/1024/1024 Increment_mb,
t.extent_management, t.allocation_type, df.status
from dba_data_files df,
dba_tablespaces t,
(select to_number(value) blksize from v$parameter where name = 'db_block_size') p
where t.tablespace_name = df.tablespace_name
union all
select tf.file_name, tf.tablespace_name, tf.bytes/1024/1024 mb,
tf.AUTOEXTENSIBLE, tf.maxbytes/1024/1024 max_mb,
(tf.increment_by*p.blksize)/1024/1024 Increment_mb,
t.extent_management, t.allocation_type, tf.status
from dba_temp_files tf,
dba_tablespaces t,
(select to_number(value) blksize from v$parameter where name = 'db_block_size') p
where t.tablespace_name = tf.tablespace_name
order by tablespace_name
/