Re: drop temp tablespace takes forever

barthome1_at_comcast.net wrote:
> I am running into a similar problem deleting rows from a table. My
> sequence of events works fine on Solaris running a 9 proC client
> against a 9 database. But on Windows with a 10 client and the same 9
> database, deleting rows just hangs the process.
>
> My code has been ported to run on Windows, Solaris and Red Hat. So I
> am running the same thing on all platforms. Everything should be the
> same execpt the particular incarnation of the proC librararies.
>
> Anybody got ideas?
>
> Bart
> bart.torbert_at_ihsenergy.com
>
> maxim2k wrote:
> > Hi,
> >
> > I'm running Oracle Database 10g R2 on Red Hat Enterprise Linux 4.
> >
> > For some reason the temp01.dbf file has grown to 32GB lately!
> >
> > So in order to bring it down, I tried to follow the directions in the
> > following article:
> >
> > <http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml#Dropping%20/%20Recreating%20Temporary%20Tablespace%20Method>
> >
> > Here is the suggested process:
> >
> > - create a new support temporary tablespace and make it the default for
> > the database
> >
> > - drop the original TEMP tablespace
> >
> > - recreate a new TEMP tablespace, make it the default and drop the
> > support tablespace
> >
> > But I'm stuck at the dropping the original TEMP tablespace with the
> > followinf command:
> >
> > DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
> >
> > The above command has been running for an hour or so on my server and
> > didn't finish yet.
> >
> > Is it supposed to be so slow?
> >
> > How can I check its progress?
> >
> > If I need to, how do I kill it without corrupting the database?
> >
> > Thanks.

Hi Bart,

Turn on trace for the offending sessions and it should show you the event on which it is waiting. Search for oracle 10046 on Google or in the docs.

HTH, Steve Received on Wed Oct 18 2006 - 10:31:03 CDT