Re: Cleaning up TEMP tablespace.
- From: Chirag DBA <ChiragDBA@xxxxxxxxx>
- To: frank4oraclel@xxxxxxxx
- Date: Wed, 27 Oct 2004 11:34:12 +0530
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@xxxxxxxx> 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
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
