unable to drop temp Tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: unable to drop temp Tablespace

  1. #1
    Join Date
    Jul 2002
    Posts
    132

    unable to drop temp Tablespace

    Hi,

    This is the scenario:
    The temp datafile was offline drop first.
    Then the .dbf file was deleted.
    Now unable to drop the temp tablespace. Getting error like

    drop tablespace TEMP INCLUDING CONTENTS;

    ERROR at line 1:
    ORA-00600: internal error code, arguments: [4344], [D], [3], [16777322], [],
    [], [], []

    Struckup in a loop. The temp datafile is in recover status. Please advice

  2. #2
    Join Date
    May 2001
    Posts
    736
    How can u drop a temp tablespace?.unless u create another tablespace as temp2 then only u can able to drop temp tablespace.Refer documentation for this as it says it is the default tablespace created while instalation along with system.

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

    You need to create a new temporary tablespace before dropping the default temporay tablespace.

    Create a new temporary tablespace
    Code:
    create temporary tablespace temp2
    tempfile 'e:\oracle\oradata\orcl\temp2_01.dbf' size 500M;
    then make it default temporary tablespace

    Code:
    alter database default temporary tablespace temp2;
    now try to drop the temp tablespace

    HTH
    Last edited by adewri; 02-25-2003 at 04:00 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by adewri
    Hi

    You need to create a new temporary tablespace before dropping the default temporay tablespace.

    Create a new temporary tablespace
    Code:
    create temporary tablespace temp2
    tempfile 'e:\oracle\oradata\orcl\temp2_01.dbf' size 500M;
    then make it default temporary tablespace

    Code:
    alter database default temporary tablespace temp2;
    now try to drop the temp tablespace

    HTH
    adewri:

    He shud have got ORA-12906: cannot drop default temporary tablespace, instead he is getting Internal error code, if he were to drop defualt temp tablespace????

    Will creating new Tablespace and making it defualt...& then droping other tablespace help????

    I dont think it will.....

    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"

  5. #5
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    hmmm... good point...
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

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

    Re: unable to drop temp Tablespace

    Originally posted by soniaarora
    ERROR at line 1:
    ORA-00600: internal error code, arguments: [4344], [D], [3], [16777322], [],
    [], [], []

    Please advice
    Contact Oracle Support Group
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  7. #7
    Join Date
    May 2001
    Posts
    736
    IT can.This what Oracle says.u need to have at least one temp tablespace.once u will create one more temporary tablespace as temp2 and drop the original temp tablespace all the users who are allocated to this temp tablespace will be allocate temp2 as the default temporaty tablespace.If u want u can test it.just now i did it successfully.

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by akhadar
    IT can.This what Oracle says.u need to have at least one temp tablespace.once u will create one more temporary tablespace as temp2 and drop the original temp tablespace all the users who are allocated to this temp tablespace will be allocate temp2 as the default temporaty tablespace.If u want u can test it.just now i did it successfully.
    I did try as well.....

    my point was ..... u cant get internal error when u try to drop temp tablespace with out having 1 more tablespace..... if so, ther might be some other probs asociated with it....
    all u get is ORA-12906 and not ORA-00600

    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"

  9. #9
    Join Date
    Oct 2000
    Posts
    139
    hi

    is it dictionary managed tablespace? if so you probably have corruption in data dictionary, issues with uset$ and fet$


    you can check if there are overlapping or duplicates in uet$ and fet$ by using following sql statements

    Overlap on FET$
    select f1.file#, f1.block#, f1.length, f2.block# from fet$ f1, fet$ f2
    where f1.file# = f2.file#
    and f1.rowid != f2.rowid
    and f1.block# <= f2.block#
    and f2.block# < f1.block#+f1.length ;


    Overlap on UET$
    select u1.file#, u1.block#, u1.length, u2.block#
    from uet$ u1, uet$ u2
    where u1.file# = u2.file#
    and u1.rowid != u2.rowid
    and u1.block# <= u2.block#
    and u2.block# < u1.block#+u1.length ;


    Duplicates
    select u1.file#, u1.block#, u1.length, f2.block#
    from uet$ u1, fet$ f2
    where u1.file# = f2.file#
    and u1.block# <= f2.block#
    and f2.block# < u1.block#+u1.length ;

    also check the trace files

  10. #10
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    other way round

    1. alter database backup controlfile to trace.
    2. shutdown immediate or normal.
    3. edit the create controlfile control file sript
    4. startup nomount
    5. recreate the control files.
    6. alter database open noresetlogs
    7. alter tablespace temp add tempfile 'absolute path' size 500M;

    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

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