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/SALESIf 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 ;