Oracle Database Discussions

Hello Experts,

I have a task to automate the database refresh through rman.  I am able to generate the SET NEWNAME script using

below command.

SQL>select 'SET NEWNAME FOR DATAFILE ' || FILE_ID|| ' TO ' || ' ' || '''/u01/app/oracle/oradata/'||

substr(file_name,instr(file_name,'/',-1)+1) ||''';' from dba_data_files order by file_name asc;

'SETNEWNAMEFORDATAFILE'||FILE_ID||'TO'||''||'''/U01/APP/ORACLE/ORADATA/DROP/'||SUBSTR(FILE_NAME,INSTR(FILE_NAME,'/',-1)+1)||''';'

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

SET NEWNAME FOR DATAFILE 5 TO  '/u01/app/oracle/oradata/undotbs2.599.891294803.dbf';

SET NEWNAME FOR DATAFILE 4 TO  '/u01/app/oracle/oradata/undotbs1.600.891294775.dbf';

SET NEWNAME FOR DATAFILE 6 TO  '/u01/app/oracle/oradata/users.279.875799791.dbf';

SET NEWNAME FOR DATAFILE 1 TO  '/u01/app/oracle/oradata/system01.599.891294803';

SET NEWNAME FOR DATAFILE 2 TO  '/u01/app/oracle/oradata/sysaux.599.891294803.dbf';

but here I need to remove the .dbf extension from all datafiles in output for which I used the replace command

but it somehow errors out.  I tried to figure it out but so far its been more than 2 hours. I will be really grateful

if someone can help me with it.

SQL> select replace('(select 'SET NEWNAME FOR DATAFILE ' || FILE_ID|| ' TO ' || ' ' || '''/u01/app/oracle/oradata/'|| substr(file_name,instr(file_name,'/',-1)+1) ||''';' from dba_data_files order by file_name asc)','.dbf',NULL)col1 from dual

                         *

ERROR at line 1:

ORA-00907: missing right parenthesis

Thanks

Mohammed