How to extract all datafile names without path using substr and instr functions

select trim(
            substr(file_name,
                (instr(file_name,'/', -1, 1) +1)
                )
               )  "file name"
from  dba_data_files;

Result:

file name
system01.dbf
sysaux01.dbf
undotbs01.dbf
users01.dbf
appl_data.dbf

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
;