Re: Cleaning up TEMP tablespace.

that is a the usual behaviour that it shows the temp tablespace full.

Nothing to worry.

U just create another temp tablespace and assign that to that particulat user.

• Chiku

On Tue, 26 Oct 2004 15:33:48 +0200 (CEST), Frank B Hansen <frank4oraclel_at_yahoo.dk> wrote:
> Hi List
>
> On one of our databases, the Temp TS is currently 13,5 GB.
>
> The tablespace is type TEMPORARY.
>
> DB version is 9.2.0.4.0 running on AIX RS 6000.
>
> It is a produktion database and bouncing the database should be avoided if possible.
>
> SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status
>
> FROM v$session a,v$sort_usage b
>
> WHERE a.saddr = b.session_addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
>
> Returns no rows.
>
> I know that since the TEMP TABLESPACE is of type TEMPORARY, the space is not released until the instance is shutdown.
>
> SMON only cleans up when the TABLESPACE is of type PERMANENT.
>
> There are 2 options, that I would like an opinion on.
>
> 1. Bounce the database.
>
> 2. Running SQL>alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1';
>
> Will the event take forever or can I expect it to finish within 1 hour ? Option 2 is what I would recommend in
>
> situations where the size is more reasonable - say up to 1GB.
>
> A bonus question:
>
> What does a 13,5 GB large datafile / TEMP tablespace do to performance ? Will it be significant or is it merely a
>
> space issue on the SAN.
>
> Thanks in advance,
>
> Frank B Hansen
>
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l