unable to rename a datafile
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: unable to rename a datafile

Hybrid View

  1. #1
    Join Date
    Apr 2002
    Posts
    291

    unable to rename a datafile

    Hi all,
    I'm running oracle 9.0.1 on linux 6.2. I was adding a datafile to a tablespace and was wrongly typed the name of the datafile. I thought of renaming the file and tried to took the tablespace offline.I got an error ORA-01658,"unable to create INITIAL extent for segment in tablespace SYSTEM", this tablespace has got only 300M left over, and even that directory doesn't got much physical space on disk to increase the system TS size. Can you please help me.

    Thanks in advance
    PNRDBA

  2. #2
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Use ALTER TABLESPACE ADD DATAFILE to add additional space to the SYSTEM tablespace on a relatively free drive.

    HTH.
    Last edited by ggnanaraj; 11-07-2002 at 06:50 AM.

  3. #3
    Join Date
    Apr 2002
    Posts
    291
    That i can do, but the thing is, i dunno have space on any other disks even. and one more thing, the file is around 2000M which i want to rename, so do i need to create another 2000M for SYSTEM too???
    PNRDBA

  4. #4
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Re: unable to rename a datafile

    Originally posted by pnrdba

    I was adding a datafile to a tablespace and was wrongly typed the name of the datafile. I thought of renaming the file and tried to took the tablespace offline.

    On second thought, are your sure you get this ORA error when trying to offline the tablespace and not when creating the datafile?

    Well, you could just drop the datafile and create it again with a name you want.

    Moreover, you need to have a smaller value for INITIAL for the SYSTEM tbs to solve the ORA error you get.

    HTH.
    Last edited by ggnanaraj; 11-07-2002 at 07:29 AM.

  5. #5
    Join Date
    Apr 2002
    Posts
    291
    On second thought, are your sure you get this ORA error when trying to offline the tablespace and not when creating the datafile?

    Yes, i'm sure . I got the ORA error when i was trying to bring the TS offline. So now can i drop the DF and re-create with the desired name?

    Thanks
    PNRDBA

  6. #6
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    can i drop the DF and re-create with the desired name?
    Yea, that should be ok, unless you have created some objects on that datafile.

  7. #7
    Join Date
    Apr 2002
    Posts
    291
    Nothing is there in that DF. but is it ok to do this job online, when the users are working?
    PNRDBA

  8. #8
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you CANNOT drop a datafile from a tablespace

  10. #10
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Workaround
    ----------
    It is possible to resize the datafile to 1K. Then no objects should get created in the datafile.

    You cannot drop individual datafiles.

    If the tablespace contains recreatable objects eg indexes, then
    1) offline the tablespace
    2) drop the tablespace including contents
    3) recreate the tablespace
    4) recreate the objects

    If the tablespace contains tables

    1) export all the objects in the tablespace
    2) drop the tablespace including contents
    3) recreate tablespace
    3) import the objects

    OFCOURSE, TAKE A FULL BACKUP before you do any of the above. You will also have a downtime. So, I suppose the workaround is better.
    Last edited by ggnanaraj; 11-07-2002 at 08:30 AM.

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