Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: [Q] How to online drop tempfile ?

Re: [Q] How to online drop tempfile ?

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Sun, 29 Aug 2004 17:30:38 +0800
Message-Id: <6.1.2.0.0.20040829172914.02468fb8@pop.singnet.com.sg>

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                



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
-----------------------------------------------------------------
Received on Sun Aug 29 2004 - 04:31:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US