Showing posts with label SQL functions. Show all posts
Showing posts with label SQL functions. Show all posts

Thursday, August 31, 2023

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

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
;