Guru's and Experts
DB: Oracle
Trying to resolve this tricky case for last few days and now decided to take experts help.
I'm accepting filesystem mounts from user input and would like to use that value in SQL query to generate dynamic script to restore datafiles.
I can generate dynamic script if I know what filesystems name will be but trying to figure out on how to take user input value and put that in query?
Runtime: ./gen_query.ksh -oldDB db1 -newDB db2 -mt /u01,/u02,/u03,/u04
values for -oldDB, -newDB, -mt are entered by users.
-mt values will be separated by comma ","
-mt values can differ from user and can be two or three or x numbers so I can't really hardcode them.
How to check all -mt mounts exist on server and place them into below to generate restore script?
Is it possible?
Dynamic query:
#!/bin/ksh LOG_FILE = test_gen_query.sql
exec >> $LOG_FILE 2>&1
gen_query= SELECT 'set newname for datafile '''
|| file_name
|| ''' to ''<-mt values>'
|| replace to_char(mod(rownum, 4)
|| replace(substr(file_name, 5, length (file_name)),'$2','$4')
|| ''';' FROM ( SELECT file_name, bytes from dba_data_files ORDER BY bytes desc )
/
Sample output required:
set newname for datafile '/u40/oradata/db1/test1.dbf' to '/u01/oradata/db2/test1.dbf';
set newname for datafile '/u40/oradata/db1/test2.dbf' to '/u02/oradata/db2/test2.dbf';
set newname for datafile '/u41/oradata/db1/test3.dbf' to '/u03/oradata/db2/test3.dbf';
set newname for datafile '/u40/oradata/db1/test4.dbf' to '/u04/oradata/db2/test4.dbf';
set newname for datafile '/u40/oradata/db1/test5.dbf' to '/u01/oradata/db2/test5.dbf';
set newname for datafile '/u40/oradata/db1/test6.dbf' to '/u02/oradata/db2/test6.dbf';
...
...