Oracle SQL Query to Find Only the Directory Names of the Datafiles

SQL Query:

select substr(file_name,1,instr(file_name,'/',-1,1 )) from dba_data_files;

Example:

Datafile Names:

SQL> select file_name from dba_data_files;

FILE_NAME

-----------------------------------------------------------

+DATA/PROD/DATAFILE/system.258.920785559

+DATA/PROD/DATAFILE/sysaux.257.920785523

+DATA/PROD/DATAFILE/undotbs2.268.920785667

+DATA/PROD/DATAFILE/users.259.920785595

+DATA/PROD/DATAFILE/p_data.270.921720487

+DATA/PROD/DATAFILE/undotbs1.260.920785595

+DATA/PROD/DATAFILE/p_indexes.269.921720487

+DATA/PROD/DATAFILE/p_lobs.266.921720487

Only Directory Structure

SQL> select substr(file_name,1,instr(file_name,'/',-1,1 ))

from dba_data_files;

SUBSTR(FILE_NAME,1,INSTR(FILE_NAME,'/',-1,1))

---------------------------------------------------------

+DATA/PROD/DATAFILE/

+DATA/PROD/DATAFILE/

+DATA/PROD/DATAFILE/

+DATA/PROD/DATAFILE/

+DATA/PROD/DATAFILE/

+DATA/PROD/DATAFILE/

+DATA/PROD/DATAFILE/

+DATA/PROD/DATAFILE/

8 rows selected.