Re: Shrink the datafile of a Temporary Tablespace

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.

jg

--
@home.com is bogus.
"We have not calculated the probability of a recession." - Ben Bernanke