Datafile name error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Datafile name error

  1. #1
    Join Date
    Mar 2006
    Posts
    3

    Datafile name error

    Hi all,
    details
    OS-HP_UX 11.11
    Oracle -9.2.0.6 64bit

    My query goes like this.
    a fellow dba has added a datafile to oracle database but when i query the name of datafile through views i get vierd characters

    eg:
    At oS level > /oracle/db/data10.dbf
    At oracle SQL PROMPT on server > /oracle/db/data10^?^?.dbf
    Through a other os via client > /oracle/db/data10.dbf=

    nOW I WANT TO rename it . I cant take tablespace offline and do a rename because the name doesnt match if i copy. i tried to copy file by command
    cp /oracle/db/data10.dbf /oracle/db2/data10.dbf
    i get error >> no such file exists
    if i use
    cp /oracle/db/data10* /oracle/db2/data10.dbf
    then it gets copied.

    Now what the only option i think would be create a new controlfile and it wud a downtime..... which i strictly want to avoid.....
    cant u advice me the best possible soln ......
    thnx

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Data file can be renamed or moved via 2 methods:
    1. alter database command
    do copy at os level
    startup mount
    alter database rename file 'old' to 'new';
    alter database open;

    2. alter tablespace command
    Take the tablespace offline;
    (in another session move or rename the old datafile)
    alter tablespace tablespace_name rename datafile 'old' to 'new' ;
    alter tablespace tablespace_name online;

  3. #3
    Join Date
    Mar 2006
    Posts
    3
    Thanks,

    But my problem is that when i query i get the filename different in putty/at server/through a different machine....... so i fear that when i do a rename file "old" as "new" won't that give an error for file does not exists
    when i took a controlfile trace it gives me the right name w/o junk characters ... so i thought that recreating controlfile wud do the trick.

  4. #4
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    If it gives an error, you can use the file number instead of file name
    i.e
    select file#,name from v$datafile
    alter database rename file THE FILE# FROM THE PREVIOUS QUERY to THE NEW NAME

    That cannot be mistaken

    Regards
    Boris

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    problem is, you've got non-printable characters in your file name. You could try using ctrl+v backspace to list out your files, but will be tedious to find the right combination.

    IMHO, I'd just create a new tablespace, move the objects from DATA to it, slap the other DBA on the head, and forget about it.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by marist89
    slap the other DBA on the head, and forget about it.
    Thanks for the Laugh!

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