Re: HELP ! ! ! temp tablespace

You are out of luck !! You will have to let the DROP TABLESPACE complete. Although u think that the command is hung, it is working for you. The reason it is taking so long is that because of the size of your temp tablespace, you might have hundreds of thousands of extents in that tablespace and if it is not locally managed tablespace then oracle needs to adjust the uet$ and fet$ tables and a whole lot of other things. Now this is happening on system tablespace and that is why you might be getting very poor performance for everything else. Even if you do a shutdown abort, and do a startup, oracle will continue to finish its work of dropping the tablespace. My advise for what its worth - Let the thing finish. You can stay assured that the drop tablespace is running and if u call Oracle support, they can help you trace oracle and make sure that the drop tablespace is running. Additionally, keep looking at uet$ and fet$ tables for this tablespace. And - do not do a lot of shutdown abort.

Hope this helps.

Ketan.
"Hans Forbrich" <forbrich_at_telusplanet.net> wrote in message news:3AE91807.2D46AE03_at_telusplanet.net...
> If you've got a legal copy, you probably have support - and that is the
 type of
> question for Oracle support. Also try http://technet.oracle.com
 discussion
> groups (actually monitored by support as well).
>
> One of the things that may have botched you - you can't simply move a
 datafile -
> you have to 'alter tablespace rename datafile' (I don't remember exact
 syntax -
> sorry)
>
> /Hans
>
> ahmed kashif wrote:
>
> > my temp (temporary) tablespace grew to ~14g (dont know why it was set to
> > unlimited?) - ended up taking all the disk space. I moved it to another
 disk
> > and brought it online, but then it wouldn't let me shutdown the database
> > with the exception of shutdown abort. i tried to drop it with drop
> > tablespace command, but it always seemed like it hung the database. i
 even
> > let it go for hours - no luck. then i created a new tablespace 'temp2',
 and
> > made that tablespace the temporary tablespace for all the users. then i
> > shutdown (abort) the database and removed that sucker from the
 filesystem.
> > brought the database up with no problem. then i tried again to drop it
> > throught drop tablespace, but it still does the same - hang the database
 on
> > me.
> >
> > i still can see that tablespace in dba_tablespaces, and i still can't
> > shutdown normal or immediate. please HELP!!!
> >
> > --
> >
> > Thanks,
> >
> > ahmed
>
Received on Sun May 06 2001 - 04:54:46 CEST