Showing posts with label mount point. Show all posts
Showing posts with label mount point. Show all posts

Thursday, June 10, 2021

How to extract all mountpoints for oracle data files in a database

To extract all unique mount points used for datafiles in a database, used the query below:
SELECT distinct SUBSTR(FILE_NAME, 1,
               INSTR(FILE_NAME, '/', -1, 1) --> Search for position of the first occurrence of the char '/', start at end of string
              -1) "PATH" --> starting from the rightmost side of the string, search from position found in INSTR above, and work your way to position 1
FROM DBA_DATA_FILES
;

Example output:
PATH
---------------------------------
/sales_db_02/oradata/SALES
/salesdb/oradata/SALES
/sales_db_01/oradata/SALES
If the database is in mount-mode (for example, a physical standby database), exchange dba_data_files with v$datafile:
 SELECT distinct SUBSTR(NAME, 1,
                   INSTR(NAME, '/', -1, 1)  
                  -1) "PATH" 
FROM v$datafile;

For logfiles, exchange dba_data_files with v$logfile and file_name with member:
  SELECT distinct SUBSTR(member, 1,
               INSTR(member, '/', -1, 1) 
              -1) "PATH" 
FROM v$logfile
;