查看表空间对应的数据文件

set wrap off 查询返回的纪录每行超过默认宽度时，可选择换行（on ）或不换行（off）,默认为换行；

set linesize N 设置查询返回的纪录每行的宽度，超过这个宽度则截掉，不过这个宽度则补空格。

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
7 SLTBS YES NO YES

7 rows selected.

SQL> select * from v$datafile;
FILE# CREATION_CHANGE# CREATION_TIM TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE#
---------- ---------------- ------------ ---------- ---------- ------- ---------- ------------------
1 9 30-JUN-05 0 1 SYSTEM READ WRITE 988937
2 444079 30-JUN-05 1 2 ONLINE READ WRITE 988937
3 6609 30-JUN-05 2 3 ONLINE READ WRITE 988937
4 10566 30-JUN-05 4 4 ONLINE READ WRITE 988937
5 467985 23-AUG-10 6 5 ONLINE READ WRITE 988937
6 958147 18-FEB-11 7 6 ONLINE READ WRITE 990485
7 990152 19-FEB-11 7 7 ONLINE READ WRITE 990485

7 rows selected.

SQL> select t1.name, t2.name
2 from v$tablespace t1, v$datafile t2
3 where t1.ts# = t2.ts#;

NAME NAME
------------------------------ ---------------------------------------------------------------------
SYSTEM /u02/oradata/orc/system01.dbf
UNDOTBS1 /u02/oradata/orc/undotbs01.dbf
SYSAUX /u02/oradata/orc/sysaux01.dbf
USERS /u02/oradata/orc/users01.dbf
EXAMPLE /u02/oradata/orc/example01.dbf
SLTBS /u02/oradata/orc/sltbs01.dbf
SLTBS /u02/oradata/orc/sltbs02.dbf

7 rows selected.
这样就可以查处每个tablespace 对应的数据文件了。