View the tablespace name and corresponding file

1. Data Table space
SQL> select t1.name, t2.name from V $ tablespace T1, V $ datafile T2 where t1.ts # = t2.ts # order by t1.name;
 
Name
--------------------------------------------------------------------------------------------------------------
DMX/export/home/Oracle/Oracle10g/oradata/orcl/datafile/DMX. DBF
Dmx_demo/export/home/Oracle/Oracle10g/oradata/orcl/datafile/dmxdemo/dmx_demo.dbf
Golden_gate/export/home/Oracle/Oracle10g/oradata/orcl/datafile/goden_gate.dbf
MDC/export/home/Oracle/Oracle10g/oradata/orcl/datafile/MDC. DBF
Odstbs/export/home/Oracle/Oracle10g/oradata/orcl/datafile/forps/odstbs. DBF
Qomo/export/home/Oracle/Oracle10g/oradata/orcl/datafile/forps/qomo. DBF
Sysaux/export/home/Oracle/Oracle10g/oradata/orcl/datafile/o1_mf_sysaux_6r5s8xjp _. DBF
System/export/home/Oracle/Oracle10g/oradata/orcl/datafile/o1_mf_system_6r5s8xdn _. DBF
Tbs_metadata/export/home/Oracle/Oracle10g/oradata/orcl/datafile/forps/tbs_metadata.dbf
To_tablespace/export/home/Oracle/Oracle10g/oradata/orcl/datafile/fordev_to/to. DBF
Undotbs1/export/home/Oracle/Oracle10g/oradata/orcl/datafile/o1_mf_undotbs1_6r5s8xpd _. DBF
Users/export/home/Oracle/Oracle10g/oradata/orcl/datafile/o1_mf_users_6r5s8xtl _. DBF
 
12 rows selected

 

2. Temporary tablespace
SQL> select O. Name, T. name from V $ tablespace o, V $ tempfile t where O. Ts # = T. Ts #;
 
Name
--------------------------------------------------------------------------------------------------------------
Temp/export/home/Oracle/Oracle10g/oradata/orcl/datafile/o1_mf_temp_6r5sbn3r _. tmp
Dmxtmp/export/home/Oracle/Oracle10g/oradata/orcl/datafile/DMX/dmxtmp. DBF
Dmx_demo_temp/export/home/Oracle/Oracle10g/oradata/orcl/datafile/dmxdemo/dmx_demo_temp
Golden_gate_temp/export/home/Oracle/Oracle10g/oradata/orcl/datafile/golden_gate_tmp.dbf
Qomotmp/export/home/Oracle/Oracle10g/oradata/orcl/datafile/forps/qomotmp. DBF
Mdc_tmp/export/home/Oracle/Oracle10g/oradata/orcl/datafile/MDC/mdc_tmp.dbf
To_temp/export/home/Oracle/Oracle10g/oradata/orcl/datafile/fordev_to/to_temp.dbf
 
7 rows selected