/* Use REGEXP_SUBSTR() to find file_names of datafile, controlfile, redo_log files, archive log files, etc
*/
-- datafiles only fullpath or absolute
SQL> select regexp_substr(file_name,'[a-zA-Z0-9_.]{0,255}+.$') dbfilename from dba_data_files;
DBFILENAME
----------------------------------------
system01.dbf
sysaux01.dbf
undotbs01.dbf
users01.dbf
ggadmin_01.dbf
-- controlfiles only file_name
SQL> select regexp_substr(name,'[a-zA-Z0-9_.]{0,255}+.$') controlfilename from v$controlfile;
CONTROLFILENAME
----------------------------------------
control01.ctl
control02.ctl
-- redo log files only file_name
SQL> select regexp_substr(member,'[a-zA-Z0-9_.]{0,255}+.$') redologname from v$logfile order by redologname
asc;
REDOLOGNAME
----------------------------------------
redo01.log
redo02.log
redo03.log
-- archive logs file only file_name
SQL> select regexp_substr(name,'[a-zA-Z0-9_.]{0,255}+.$') archivelogname from v$archived_log order by
archivelogname asc;
ARCHIVELOGNAME
----------------------------------------
*/
-- datafiles only fullpath or absolute
SQL> select regexp_substr(file_name,'[a-zA-Z0-9_.]{0,255}+.$') dbfilename from dba_data_files;
DBFILENAME
----------------------------------------
system01.dbf
sysaux01.dbf
undotbs01.dbf
users01.dbf
ggadmin_01.dbf
-- controlfiles only file_name
SQL> select regexp_substr(name,'[a-zA-Z0-9_.]{0,255}+.$') controlfilename from v$controlfile;
CONTROLFILENAME
----------------------------------------
control01.ctl
control02.ctl
-- redo log files only file_name
SQL> select regexp_substr(member,'[a-zA-Z0-9_.]{0,255}+.$') redologname from v$logfile order by redologname
asc;
REDOLOGNAME
----------------------------------------
redo01.log
redo02.log
redo03.log
-- archive logs file only file_name
SQL> select regexp_substr(name,'[a-zA-Z0-9_.]{0,255}+.$') archivelogname from v$archived_log order by
archivelogname asc;
ARCHIVELOGNAME
----------------------------------------
