Oracle How to get only file name ( with out path )

Oracle file name

Oracle How to get only file name ( with out path )

Oracle file name

 select substr(name,instr(name,'/',-1)+1) file_name from v$datafile;

FILE_NAME
--------------------
system01.dbf
sysaux01.dbf
undotbs01.dbf
users01.dbf
example01.dbf

set linesize 200
col file_name format a15
col name format a70

select name, substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) file_name from v$datafile;

NAME                                                                   FILE_NAME
---------------------------------------------------------------------- ---------------
/u01/app/oracle/oradata/vihaan/system01.dbf                            system01
/u01/app/oracle/oradata/vihaan/sysaux01.dbf                            sysaux01
/u01/app/oracle/oradata/vihaan/undotbs01.dbf                           undotbs01
/u01/app/oracle/oradata/vihaan/users01.dbf                             users01
/u01/app/oracle/oradata/vihaan/example01.dbf                           example01

set linesize 200
col file_name format a15
col name format a70
col path for a50
select name,substr(name, 1, instr(name, '/',-1)) path , substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) file_name from v$datafile;

NAME                                                                   PATH                                               FILE_NAME
---------------------------------------------------------------------- -------------------------------------------------- ---------------
/u01/app/oradata/Orclsex/system01.dbf                                  /u01/app/oradata/Orclsex/                          system01
/u01/app/oradata/Orclsex/sysaux01.dbf                                  /u01/app/oradata/Orclsex/                          sysaux01
/u01/app/oradata/Orclsex/undotbs01.dbf                                 /u01/app/oradata/Orclsex/                          undotbs01
/u01/app/oradata/Orclsex/users01.dbf                                   /u01/app/oradata/Orclsex/                          users01

set linesize 200
col file_name format a50
col name format a70
col path for a50
select FILE_NAME,substr(FILE_NAME, 1, instr(FILE_NAME, '/',-1)) path , substr(FILE_NAME,instr(FILE_NAME,'/',-1)+1, instr(substr(FILE_NAME,instr(FILE_NAME,'/',-1)+1),'.')-1 ) file_name from dba_data_files
where TABLESPACE_NAME='USERS' ;

FILE_NAME                                          PATH                                               FILE_NAME
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
/u01/app/oradata/Orclsex/users01.dbf               /u01/app/oradata/Orclsex/                          users01
/u01/app/oradata/Orclsex/users02.dbf               /u01/app/oradata/Orclsex/                          users02
/u01/app/oradata/Orclsex/users03.dbf               /u01/app/oradata/Orclsex/                          users03
/u01/app/oradata/Orclsex/users04.dbf               /u01/app/oradata/Orclsex/                          users04
/u01/app/oradata/Orclsex/users05.dbf               /u01/app/oradata/Orclsex/                          users05
/u01/app/oradata/Orclsex/users06.dbf               /u01/app/oradata/Orclsex/                          users06
/u01/app/oradata/Orclsex/users07.dbf               /u01/app/oradata/Orclsex/                          users07
/u01/app/oradata/Orclsex/users08.dbf               /u01/app/oradata/Orclsex/                          users08
/u01/app/oradata/Orclsex/users09.dbf               /u01/app/oradata/Orclsex/                          users09
/u01/app/oradata/Orclsex/users10.dbf               /u01/app/oradata/Orclsex/                          users10
/u01/app/oradata/Orclsex/users11.dbf               /u01/app/oradata/Orclsex/                          users11
/u01/app/oradata/Orclsex/users12.dbf               /u01/app/oradata/Orclsex/                          users12
/u01/app/oradata/Orclsex/users13.dbf               /u01/app/oradata/Orclsex/                          users13

13 rows selected.

====

SELECT    'set newname for datafile '

       || file#

       || ' to ''/dumps/UGARB/'

       || substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) ||'.dbf'

       || ''';'

  FROM v$datafile;