Query to generate SET NEWNAME for DATAFILE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Query to generate SET NEWNAME for DATAFILE

  1. #1
    Join Date
    Oct 2007
    Posts
    31

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    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;
    this space intentionally left blank

  3. #3
    Join Date
    Oct 2007
    Posts
    31
    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
  •  



Click Here to Expand Forum to Full Width