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

Thread: DB files

  1. #1
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995

    DB files

    Gals/girls.
    I messed up and I hope there's some ancient DBA secret that I don't know about to fix it, hence the question before the community.

    Here's the scenario....
    I'm in my database cleaning up some tablespaces that are no longer required. Since Oracle won't remove the datafile(s) associated with the tablespace, once you remove the tablespace you have to go into the OS and rm the datafile, and that's exactly what I was doing. However... during my datafile removal I got a little to presumptuous and deleted a datafile that was still being used by an existing tablespace. Even though the datafile I deleted was going to get blown away, I did the removal backwards therefore causing the tablespace to freak out. Now, since the datafile is gone, Oracle won't let me blow the tablespace away because he can't get a lock on the datafile.

    Does anyone know of a way to remedy this situation other than backing the controlfile to trace, editing the file to remove said tablespace and associated datafile(s), shutdown the database, blow away existing controlfiles, crank database back up with edited controlfile?

    Thanks!
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    That's a classic "media failure", so you could restore the file . . . though the control file method could be quicker.

    In 9i you can drop a ts INCLUDING CONTENTS AND DATAFILES

    Of course, some OS don't let you delete a file that's in use . . . .

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796

    Re: DB files

    Originally posted by OracleDoc
    Now, since the datafile is gone, Oracle won't let me blow the tablespace away because he can't get a lock on the datafile.
    r u sure ??

    Code:
    sys@DELDB.WORLD> create tablespace del_me datafile '/perfdb2/oradata/DELDB/del_me_01.dbf' size 10M;
    
    sys@DELDB.WORLD> select name from v$datafile;
    
    NAME
    ----------------------------------------------------------------------------------------------------
    /perfdb2/oradata/DELDB/system01.dbf
    /perfdb2/oradata/DELDB/undotbs01.dbf
    /perfdb2/oradata/DELDB/indx01.dbf
    /perfdb2/oradata/DELDB/tools01.dbf
    /perfdb2/oradata/DELDB/users01.dbf
    /perfdb2/oradata/DELDB/del_me_01.dbf
    
    6 rows selected.
    
    oracle(DELDB):/perfdb2/oradata/DELDB>
    > ls
    del_me_01.dbf  redo01.log     redo03.log     temp01.dbf     undotbs01.dbf
    indx01.dbf     redo02.log     system01.dbf   tools01.dbf    users01.dbf
    oracle(DELDB):/perfdb2/oradata/DELDB>
    > mv del_me_01.dbf del_me_01.dbf.old
    oracle(DELDB):/perfdb2/oradata/DELDB>
    > ls
    del_me_01.dbf.old  redo01.log         redo03.log         temp01.dbf         undotbs01.dbf
    indx01.dbf         redo02.log         system01.dbf       tools01.dbf        users01.dbf
    
    sys@DELDB.WORLD> drop tablespace del_me;
    
    Tablespace dropped.
    
    sys@DELDB.WORLD> select name from v$datafile;
    
    NAME
    ----------------------------------------------------------------------------------------------------
    /perfdb2/oradata/DELDB/system01.dbf
    /perfdb2/oradata/DELDB/undotbs01.dbf
    /perfdb2/oradata/DELDB/indx01.dbf
    /perfdb2/oradata/DELDB/tools01.dbf
    /perfdb2/oradata/DELDB/users01.dbf
    
    sys@DELDB.WORLD>
    This was on Unix... Oracle9i
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  4. #4
    Join Date
    May 2001
    Posts
    736
    What i will do in this case is

    1.Recreate the user tablespace.If all the objects in the tablespace can be re-created (recent export is available, tables can be re-populated using scripts or through SQL*Loader)Then, offline drop the datafile, drop the tablespace with including contents option.

    2.Restore file from backup and do a media recovery.Then u can drop the tablespace as per the normal procedure.

    Another useful link is

    http://metalink.oracle.com/metalink/...atabase_id=NOT
    Last edited by akhadar; 07-13-2004 at 04:47 AM.

  5. #5
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    LOL good Lord akhadar that's a lot of work...

    Ok, I forgot to mention it's 8i and on Sun. Adewri, I did exactly that and it still wouldn't do drop it. I'm think'n it's because I'm on 8i.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    alter database datafile 'XXX' offline drop;

    drop tablespace xxx;

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