Re: Global Temp Tables - What do I tell my dba?

In article <2bb94f94.0205080732.750bafd_at_posting.google.com>, fast_johnnyz_at_yahoo.com says...
>
>I'm using 2 global temporary tables to store some information I'm
>pulling from a DB2 database.
>I process these two tables with some selects and updates. These tables
>consist of 5-6 columns with each
>row about 80-90 wide. We're about to go live with this procedure and
>since global temp tables
>use the user's temp tablespace, should I have my dba increase the temp
>tablespace by the
>largest data that both of these tables will store?
>
>Also, after reading Tom Kyte's book, (great book...) I am a bit
>confused. He says that
>a global temp table is stored in a user's temp tablespace. Well, when
>I create the
>table, there are no rows in it. Does it just automatcally site in the
>tablespace or
>in memory. Also, after I fill it, is it always on disk or in memory
>first then to disk
>when there is no memory left. (like a large sort)
>
>Thanks.
>Eric

What I actually say (page 251) is:

"Temporary tables will allocate storage from the currently logged in users temporary tablespace, or if they are accessed from a definers rights procedure ? the temporary tablespace of the owner of that procedure will be used. A global temporary table is really just a template for the table itself. The act of creating a temporary table involves no storage allocation ? no initial extent is allocated as it would be for a non-temporary table. Rather, at runtime when a session first puts data into the temporary table a temporary segment for that session will be created at that time. Since each session gets its own temporary segment (not just an extent of an existing segment) every user might be allocating space for their temporary table in different tablespaces. That is, USER1 might have their temporary tablespace set to TEMP1 ? their temporary tables will be allocated from this space. USER2 might have TEMP2 as their temporary tablespace and their temporary tables will be allocated there. "

When you create the temp table -- it consumes no storage anywhere, until you start using it.

As soon as you get a row in there, it'll start using TEMP -- temp can be buffered so it can be in the cached however, it'll consume TEMP space logically (either in the buffer cache or physically on disk).

You can observe this by querying v$sort_usage as you use the temp table. It'll acquire temp space with the first insert and keep it until the contents are "gone" (either on commit or session end or truncate of the temp table)

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp