Re: Question - Oracle's "TEMP" tablespace and a user's "temporary

From: Don Granaman <granaman_at_home.com>
Date: Fri, 19 Oct 2001 00:45:49 -0700
Message-ID: <F001.003AF805.20011019005517@fatcity.com>

Well... Again - "sort of". Temporary segments for "normal" stuff (e.g. SQL "order by", joins, etc.) will occur in USER_TEMP. Sorts for segment operations, parallel index builds and the like, may occur elsewhere.

-Don Granaman
(OraSaurus - Honk if you remember OPS ;-)

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, October 17, 2001 1:18 PM "temporary

> Don;
> Thanks very much for your reply. All very understandable. However,
let me
> ask a direct question; does this mean that, given a user having been
> explicitly assigned a temporary tablespace (ie. USER_TEMP), that all
of that
> user's sorts and temporary segments will occur in the assigned
(USER_TEMP)
> tablespace and nowhere else.
>
> Thanks again.
>
> Michael L. Petrus
> GE Auto Warranty Services
> 7125 W. Jefferson Av. #200
> Lakewood, CO 80235
>
> Database Administrator
>
> Phone: (303) 987 4129
> Fax: (303) 987 4298
> Email: Mike.Petrus2_at_gecapital.com
>
>
> -----Original Message-----
> Sent: Wednesday, October 17, 2001 1:40 AM
> To: Multiple recipients of list ORACLE-L
> "temporary" ta
>
>
> Not exactly...
>
> "TEMP" is an arbitrary (but logical, recommended, and OFA compliant)
> name for a tablespace. It could just as well be named "GEORGE" for
> all that $ORACLE_HOME/bin/oracle or any of its kin care.
>
> "TEMPORARY_TABLESPACE" is an attribute of a user. (i.e. select
> TEMPORARY_TABLESPACE from ALL_USERS;" or from SYS.DBA_USERS). All
> users (including SYS and SYSTEM) are either implicitly or explicitly
> assigned a temporary tablespace. Unfortunately, it defaults to the
> worst possible choice -SYSTEM! Typically, users are explicitly
> assigned TEMP as their temporary tablespace. ["Oracle" (AKA: sys)
> doesn't usually do much that requires a lot of temporary space.]
>
> The vast majority of Oracle databases have only one such tablespace,
> but (logically) partitioning users among multiple such tablespaces
> (TEMP01, TEMP02, ...) is sometimes advisable. Making TEMP (and its
> ilk) truly "TEMPORARY" (as opposed to PERMANENT) is always
advisable.
>
> -Don Granaman
> (OraSaurus - Honk if you remember OPS ;-)
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Tuesday, October 16, 2001 5:15 PM
> "temporary" ta
>
>
> > Environment:
> > Oracle v8.1.7
> > Sun / Solaris
> >
>

---

> ======
> > ================
> > Can someone explain what activities occur in Oracle's "TEMP"
> tablespace as
> > opposed to what occurs in a user's assigned "TEMPORARY"
tablespace?
> >
> > It is my assumption that Oracle's "TEMP" tablespace is the area
used
> by
> > Oracle for it's processes to sort in.
> >
> > It is also my assumption that the user specific "TEMPORARY"
> tablespace is
> > used to hold temporary segments that are created when the user
> builds an
> > index or creates a table, etc..
> >
> > If I am off base please set me straight.
> >
> > Thanks in advance.
> >
> > Michael L. Petrus
> > GE Auto Warranty Services
> > 7125 W. Jefferson Av. #200
> > Lakewood, CO 80235
> >
> > Database Administrator
> >
> > Phone: (303) 987 4129
> > Fax: (303) 987 4298
> > Email: Mike.Petrus2_at_gecapital.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Don Granaman
> INET: granaman_at_home.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
Lists
> --------------------------------------------------------------------
> 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: Petrus, Mike (CAP, GEFA)
> INET: Mike.Petrus2_at_gecapital.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
Lists
> --------------------------------------------------------------------
> 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: Don Granaman
  INET: granaman_at_home.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).

Received on Fri Oct 19 2001 - 02:45:49 CDT