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: EdStevens <quetico_man_at_yahoo.com>
Date: 18 Oct 2006 08:02:04 -0700

<div class="mail">
<address class="headers">
<span id="from">
<dfn>From</dfn>: EdStevens <quetico_man_at_yahoo.com>
</span>
<span id="date"><dfn>Date</dfn>: 18 Oct 2006 08:02:04 -0700</span>
<span id="message-id"><dfn>Message-ID</dfn>: <1161183724.475301.188710_at_b28g2000cwb&#46;<!--nospam-->googlegroups.com>
</span>
</address>

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">> 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>

did you remember to alter every user to make their temp tablespace the 'new' one while you drop and recreate the old one? That's a separate operation from simply making the new one the 'default'.
<span id="received"><dfn>Received on</dfn> Wed Oct 18 2006 - 10:02:04 CDT</span>
</div>
Received on Wed Oct 18 2006 - 10:02:04 CDT

Original text of this message

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