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.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google