RMAN DUPLICATE db_file_name_convert SELECT log_file_name_convert SELECT

-- Useful Staff

- Rman Duplication -- DB Files Directories Replace - db_file_name_convert select -----------------------------------------------------------------------------
-- Kamil Piórek -- kamil.piorekk@gmail.com
-- Replace " / " to last character of string

      SELECT '*.db_file_name_convert=' AS dir_replace FROM dual

    UNION

    SELECT (SubStr(file_name,1,(instr(file_name,'/', 1, (length (file_name) - length (replace (file_name, '/')))))-1))||''''||' , '||'''/<NEW_DIRECTORY>'' , ' AS dir_replace FROM dba_data_files

    UNION

        SELECT DISTINCT(SubStr(name,1,(instr(name,'/', 1, (length (name) - length (replace (name, '/')))))-1)) ||' , /<NEW_DIRECTORY> , ' AS dir_replace FROM v$tempfile

    UNION

     select case when filename is null then '' else (SubStr(filename,1,(instr(filename,'/', 1, (length (filename) - length (replace (filename, '/')))))-1))||''''||' , '||'''/<NEW_DIRECTORY>'' , ' end AS dir_replace  

                from   v$block_change_tracking   

------------------------------------------------------------------------------------------------------------------------
--  log_file_name_convert
--  Replace
    SELECT '*.log_file_name_convert=' AS dir_replace FROM dual
    UNION
    SELECT DISTINCT(SubStr(member,1,(instr(member,'/', 1, (length (member) - length (replace (member, '/')))))-1)) ||' , /<NEW_DIRECTORY> , ' AS dir_replace FROM v$logfile

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