Show the files that comprise a tablespace

set lines 100
col tbn format a10
col file_name format a50
select d.tablespace_name tbn,
       d.file_name,
       ceil(d.bytes / 1024 / 1024) "size MB"
  from dba_data_files d
 order by 3 desc
/

Результат:

TBN        FILE_NAME                                             size MB
---------- -------------------------------------------------- ----------
SYSTEM     C:\ORA11G\ORADATA\ORCL\SYSTEM01.DBF                       700
SYSAUX     C:\ORA11G\ORADATA\ORCL\SYSAUX01.DBF                       657
EXAMPLE    C:\ORA11G\ORADATA\ORCL\EXAMPLE01.DBF                      100
UNDOTBS1   C:\ORA11G\ORADATA\ORCL\UNDOTBS01.DBF                       45
USERS      C:\ORA11G\ORADATA\ORCL\USERS01.DBF                          5

Источник.