Re: Temp Segments

I am still shakey on what is a 'temporary tablespace'.

In another thread I wrote:

I favor a 'permanent' tablespace used just for temporary segment for sorts, etc. It is less risky that housing temporary segments in any tablespace that contains 'permanent' data or Rollback segments. It also is easier to control i/o balancing and I think can be more efficient for space control.

There are those that favor mixing the temporary segments and permantent tablespaces. I interpret this to mean tablespaces that hold 'permanent' data.

What I now am curious about, because I am an old fogy that may not know all the new bells and whistles in Oracle, is that if there is a new tablespace type, 'temporary', that gets blown away and recreated every time the database is bounced, ala Informix and MS SS? A quick search of my doc cd does not show this kind of tablespace. Anything id'd in the data dictionary as a temporary segment gets blown away when it is no longer needed (when SMON decides to notice) or when the database is bounced.

O8i has a new (to me) thing of temporary tables (as opposed to tablespaces). If this is what is meant, I would still favor a seperate tablespace to protect my 'permanent' data and balance i/o.

I won't argue raw or locally managed. Raw is a matter of religion that needs to be addressed more on an instance/application basis then generically. But a raw partition is still a permanent allocation. Locally managed will give a performance benefit, the degree of which is more hardware and religouse than worth worrying about.

I am not familiar with the 'tempfile' construct (what platform?). My age may be showing again.

I hate to sound like Bill Clinton, but what do you mean by 'temporary' in this context?

In article <38E872A5.F5F_at_yahoo.com>,
  connor_mcdonald_at_yahoo.com wrote:
> Joe Maloney wrote:
> >
> > Just to confues the issue, there should be a permanent tablespace,
> > usually called TEMP, that is used just for temporary segments. The
> > other, 'Permanent', tablespaces hold the data.
> >
> > In article <8c8php$e0a$1_at_nnrp1.deja.com>,
> > hotoracle_at_my-deja.com wrote:
> > > The SORT_AREA_SIZE parameter is the maximum amount of memory
 given to
 an
> > > Oracle session for sorting before using the temporary segments.
> > >
> > > The parameter SORT_AREA_RETAINED_SIZE is the space that will be
 retained
> > > for the results of the sort.
> > >
> > > Lastly temporary segments should never be created in PERMANENT
> > > tablespaces only in TEMPORARY tablespaces. This is for many
 performance
> > > reasons.
> > >
> > > Ian Saunders
> > > http://www.hot-oracle.com
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> > --
> > Joseph R.P. Maloney, CCP,CSP,CDP
> > MPiR, Inc.
> > 502-451-7404
> > some witty phrase goes here, I think.
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
> I don't think TEMP should be a permament tablespace.
>
> Ideally it should be
>
> - temporary
> - raw (to avoid inode locking problems on Unix)
> - based on a tempfile so it doesn't worry backup/recovery
> - locally managed so it doesn't do nasties to space transactions
>
> HTH
> --
> ===========================================
> Connor McDonald
> http://www.oracledba.co.uk
>
> We are born naked, wet and hungry...then things get worse
>

--
Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.


Sent via Deja.com http://www.deja.com/
Before you buy.