Re: Shrink the datafile of a Temporary Tablespace

On Nov 9, 5:44 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Nov 9, 4:30 am, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
>
>
>
>
>
> > On Nov 9, 4:56 am, Kurt-Erich.Fin..._at_hte-company.de wrote:
>
> > > Oracle Standard Edition 10.2 on Debian
>
> > > Hello,
> > > some sorting etc was done on our database cause the temp tablespace to
> > > increase dramtically.
> > > Since we do a cold backup frequently the large temp datafile causes
> > > some trouble.
> > > In the past I shrank the temp tablespace by creating a new temp
> > > tablespace and setting it as the default tablespace.
> > > Then I dropped the old tablespace and deleted the datafile.
>
> > > >CREATE TEMPORARY TABLESPACE TEMPnew TEMPFILE 'tempnew.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
> > > >ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPnew
> > > >ALTER DATABASE drop TABLESPACE TEMP including contents
>
> > > here I get : ORA-01900: LOGFILE keyword expected
>
> > > What does that mean? I searched metalink and the internet but did not
> > > find any useful explanation.
>
> > > regards
>
> > > Kurt-Erich
>
> > You described your original problem as the tablespace growing larger
> > than you wanted it to. However you did not limit the size of the next
> > one you created. Looks like the original problem will just come back
> > eventually
>
> Isn't whether you want to do that dependent on if the original problem
> was due to a mistake v. periodic large real work? For example, a
> yearly batch job might require much more temp space, and you might
> want to not shoot yourself in the foot for next time, but rather react
> after it happens. If you know that will never happen and the growth
> will necessarily show a mistake, like if you allow DSS inquries, then
> you might want to limit it. You should of course put in limits so you
> don't run out of disk space, but the details there... depend.
>

Well of course. The consequence of allowing a maximum size unlimited setting rarely make any sense at all. There has got to be some kind of upper limit whether it is reasonably arrived at or totally arbitrary.

It's not hard to build in some monitoring that alerts you go things that are going past 1/2 a gig, 1 gig ... more of temp space ... whether you use OEM or home grown.

Once you go past a gig of temp space I rarely see anything that is not a mistake of some kind, missing joins etc. Could vary to some degree in a DSS environment. Received on Fri Nov 09 2007 - 17:54:41 CST