Re: temporary tablespace usage

"Yong Huang" <yong321_at_yahoo.com> wrote in message news:b3cb12d6.0211301941.6cf3f77e_at_posting.google.com... > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<uo9G9.87191$g9.245220_at_newsfeeds.bigpond.com>...
> > "Yong Huang" <yong321_at_yahoo.com> wrote in message
> > news:b3cb12d6.0211300839.59dddf3b_at_posting.google.com...
> > > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> > news:<mFPF9.86504$g9.243485_at_newsfeeds.bigpond.com>...
> > > > To be certain, what is the output from dba_tablespaces for your temp
> > > > tablespace? If it says 'LOGGING' for it, its a temporary tablespace
of
> > the
> > > > first sort.
> > >
> > > By default, that's true.

> >
> > You've missed the point. Whether it's the default or not, if it says
logging
> > then it is definitely a datafile temporary tablespace. If it says
> > 'nologging', then fair enough: further investigation is required,
because
> > yes, its status could have been changed.

> >

>
> That's right. I did misread your message.
>
> I've never figured out why the datafile-based temporary tablespace can
> be and by default is labeled LOGGING.

I think it's because in the old days before tempfiles came out, which are sparse files and can be created in a matter of a second or two however big they are, datafile temporary tablespace might take forever to be re-created from scratch (a 100GB TEMP tablespace would require 100 gigs-worth of block formatting etc).

Also recall that a datafile temporary tablespace is still scanned for existence and consistency at the moment of startup, so to open a database with a duff temporary tablespace, you had to offline drop it, open what was left of the database, and then re-create it from scratch.

But if you had an enormous temporary tablespace, that would mean sitting there for (potentially) hours waiting for the damn thing to be recreated. So you might well have chosen instead the alternative strategy: restore the datafile and recover it. And the minute you propose to recover your temporary tablespace, you have to have redo available for it. Hence logging made a certain kind of sense.

But I agree with what I think you're saying: the tempfile alternative makes far more sense and you'd be mad to want to go back to datafiles.

Regards
HJR >
> Yong Huang Received on Sat Nov 30 2002 - 23:40:30 CST