Re: TEMP tablespace again

From: Vadim Grepan <kezal_at_mail.ru>
Date: Wed, 14 Mar 2001 14:27:34 +0300
Message-ID: <3AAF55A5.A4B0D128@mail.ru>

Hello Jonathan !

  Thanks for your hint, news segments are allocated with new size. But there are two large segments owned by SYS created before. Is there any way to drop or decrease them?

Rgds, Vadim Grepan

---

Moscow, Russia

Jonathan Lewis wrote:

> Did you also change the MINIMUM EXTENT when you
> changed the (initial next). The presence of the minimum
> requires every extent to be rounded up to a multiple of
> that value.
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
> Publishers: Addison-Wesley
>
> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
> Vadim Grepan wrote in message <3AAF318D.21C48D58_at_mail.ru>...
> >Hello!
> >
> > I have TEMP temporary tablespace with creating parameter ..MINIMUM
> >EXTENT 32M
> > DEFAULT STORAGE (INITIAL 32M NEXT 32M ..). So every session
> >allocates
> >32M, it's too much. I'd like to decrease INITIAL/NEXT parameters and
> >used clause
> > alter tablespace temp default storage (initial 1M next 1M);
> >Unfortunatly it doesn't work for some reason and sessions allocate 32M
> >as usuall. After that
> >I decided to re-create this space:
> >
> >### 1. check for using tablespace:
> >SQL> l
> > 1 SELECT substr (s.username,1,10),
> > 2 substr (u.tablespace,1,15), u.contents, u.extents, u.blocks
> > 3 FROM v$session s, v$sort_usage u
> > 4* WHERE s.saddr=u.session_addr
> >SQL> /
> >no rows selected
> >
> >### 2. offline space
> >SQL> alter tablespace temp offline;
> >
> >Tablespace altered.
> >
> >### 3. drop space
> >SQL> drop tablespace temp including contents;
> >[..... wait a lot of time .....]
> >^Cdrop tablespace temp including contents
> >*
> >ERROR at line 1:
> >ORA-01013: user requested cancel of current operation
> >
> >So how can I recreate/change parameter for this tempoprary tablespace?
> >
> >TFHAOT
> >
> >Rgds, Vadim Grepan
> >------------------------
> >Moscow, Russia
> >
> >
> >
> >
> >
Received on Wed Mar 14 2001 - 05:27:34 CST