Sunday, July 26, 2009

Generate script to transfer data files with compression through SSH

Recently, I have used the following SQL to generate scripts for transferring data files in order to clone an open database. Some explanation about a similar task can be seen in this previous post



select case when mod(rn,10) = 0
then
'ssh srcserv ' || '''cat ' || file_name ||
' | gzip -c '' | gunzip -c > ' || file_name || ' &'
|| chr(10) ||
'wait ' || chr(10) ||
'echo "`date` rn: ' || rn || '" >> $LOGFILE'|| chr(10)
else
'ssh srcserv ' || '''cat ' || file_name ||
' | gzip -c '' | gunzip -c > ' || file_name || ' &'
end
from
(
select file_name, row_number() over ( order by bytes desc ) rn
from dba_data_files
where tablespace_name=upper('&tabspacename')
)
/

No comments: