Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    28

    Wink Unanswered: rename a datafile .dbf

    we had created 2 datafiles for one space table which has the same name in the same directory

    let's see the following results

    select

    FILE_ID FILE_NAME TABLESPACE_NAME
    ---- ------------------------------- -----------------
    95 /database/COEADWH/data10/dwh19ut01.dbf DWH19UT
    108 /database/COEADWH/data10/dwh19ut02.dbf DWH19UT
    109 /database/COEADWH/data10/dwh19ut03.dbf DWH19UT
    110 /database/COEADWH/data10/dwh19ut04.dbf DWH19UT
    111 /database/COEADWH/data10/dwh19ut05.dbf DWH19UT
    112 /database/COEADWH/data10/dwh19ut06.dbf DWH19UT
    342 /database/COEADWH/data09/dwh19ut09.dbf DWH19UT
    343 /database/COEADWH/data09/dwh19ut09.dbf DWH19UT
    522 /database/COEADWH/data10/dwh19ut07.dbf DWH19UT
    523 /database/COEADWH/data10/dwh19ut08.dbf DWH19UT


    I know that we can rename files but how to rename juste the one which has file_id equal to 343 ??

    Here there are how to do in normal case :

    tablespace DWH19UT OFFLINE

    in UNIX command do
    mv /database/COEADWH/data09/dwh19ut09.dbf /database/COEADWH/data09/dwh19ut11.dbf

    under SQLPLUS SOFTWARE /system user do

    alter tablespace DWH19UT rename datafile '/database/COEADWH/data09/dwh19ut09.dbf' to '/database/COEADWH/data09/dwh19ut10.dbf' ;

    alter tablespace DWH19UT online;

    but there are two datafile which name is '/database/COEADWH/data09/dwh19ut09.dbf'
    one with file_id = 342 and another with file_id = 343 !!
    how to rename just one of these 2 files !!

    thanks 4 U'r reply !! )

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >but there are two datafile which name is '/database/COEADWH/data09/dwh19ut09.dbf'
    HUH?
    It appears that Oracle believes that two files (#342 & #343) exists.
    Does Unix really acknowledge that both exist?
    AFAIK, Unix does NOT allow two files of the same name to concurrently exist in the same directory.
    ==============================
    Can you actually get Oracle to retrieve all objects out of the DWH19UT tablespace?
    I suspect you have a MAJOR problem to resolve. :-(
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2003
    Posts
    28

    rename a datafile.dbf

    ok thank's 4 u're reply

    But just let me to show you this :

    it's SUN system (not AIX system)
    And this is when ls -l is launched !!

    wsdscef1d2root):/database/COEADWH/data09 # ls -lib *
    17 -rw-r----- 1 oracle dba 525352960 Jun 23 09:32 dwh04ut12.dbf
    15 -rw-r----- 1 oracle dba 525352960 Jun 23 09:32 dwh08ui26.dbf
    10 -rw-r----- 1 oracle dba 525352960 Jun 23 09:31 dwh08ut08.dbf
    7 -rw-r----- 1 oracle dba 2884648960 Jun 23 09:31 dwh08ut_040501.dbf
    11 -rw-r----- 1 oracle dba 2622504960 Jun 23 09:31 dwh08ut_040601.dbf
    18 -rw-r----- 1 oracle dba 2622504960 Jun 23 09:32 dwh08ut_040701.dbf
    6 -rw-r----- 1 oracle dba 525352960 Jun 23 09:31 dwh10ui72.dbf
    8 -rw-r----- 1 oracle dba 4195368960 Jun 20 18:42 dwh10ut_040501.dbf
    16 -rw-r----- 1 oracle dba 525352960 Jun 23 09:32 dwh10ut_040502.dbf
    12 -rw-r----- 1 oracle dba 4195368960 Jun 23 09:31 dwh10ut_040601.dbf
    19 -rw-r----- 1 oracle dba 4195368960 Jun 23 09:32 dwh10ut_040701.dbf
    4 -rw-r----- 1 oracle dba 735068160 Jun 23 09:32 dwh16at01.dbf
    20 -rw-r----- 1 oracle dba 525352960 Jun 23 09:32 dwh17ut_000501.dbf
    21 -rw-r----- 1 oracle dba 525352960 Jun 23 09:32 dwh17ut_000601.dbf
    22 -rw-r----- 1 oracle dba 525352960 Jun 23 09:32 dwh17ut_000701.dbf
    23 -rw-r----- 1 oracle dba 525352960 Jun 23 09:32 dwh17ut_000801.dbf
    24 -rw-r----- 1 oracle dba 525352960 Jun 23 09:32 dwh17ut_000901.dbf
    25 -rw-r----- 1 oracle dba 525352960 Jun 23 09:32 dwh17ut_001001.dbf
    26 -rw-r----- 1 oracle dba 525352960 Jun 23 09:32 dwh17ut_001101.dbf
    27 -rw-r----- 1 oracle dba 525352960 Jun 23 09:32 dwh17ut_001201.dbf
    28 -rw-r----- 1 oracle dba 525352960 Jun 23 09:32 dwh17ut_010101.dbf
    29 -rw-r----- 1 oracle dba 525352960 Jun 23 09:33 dwh17ut_030201.dbf
    9 -rw-r----- 1 oracle dba 1573928960 Jun 23 09:31 dwh17ut_040501.dbf
    13 -rw-r----- 1 oracle dba 1573928960 Jun 23 09:31 dwh17ut_040601.dbf
    35 -rw-r----- 1 oracle dba 1573928960 Jun 23 09:32 dwh17ut_040701.dbf
    36 -rw-r----- 1 oracle dba 525352960 Jun 23 09:32 dwh19ut09.dbf
    37 -rw-r----- 1 oracle dba 525352960 Jun 23 09:32 dwh19ut09.dbf
    38 -rw-r----- 1 oracle dba 10502144 Jun 23 09:32 mikevwtst01.dbf
    39 -rw-r----- 1 oracle dba 10502144 Jun 21 19:22 te

    there are really two different files with the really same name !!

    unless a no visible special character is in the name itself !! blank or something else !!

    I think that the only solution is to

    EXPORT tables which are in the SPACE TABLE DWH19UT
    Drop the existing DWH19UT SPACE TABLE
    Create a New DWH19UT SPACE TABLE with 10 datafiles
    import tables into the new DWH19UT SPACE TABLE !!

    Thank's 4 U're reply

    Nice to meet U ))

    Bye

    Sidnet !!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    ls -lb will display any "unprintable" characters in the filename
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2004
    Posts
    370
    I agree you should try exporting the tables.

    Have you done a fsck on the filesystem?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •