techxplore blog

SQL Command to Show Oracle Physical File Location

Oracle database is a primary server which needs administration and close monitoring. There are instances where a copy or a backup is restored on a different computer for backup integrity verification. A backup requires a snapshot of a data file, table space, or database at a specific time. In cases where the database data backup is lost redo data information would be helpful.

Making backup and data recovery plan require accurate knowledge of data file location, redo files location, and control files location. The lack of knowledge of the said files locations would make lost data recovery difficult. An SQL command that easily shows Oracle physical file location are as listed below.

SQL command to show datafile location:
SQL> SELECT name FROM v$datafile;

SQL command to show logfile location:
SQL> SELECT member FROM v$logfile;

SQL command to show controlfile location:
SQL> SELECT name FROM v$controlfile;

In case of Oracle database media failure, recovering a backup from tape media that have been properly prepared based on the correct locations would give a higher data recovery probability. Thus, SQL command to disclose Oracle physical file location is useful for a database administrator.

Leave a Reply