Re: drop temp tablespace takes forever

<div class="mail">
<address class="headers">
<span id="from">
<dfn>From</dfn>: Brian Peasland <dba_at_nospam.peasland.net>
</span>
<span id="date"><dfn>Date</dfn>: Wed, 18 Oct 2006 14:59:53 GMT</span>
<span id="message-id"><dfn>Message-ID</dfn>: <J7C6zw.JHM_at_igsrsparc2&#46;<!--nospam-->er.usgs.gov>
</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>

If users are actively using the tablespace, the DROP command will wait for those sessions to release their locks on that tablespace. How long this takes depends on the session's activity.

<strong>HTH,
</strong>
<br>

Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam&#46;<!--nospam-->peasland.net


<a href="http://www.peasland.net">http://www.peasland.net</a>


Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown


<span id="received"><dfn>Received on</dfn> Wed Oct 18 2006 - 09:59:53 CDT</span>

</div>