Oracle Database physical files and file system details

From: Kenneth Fowler <kenneth.r.fowler_at_gmail.com>
Date: Fri, 12 Oct 2012 16:13:30 -0400
Message-ID: <CAEHB_aqArCnwKOEs=yR4HcDTjubuHE92ThUv0xAe9z3ncpS9gQ_at_mail.gmail.com>



Hi List,

Can anyone tell me if there is a v$, x$ or dba_ table/view that contains file system details for the physical files that make up an oracle database?

I recently accepted a job (Oracle DBA) at a new company and I notice that the standard database layout here sometimes makes use of Unix soft links. Lets say the database files from dba_data_files look like this...

SQL> select version from v$instance;

VERSION



11.2.0.2.0

SQL> select file_name from dba_data_files;

FILE_NAME



/u01/oradata/dbarep/system01.dbf
/u01/oradata/dbarep/sysaux01.dbf
/u01/oradata/dbarep/undotbs01.dbf
/u01/oradata/dbarep/users01.dbf

But there are soft links involved...

$ ls -ald /u01
lrwxrwxrwx 1 oracle dba 4 Feb 4 2011 /u01 -> /mnt/nfsstore/oracle

So the database files are stored in
/mnt/nfsstore/oracle/oradata/dbarep which happens to be an NFS file
system that is giving a lot of trouble lately. I have already set up an automated process that will collect database metadata for loading into a DBA repository for analysis and reporting etc. I already collect dba_data_files, dba_temp_files along with a lot of other stuff however I checked around (Google mostly) but cannot find any reference to v$, x$ or dba_ tables/views that can tell me what file system a file happens to be on and if it is local or NFS etc. I realize that I could get this information by using Unix commands (df for example) but that starts to get a bit messy so my hope was that I could somehow get the information from the database (sqlplus). Any ideas?

Thanks,
Ken.

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 12 2012 - 22:13:30 CEST

Original text of this message