RE: Global Temp Tables and Indexes

Kirti,

I am running 81712 on NT4 with a 1MB sort_area_size and a 1MB sort_area_retained_size.

As soon as I insert 1 row into a 6 column GTT ( VARCHAR2(2),NUMBER(5),NUMBER(5),NUMBER(8),VARCHAR2(1),DATE) I see a record appear in v$sort_usage.
In testing, this the first thing I do as soon as I connect and I do not have a login script that runs any sorts.

Why might this be (ie why isn't the row held entirely in the PGA given the 1M sort_area_size)?

I altered the system's sort_area_size to 10M and reconnected and still after inserting just 1 row I see a record in v$sort_usage. I get the same behaviour regardless of whether the temp tablespace has temporary or permanent contents.

Thanks,
Bruce

-----Original Message-----
Sent: Thursday, 28 June 2001 11:06

Jack,
 Those rows will be stored in session's PGA in the sort areas (sort_area_size). When you insert more rows to fill up sort_area_size, you will see the table using your temporary tablespace. Or you can alter your session to use much smaller sort_area_size. Also, you can check v$sort_usage view to see how the numbers change as you add to the GTT.

 Hope this helps,

• Kirti Deshpande Verizon Information Services http://www.superpages.com

> -----Original Message-----
> From: Jack C. Applewhite [SMTP:japplewhite_at_inetprofit.com]
> Sent: Wednesday, June 27, 2001 7:01 PM
>
> Hi,
>
> Using Oracle8i 8.1.6...
>
> I've created a Temp table and inserted a couple of rows, but there seems
> to
> be no way to verify that it's segment(s) is(are) in the temp tablespace of
> the creating user. I've looked at every USER*, DBA*, and v$* view I can
> find - Tablespace_Name is null in all of them. I inserted the rows
> because
> I know that the first extent of a temp table is not allocated until the
> first insert.
>
> I'd like to see that the segment is where I think it is because I want
> another user whose temp t.s. is on another drive to create an index on
> that
> table. That's the only way I can think of to get an index on a temp table
> in another tablespace - for performance considerations.
>
> Anybody know how to verify the tablespace? Will my proposed method for
> getting the temp index on the temp table on another drive work? Any
> suggestions?
>
> Thanks.
>
> Jack
>
> --------------------------------
> Jack C. Applewhite
> Database Administrator/Developer
> OCP Oracle8 DBA
> iNetProfit, Inc.
> Austin, Texas
> www.iNetProfit.com
> japplewhite_at_inetprofit.com
> (512)327-9068

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.com.au

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).