OraFAQ Forum: Server Administration » TEMPFILE cannot be dropped at this time

Home » RDBMS Server » Server Administration » TEMPFILE cannot be dropped at this time (Oracle Database 9.2.0.5 on Redhat Linux 9)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

TEMPFILE cannot be dropped at this time [message #324784]

Wed, 04 June 2008 00:21

laksha
Messages: 42
Registered: June 2006

Member

One of the drives on my server having the tempfile is becoming full and I want to delete the tempfile on this drive and I will create aditional tempfile in other drive.

I tried using both these commands

ALTER DATABASE TEMPFILE '/u01/temp02.dbf' DROP

ALTER DATABASE TEMPFILE '/u01/temp02.dbf' DROP INCLUDING DATAFILES;

but this gives me error

Quote:

ORA-25152: TEMPFILE cannot be dropped at this time

and then the tempfile goes offline.

I can use Dropping / Recreating Temporary Tablespace Method.

But i dont know why this droping tempfile doesnt work.

Report message to a moderator

Re: TEMPFILE cannot be dropped at this time [message #324786 is a reply to message #324784]

Wed, 04 June 2008 00:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

If you are desperate, you can do:
shutdown abort
startup restrict
ALTER DATABASE TEMPFILE '/u01/temp02.dbf' DROP INCLUDING DATAFILES;
shutdown immediate
startup

Report message to a moderator

Re: TEMPFILE cannot be dropped at this time [message #324790 is a reply to message #324784]

Wed, 04 June 2008 00:40

laksha
Messages: 42
Registered: June 2006

Member

I tried this drop command both by keeping the tempfile offline as well as online. Does'nt keeping this tempfile offline means no transaction is using this particular tempfile and so it can be dropped.

Report message to a moderator

Re: TEMPFILE cannot be dropped at this time [message #324793 is a reply to message #324784]

Wed, 04 June 2008 00:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

OK
You lose!

Report message to a moderator

Previous Topic:	how to convert a latin-1 db to utf-8 database.
Next Topic:	ORA-01045: user SYSTEM lacks CREATE SESSION privilege; logon denied

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 09 03:40:33 CDT 2024