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

Home -> Community -> Usenet -> c.d.o.server -> Re: drop temp tablespace takes forever

Re: drop temp tablespace takes forever

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 18 Oct 2006 08:21:02 -0700

<div class="mail">
<address class="headers">
<span id="from">
<dfn>From</dfn>: DA Morgan <damorgan_at_psoug.org>
</span>
<span id="date"><dfn>Date</dfn>: Wed, 18 Oct 2006 08:21:02 -0700</span>
<span id="message-id"><dfn>Message-ID</dfn>: <1161184857.180844_at_bubbleator&#46;<!--nospam-->drizzle.com>
</span>
</address>

<br>

maxim2k wrote:
<br>
<i class="quotelev1">> Hi,</i><br>
<i class="quotelev1">> </i><br>
<i class="quotelev1">> I'm running Oracle Database 10g R2 on Red Hat Enterprise Linux 4.</i><br>
<i class="quotelev1">> </i><br>
<i class="quotelev1">> For some reason the temp01.dbf file has grown to 32GB lately!</i><br>
<i class="quotelev1">> </i><br>
<i class="quotelev1">> So in order to bring it down, I tried to follow the directions in the </i><br>
<i class="quotelev1">> following article:</i><br>
<i class="quotelev1">> </i><br>
<i class="quotelev1">> <http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml#Dropping%20/%20Recreating%20Temporary%20Tablespace%20Method> </i><br>
<i class="quotelev1">> </i><br>
<i class="quotelev1">> </i><br>
<i class="quotelev1">> Here is the suggested process:</i><br>
<i class="quotelev1">> </i><br>
<i class="quotelev1">> - create a new support temporary tablespace and make it the default for </i><br>
<i class="quotelev1">> the database</i><br>
<i class="quotelev1">> </i><br>
<i class="quotelev1">> - drop the original TEMP tablespace</i><br>
<i class="quotelev1">> </i><br>
<i class="quotelev1">> - recreate a new TEMP tablespace, make it the default and drop the </i><br>
<i class="quotelev1">> support tablespace</i><br>
<i class="quotelev1">> </i><br>
<i class="quotelev1">> But I'm stuck at the dropping the original TEMP tablespace with the </i><br>
<i class="quotelev1">> followinf command:</i><br>
<i class="quotelev1">> </i><br>
<strong><i class="quotelev1">> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;</i><br>
</strong>
<i class="quotelev1">> </i><br>
<i class="quotelev1">> The above command has been running for an hour or so on my server and </i><br>
<i class="quotelev1">> didn't finish yet.</i><br>
<i class="quotelev1">> </i><br>
<i class="quotelev1">> Is it supposed to be so slow?</i><br>
<i class="quotelev1">> </i><br>
<i class="quotelev1">> How can I check its progress?</i><br>
<i class="quotelev1">> </i><br>
<i class="quotelev1">> If I need to, how do I kill it without corrupting the database?</i><br>
<i class="quotelev1">> </i><br>
<i class="quotelev1">> Thanks.</i><br>

Temporary tablespaces are not datafiles ... they are tempfiles. Go to tahiti.oracle.com and look up the correct syntax.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x&#46;<!--nospam-->washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

<span id="received"><dfn>Received on</dfn> Wed Oct 18 2006 - 10:21:02 CDT</span>
</div>
Received on Wed Oct 18 2006 - 10:21:02 CDT

Original text of this message

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