Re: temp tablespace questions....

From: Hemant K Chitale <hkchital_nospam_at_singnet_nospam.com.sg>
Date: Tue, 5 Mar 2002 22:36:24 +0800
Message-ID: <a62lbb$s51$1@coco.singnet.com.sg>

You could have a CRON job (or dbms_job)
which does an ALTER TABLESPACE TEMP DEFAULT STORAGE (PCTINCREASE 0) at midnight every day(night ?). This would shrink the size of the TEMPORARY segment back to 0. Then also add an ALTER DATABASE DATAFILE <filename> RESIZE 100M; running with it.

Hemant K Chitale

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:3c846633.319999515_at_news.saix.net...
> cmercer_at_vibrant-1.com (Carl Mercer) wrote:
>
> >I have a 9.0.1.2.0 instance that has a temporary tablespace (duh...).
> >The tablespace was created as follows:
> >
> >CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
> >'/dev_tools/oracle/product/oradata/PRODDB/sys3/temp01.dbf'
> >SIZE 50M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
> >EXTENT MANAGEMENT LOCAL;
>
> I would not recommend using auto and unlimited extents on temp. This
> is just an invite to some silly developer or user to pull one of them
> infinite running disk crashing queries-from-hell on the database,
> doing cartesians joins and using perverted S&M SQL (like doing the
> TO_CHAR on the date colum in the WHERE clause and not using the
> TO_DATE on the literal instead).
>
> Don't you also at times wish that there is an optional WITH ASSASINATE
> parameter for the DROP USER statement? :-)
>
> --
> Billy
>
Received on Tue Mar 05 2002 - 08:36:24 CST