-
Query to generate SET NEWNAME for DATAFILE
Hi,
I am running Oracle 11.2.0.3 on Redhat Linux
I want to run a query to generate a command to rename ASM files:
This is how the ASM data looks:
+DATA/db1/datafile/report.180.248540158
This is how I want it to look:
SET NEWNAME FOR DATAFILE 1 TO ‘/u02/data/dev/db1/report.180.dbf’;
How can I write the query, using SUBSTR and INSTR to generate the command:
e.g. - SELECT ‘SET NEWNAME FOR DATAFILE ‘||FILE#||’ TO ‘’’||’/u02/……
To look like:
SET NEWNAME FOR DATAFILE 1 TO ‘/u02/data/dev/db1/report.180.dbf’;
Thanks,
Lucky
-
It looks like you had almost everything you needed, except for the replace function.
Code:
SELECT 'SET NEWNAME FOR DATAFILE '||FILE#||' TO '''||
REPLACE(FILE_NAME, '+DATA/','/u02/data/dev/')||';' TEXT
FROM DBA_DATA_FILES
order by 1;
-
Thanks for your reply. This is what I used to get it to work:
SELECT 'SET NEWNAME FOR DATAFILE '||FILE#||' TO '''||'/u02/data/dev/db1'||
SUBSTR(NAME,
INSTR(NAME, '/', -1, 1),
INSTR(NAME, '.',1,2) - INSTR(NAME, '/',-1,1)) ||'.dbf'';'
FROM V$DATAFILE;
Thanks,
Lucky
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|