如何获取表空间的路径

Question

在服务器上,已经存在一个数据库.
表空间都存在,但是却查不到路径.
我需要怎么做才能查到表空间存放的路径?

Answer 1

在OEM图形界面里可以看的清清楚楚:)

Answer 2

SQL> SELECT a.tablespace_name "tbs name", b.NAME "file path"
  2    FROM dba_tablespaces a, v$datafile b, v$tablespace c
  3   WHERE a.tablespace_name = c.NAME
  4     AND c.ts# = b.ts#;tbs name             file path
-------------------- ----------------------------------------
SYSTEM               /home/ora11g/oradata/ora11g/system01.dbf
SYSAUX               /home/ora11g/oradata/ora11g/sysaux01.dbf
UNDOTBS1             /home/ora11g/oradata/ora11g/undotbs01.db
                     fUSERS                /home/ora11g/oradata/ora11g/users01.dbfSQL> 

Answer 3

10g的话，在oem管理平台上可以看到全路径。

Answer 4

SQL> select * from dba_data_files;
 
FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
/opt/oracle/oradata/ORCL203/system01.dbf                                                  1 SYSTEM                          471859200      57600 AVAILABLE            1 YES            3435972198    4194302         1280  471793664       57592 SYSTEM
/opt/oracle/oradata/ORCL203/undotbs01.dbf                                                 2 UNDOTBS1                        209715200      25600 AVAILABLE            2 YES            3435972198    4194302          640  209649664       25592 ONLINE
/opt/oracle/oradata/ORCL203/sysaux01.dbf                                                  3 SYSAUX                          220200960      26880 AVAILABLE            3 YES            3435972198    4194302         1280  220135424       26872 ONLINE
/opt/oracle/oradata/ORCL203/users01.dbf                                                   4 USERS                             5242880        640 AVAILABLE            4 YES            3435972198    4194302          160    5177344         632 ONLINE
/opt/oracle/usrdata/ORCL203/testts.dbf                                                    5 TESTTS                           20971520       2560 AVAILABLE            5 YES             104857600      12800         1280   20905984        2552 ONLINE
/opt/oracle/usrdata/ORCL203/testts02.dbf                                                  6 TESTTS2                          10485760       1280 AVAILABLE            6 YES              41943040       5120          640   10420224        1272 ONLINE
/opt/oracle/usrdata/ORCL203/testts1.dbf