unable to drop temp Tablespace

[soniaarora]

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

[akhadar]

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.

[adewri]

Hi

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

Create a new temporary tablespace

create temporary tablespace temp2
tempfile 'e:\oracle\oradata\orcl\temp2_01.dbf' size 500M;

then make it default temporary tablespace

alter database default temporary tablespace temp2;

now try to drop the temp tablespace

HTH

[abhaysk]

Originally posted by adewri
Hi

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

Create a new temporary tablespace

create temporary tablespace temp2
tempfile 'e:\oracle\oradata\orcl\temp2_01.dbf' size 500M;

then make it default temporary tablespace

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.

[adewri]

hmmm... good point...

[adewri]

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

Please advice

Contact Oracle Support Group

[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.

[abhaysk]

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.

[Sweetie]

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

[adewri]

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