Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: [Q] How to online drop tempfile ?
I prefer to use DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES followed by a CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
Note : In 9i and above, your database would have a default temporary
tablespace. You must
issue an ALTER DATABASE DEFAULT TEMPORARY TABLESPACE newtemp before
you can drop temp
Hemant
At 10:58 PM Friday, you wrote:
>I am doing 9ir2 hot backup/recovery test. I got
>problem on tempfile (temporary tablespace)
>drop problem.
>
>I can drop tempfile under "mount" mode, but NOT under
>database open:
>
>SQL> startup mount
>ORACLE instance started.
>
>Total System Global Area 202880128 bytes
>Fixed Size 451712 bytes
>Variable Size 83886080 bytes
>Database Buffers 117440512 bytes
>Redo Buffers 1101824 bytes
>Database mounted.
>SQL> alter database tempfile
>'/apps/oradata/eaii/temp01.dbf' drop;
>
>Database altered.
>
>SQL> alter database open;
>
>If I use same command when database open. It will
>hang in there for ever.
>
>Can anyone tell me how to drop tempfile when database
>open?
>
>Thanks.
>
>
>
>
>
>__________________________________
>Do you Yahoo!?
>Yahoo! Mail is new and improved - Check it out!
>http://promotions.yahoo.com/new_mail
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
http://web.singnet.com.sg/~hkchital
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Sun Aug 29 2004 - 04:31:45 CDT
![]() |
![]() |