Useful SQL statements

1. datafiles

SELECT ‘cp ‘||NAME ||‘   /localdisk/oradb/’||SUBSTR(NAME,INSTR(NAME,‘/’,-1,1)+1 )||‘ &’

from (

       select name from v$datafile

       union all

       select name from v$controlfile

       union all

       select member from v$logfile

)

 

2. Rename Datafiles 

SELECT ‘ALTER DATABASE RENAME FILE ”’||NAME ||”’   TO  ”/localdisk/oradb/’||SUBSTR(NAME,INSTR(NAME,‘/’,-1,1)+1 )||”’;’

FROM (

       SELECT NAME FROM V$DATAFILE

       UNION ALL

       SELECT MEMBER FROM V$LOGFILE

)

 

 

3.    datafiles 

SELECT ‘cp ‘||NAME ||‘   /localdisk/oradb/’||SUBSTR(NAME,INSTR(NAME,‘/’,-1,1)+1 )||‘ &’

FROM V$DATAFILE;

 

4. controlfiles

SELECT ‘cp ‘||NAME ||‘   /localdisk/oradb/’||SUBSTR(NAME,INSTR(NAME,‘/’,-1,1)+1 )||‘ &’

FROM V$CONTROLFILE;

 

 

5. Redo files

SELECT ‘cp ‘||MEMBER ||‘   /localdisk/oradb/’||SUBSTR(MEMBER,INSTR(MEMBER,‘/’,-1,1)+1 )||‘ &’ FROM V$LOGFILE;

 

 

SELECT ‘ALTER DATABASE RENAME FILE ”’||name||”’ TO ”/localdisk/oradb/’||substr(name,instr(name,‘/’,-1,1)+1 )||”’;’  FROM V$DATAFILE;

 

SELECT ‘ALTER DATABASE RENAME FILE ”’||member||”’ TO ”/localdisk/oradb/’||substr(member,instr(member,‘/’,-1,1)+1 )||”’;’  FROM V$LOGFILE;

 

 

 

select ‘ Alter database rename file ”’||name ||”’ to ”’||

decode(substr(name,1,instr(name,‘/’,-1,1)),

‘/u01/oradb/’,‘/localdisk/oradb/’,

‘/u02/oradb/’,‘/localdisk/oradb/’,

‘/u03/oradb/’/localdisk/oradb/‘,

‘/u04/oradb/’,‘/localdisk/oradb/’

 

)||substr(name,instr(name,‘/’,-1,1)+1) ||”’ ;’ name

from v$datafile;