rename datafile created with special charactor
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: rename datafile created with special charactor

  1. #1
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330

    Angry rename datafile created with special charactor

    Need help to rename the datafile created with spl char at the end like ***index03.dbf\12. I am unable to query the views for this file too. Please help.
    Thanks.
    Thanigaivasan.
    Thanigaivasan

  2. #2
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    In sqlplus: SQL> set escape \

    Escape the special chars with \ and rename file.
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  3. #3
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    Hi Kris,
    Thanks and when I try this...
    SQL> set escape \
    SQL>select name from v$datafile where name like '/u12/oradata/trps/ts_incdnt_txt_procs_index_03.dbf'
    no row selected
    but....for
    SQL> select name from v$datafile where name like '%txt_procs_index_03%';
    NAME
    --------------------------------------------------------------------------------
    /u12/oradata/trps/ts_incdnt_txt_procs_index_03.dbf SQL> select name from v$datafile where name like '%txt_procs_index_03.dbf';
    no rows selected
    Why it is not recognizing the file. Am I wrong?.
    Thanks,

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Try ****ndex03.dbf\\12(Escape the char '\') after switching escape on in sqlplus.
    Reddy,Sam

  5. #5
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    Not working for me...
    SQL> set escape on
    SQL> select name from v$datafile where name like '%ndex03.dbf\\12';

    no rows selected

    SQL> c/%/***
    1* select name from v$datafile where name like '***ndex03.dbf\\12'
    SQL> /
    no rows selected

    Thanks,
    Thanigaivasan.

  6. #6
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    Any update on this?.
    Thanks,
    Thanigaivasan.

  7. #7
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330

    Thumbs up

    Found the solution...for this. So thought of sharing with all.

    do at os level..
    cp file_name.dbf* file_name.dbf
    now you will have two files in same directory..
    ls -lrtb
    -------- 2040 file_name.dbf\12
    -------- 2040 file_name.dbf
    at sqlplus...
    SQL>alter database rename datafile 'file_name.dbf
    ' to 'file_name.dbf';

    SQL>select file_name from dba_data_files;
    you are fine now...

    Please note the "'" at the end of file_name.dbf is in 2nd line...
    that way it matches the file_name and allows to rename the file...
    to do that after typing file_name.dbf(after typing f in dbf) press contrl j. that generates second line and allows to rename. It worked to select the file from dba_data_files too.

    I did not mention all the pre steps like shutdown/startup mount etc...because we all know that. And also we know renaming is possible in 9i in open mode too.
    Thanks,
    Thanigaivasan.

  8. #8
    Join Date
    Oct 2003
    Posts
    65
    hello...

    very simple....dont do much..just do simple stuffs.

    make the tablespace readonly or if you can shutdown do it.

    copy the file from wrongfilename to newfile name.

    bring the database up if you have shutdown or else make the tablespace online.

    Take the file#(file number)
    then rename with the file number.

    like

    alter database rename 1 to index1.dbf

    this will work.

    cheers

  9. #9
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    Hi Bolero,
    The renaming in open mode is a trick...Actual steps are..
    1.alter tablespace XYZ read only;
    2.cp the file to new file in OS level
    3.alter tablespace XYZ offline
    4. aleter database rename datafile 'file_name' to 'new_file_name';
    5.alter tablespace XYZ online;
    6. alter tablespace XYZ read write;
    7. Check the status of XYZ tablespace by querying dba_tablespaces.
    8. Check file status from dba_data_files.

    we cannot rename datafile without making the tablespace offline...in open mode.
    Did you try renaming the datafile using file# any time?. IS this the correct syntax please?.

    Thanks,
    Thanigaivasan.

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