RE: Oracle Database physical files and file system details

From: Sweetser, Joe <JSweetser_at_icat.com>
Date: Fri, 12 Oct 2012 20:31:23 +0000
Message-ID: <D18D6513433DF04394041EA42B53E91C9CD5E651_at_ICATMAIL1.ICAT.com>

I don't know if it's stored in the database though I would guess not. Perhaps a quick/dirty way to generate the df commands would help?

set feedb off
set pages 0
set trims on
set head off
spool x.ksh
select 'df -k ' || file_name from dba_data_files order by file_name; spool off

Don't forget tempfiles and redo logs, too.

hth,
-joe

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kenneth Fowler Sent: Friday, October 12, 2012 2:14 PM
To: oracle-l_at_freelists.org
Subject: Oracle Database physical files and file system details

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



Confidentiality Note: This message contains information that may be confidential and/or privileged. If you are not the intended recipient, you should not use, copy, disclose, distribute or take any action based on this message. If you have received this message in error, please advise the sender immediately by reply email and delete this message. Although ICAT Managers, LLC, Underwriters at Lloyd's, Syndicate 4242, scans e-mail and attachments for viruses, it does not guarantee that either are virus-free and accepts no liability for any damage sustained as a result of viruses. Thank you.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 12 2012 - 22:31:23 CEST