| Replace string [message #651543] |
Thu, 19 May 2016 23:45  |
trantuananh24hg
Messages: 742
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Good a day to everyone,
We have got a big database with 504 datafiles within, and I have got create a physical standby for it, ok, it's easy but difficult (of course, to me because of poor SQL) to convert string.
We have got datafile location as following
Primary: +DATA/wrhprim/datafile/data.279.907075777
DG: +DATA/wrhstd/datafile/data.279.907075777
The simply SQL to generate file_name location is
select 'set newname for datafile '''||file_name||''' to '''||''||file_name||''''||';'
from dba_data_files where rownum<=10;
and the simply sql to get wrhprim is:
SELECT SUBSTR(file_name,7,7) from dba_data_files where rownum<=10;
But, I have no idea to put the convert string into one of SQL (wrhprim to wrhstd)
May you help me, thank you very much
[Updated on: Thu, 19 May 2016 23:47] Report message to a moderator |
|
|
|
| Re: Replace string [message #651544 is a reply to message #651543] |
Fri, 20 May 2016 00:10  |
 |
Littlefoot
Messages: 21760
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
It is just the opposite with me - I'm not a DBA so the following code is probably quite stupid but I hope you'll get the idea: if the sample string looks like 'set newname for datafile wrhprim to wrhprim', I'm going to replace the second 'wrhprim' occurrence to 'wrhstd' so that the result looks like 'set newname for datafile wrhprim to wrhstd':
SQL> with test as
2 (select 'set newname for datafile wrhprim to wrhprim' col from dual)
3 select regexp_replace(col, 'wrhprim', 'wrhstd', 1, 2) result
4 from test;
RESULT
------------------------------------------
set newname for datafile wrhprim to wrhstd
SQL>
If that's not what you are looking for, could you provide a sample input and desired output (or just wait for someone who, actually, knows what's going on).
|
|
|
|
|
|
|
|
| Re: Replace string [message #651558 is a reply to message #651547] |
Fri, 20 May 2016 01:31 |
trantuananh24hg
Messages: 742
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
@LittleFoot: Thank you very much, your query is exactly what I need.
WITH dtfstri
AS (SELECT 'set newname for datafile '''
|| file_name
|| ''' to '''
|| ''
|| file_name
|| ''''
|| ';'
col
FROM dba_data_files)
SELECT REGEXP_REPLACE (col,
'wrhprim',
'wrhstd',
1,
2)
result
FROM dtfstri;
set newname for datafile '+DATA/wrhprim/datafile/indx_0631.676.907173861' to '+DATA/wrhstd/datafile/indx_0631.676.907173861';
@Michel & John: Thank you for your advice. However, I often do manually many times, even using RMAN restore/recovery without duplicate method, thanks again.
[Updated on: Fri, 20 May 2016 01:32] Report message to a moderator |
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
