1.数据表空间
SQL> SELECT t1.name, t2.name FROM v$tablespace t1, v$datafile t2 WHERE t1.ts#=t2.ts# order by t1.name;
NAME NAME
------------------------------ --------------------------------------------------------------------------------
DMX /export/home/oracle/oracle10g/oradata/ORCL/datafile/dmx/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/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.临时表空间
SQL> select o.name,t.name from v$tablespace o,V$tempfile t where o.TS#=t.TS#;
NAME 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