Re: TEMP segments

Sorry it took so long to reply...

The first direct-path mechanism in Oracle (i.e. Oracle v6.0 FASTLOAD on MVS platforms, followed by Oracle7 v7.0 SQL*Loader DIRECT=TRUE) did not create a separate segment. It merely made use of space in the existing table segment above the HWM and performed loads there. If the operation succeeded, then the HWM was simply moved to include the populated blocks. If the load failed, then the HWM would stay where it was -- no need for a rollback! The blocks would presumably just get "eaten" over time as the HWM advanced naturally...

Starting in v7.1 and continuing today, all new "direct-path" (now referred to as APPEND) features create one or more separate TEMPORARY segment(s). When the load completes successfully, the TEMPORARY segment is merged into the table or index. If the operation fails, the TEMPORARY segment is left in place to be eventually cleaned up by SMON...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, November 21, 2002 4:13 AM

> Okay, it's early here, I'm fuzzy.
>
> I think I meant the same thing (I'd had one of those "user calls" in
> the middle of the night)
>
> what I think I meant to say was that any operation that would end up
> adding blocks above the HWM if it COMPLETED would do it by creating
> TEMP segments that are converted to data blocks on commit of the
> operation.
>
> basically, temp segments are created for any operation that does not
> touch existing blocks
>
> or am I still fuzzy?
>
>
> --- Tim Gorman <Tim_at_SageLogix.com> wrote:
> > No, only the original SQL*Loader DIRECT=TRUE does that (adding blocks
> > above
> > HWM), which was introduced with v7.0.x...
> >
> > Since then, all direct-path (a.k.a. append) operations (including
> > SQL*Loader
> > DIRECT=TRUE PARALLEL=TRUE, parallel CREATE INDEX, parallel CREATE
> > TABLE AS
> > SELECT, and INSERT /*+ APPEND PARALLEL */, etc) have created
> > temporary
> > segments for each parallel execution "slave" process which will be
> > merged
> > into eventual segment. As Anjo commented, it makes for really fast
> > and easy
> > rollback; just let SMON clean up the temporary segments...
> >
> > ...don't know if it's ever really been documented; I did include
> > this
> > description in our books on data warehousing in 1997 and 1999...
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Wednesday, November 20, 2002 4:08 AM
> >
> >
> > > Tim -- that adds new blocks above the HWM?
> > >
> > > I wonder if any command that adds new blocks (vs inserting rows
> > into
> > > existing ones) automatically creates the new blocks as TEMP
> > segments.
> > > Logically it makes sense but I wonder if it's documented anywhere.
> > >
> > >
> > >
> > > --- Tim Gorman <Tim_at_SageLogix.com> wrote:
> > > > ...as does INSERT /*+ APPEND PARALLEL */...
> > > > ----- Original Message -----
> > > > From: Fink, Dan
> > > > To: Multiple recipients of list ORACLE-L
> > > > Sent: Tuesday, November 19, 2002 1:15 PM
> > > > Subject: TEMP segments
> > > >
> > > >
> > > > I just found a new command that creates TEMP segments. It is
> > well
> > > > known that index creations first create the index segments as
> > TEMP
> > > > segments then 'convert' them to index segments upon completion.
> > What
> > > > I just found out (thanks to a failed operation) is that 'CREATE
> > TABLE
> > > > AS SELECT' (ctas) also creates the segments as TEMP first.
> > > >
> > > > Dan Fink
> > > >
> > >
> > >
> > > __________________________________________________
> > > Do you Yahoo!?
> > > Yahoo! Web Hosting - Let the expert host your site
> > > http://webhosting.yahoo.com
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Rachel Carmichael
> > > INET: wisernet100_at_yahoo.com
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting
> > services
> > >
> > ---------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like
> > subscribing).
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Tim Gorman
> > INET: Tim_at_SageLogix.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).