-
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
-
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.
-
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."
-
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"
-
Amar
"There is a difference between knowing the path and walking the path."
-
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."
-
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.
-
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"
-
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
-
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."
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|