Dropping Tablespaces with missing datafiles
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: Dropping Tablespaces with missing datafiles

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Posts
    55

    Dropping Tablespaces with missing datafiles

    Hi...

    Does anyone know how i can drop a tablespace when the actual data file assocaited with that tablespace is no longer there. Somehow the data file has been deleted but the tablespace still exists. When i use the normal drop command says it can't access the data file.

    I have seen this being done before when someone used SVRMGRL command and dropped the tablespace through there but can't wuite remeber how they did it any clues anyone?

    Thanks

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    No way. U have to restore datafile from dump and then drop tablespace.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    try

    alter tablespace offline immediate
    then drop

    or

    startup database in mount state

    alter database datafile 'missing XXXXXX' offline drop
    open database
    drop tablespace

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    As i know :
    alter tablespace ... offline;
    has to write to this tablespace :
    1) all uncommited transactions
    2) checkpoint number in datafile header block for each file in the tablespace.

    in this situation this command can't solve this problem.

    >>> alter database datafile 'missing XXXXXX' offline drop

    may solve this problem in noachivelog mode only if no one oracle objects or espesially
    part of object like tables, indexes ... no place in this datafile.but in any way in archivelog
    mode oracle will ignore drop clause.

    If i wrong - correct me.

  5. #5
    Join Date
    Aug 2001
    Posts
    267
    Make sure no transactions are pending

    Shutdown abort
    startup nomount
    alter database mount
    alter database datafile ' ' offline drop
    open database
    drop tablespace ' '

    should work as pando said
    Raghu

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Hi Shestakov

    Note I said immediate keyword, issueing offline immediate doesnt force checkpoint, i.e no need to write the datafiles

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by pando

    Note I said immediate keyword, issueing offline immediate doesnt force checkpoint, i.e no need to write the datafiles
    Yes, I agree, with immediate Oracle will not force checkpoint for the tablespace.

  8. #8
    Join Date
    Aug 2001
    Posts
    267
    I guess if we want use offline immediate the media recovery should be enabled right? . Correct me if I am wrong.
    Raghu

  9. #9
    Join Date
    Aug 2001
    Posts
    267
    Sorry you are asking to put, alter tablespace offline immediate, but not datafile. Media recovery need not be enabled.
    Raghu

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: Dropping Tablespaces with missing datafiles

    Originally posted by hummy
    Hi...

    Somehow the data file has been deleted but the tablespace still exists. When i use the normal drop command says it can't access the data file.

    Thanks
    This sounds almost impossible.....

    When the tablespace is online it would have obiviously locked all its datafiles....& when file is locked at OS level you just cant delete/rename/move the file.

    I wonder how on earth ( may it be any OS ) lets you delete file when locked?


    Can any one tell how this can be done...Tried to simulate the problem but it seems almost impossible.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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