Home » RDBMS Server » Server Administration » TEMPFILE cannot be dropped at this time (Oracle Database 9.2.0.5 on Redhat Linux 9)
TEMPFILE cannot be dropped at this time [message #324784] Wed, 04 June 2008 00:21 Go to next message
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.
Re: TEMPFILE cannot be dropped at this time [message #324786 is a reply to message #324784] Wed, 04 June 2008 00:24 Go to previous messageGo to next message
BlackSwan
Messages: 25048
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
Re: TEMPFILE cannot be dropped at this time [message #324790 is a reply to message #324784] Wed, 04 June 2008 00:40 Go to previous messageGo to next message
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.
Re: TEMPFILE cannot be dropped at this time [message #324793 is a reply to message #324784] Wed, 04 June 2008 00:44 Go to previous message
BlackSwan
Messages: 25048
Registered: January 2009
Location: SoCal
Senior Member
OK
You lose!
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:
  


Current Time: Fri Dec 09 09:47:50 CST 2016

Total time taken to generate the page: 0.05380 seconds