Useful SQL statements

1. To create a script to copy all the datafiles to new location:

select 'cp 'name' /newpath/'substr(name,instr(name,'/',-1,1)+1))' &'

from v$database;

2. To create a script to copy all the datafiles, controlfiles and redolog files to new location:

select 'cp 'name'/new_path/'
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
)

3. To get the sql statements to Rename all datafiles and redolog files:

select 'alter database rename file '''name ''' to
''/new_path/'substr(name,instr(name,'/',-1,1)+1)''';'
from (
select name from v$datafile
union all
select member from v$logfile
)

4. Copy datafile

select 'cp ' file_name

decode(substr(file_name,1,instr(file_name,'/',-1,1)),

'/Old_path1/','/New_path1/',

'/Old_path2/','/New_path2/'

)substr(file_name,instr(file_name,'/',-1,1)+1)

' &' file_name

from dba_data_files;

5. Copy redolog file

select 'cp ' member ' '

decode(substr(member,1,instr(member,'/',-1,1)),
'/Old_path1/','/New_path1/',
'/Old_path2/','/New_path2/'
)substr(member,instr(member,'/',-1,1)+1)' &' member

from v$logfile;

6. Rename data file

select ' Alter database rename file '''file_name ''' to '''

decode(substr(file_name,1,instr(file_name,'/',-1,1)),
'/Old_path1/','/New_path1/',
'/Old_path2/','/New_path2/'
)substr(file_name,instr(file_name,'/',-1,1)+1) ' ;' file_name
from dba_data_files;

7. Rename redolog file

select 'alter database rename file ''' member ''' to '''
decode(substr(member,1,instr(member,'/',-1,1)),

'/Old_path1/','/New_path1/'

,'/Old_path2/','/New_path2/')

substr(member,instr(member,'/',-1,1)+1) ''' ;' member
from v$logfile;

8. Datafile Offline drop

select 'alter database datafile 'file_id' offline drop;'

from dba_data_files;