RE: Size of Global temporary table.

From: Paresh Patel <>
Date: Thu, 2 Jul 2009 14:17:07 -0700
Message-ID: <>

You are right Alex. Thanks for your help.  

When I use session_address to figure out sql_id from v$session and v$tempseg_usage views, both show different SQL_IDs.  

Here is the query I used,  

select a.session_addr,b.saddr,a.sql_id sqlid_tempseg ,b.sql_id sqlid_session,sid from V$TEMPSEG_USAGE a, v$session b

WHERE a.session_addr= b.saddr;  

If I take sql_id which v$session shows and check in v$sql, it shows the sql which populate this global temporary table, which is correct but sql_id which v$tempseg_usage has, shows different sql in v$sql.  

Does anyone have any idea why both views show different sql_id?  


Paresh Patel,    

-----Original Message-----
From: Alex Fatkulin [] Sent: Thursday, July 02, 2009 12:26 PM
To: Paresh Patel
Subject: Re: Size of Global temporary table.  

you should see segment type data  

SQL> create global temporary table gtt (n number);  

Table created  

SQL> insert into gtt values (1);  

1 row inserted  

SQL> select blocks, segtype

  2 from v$tempseg_usage

  3 where session_addr=(select saddr from v$session where

sid=sys_context('userenv', 'sid'));  


  • ---------

       128 DATA     On Thu, Jul 2, 2009 at 3:23 PM, Paresh
Patel<> wrote:

> I checked that view, but it doesn't contain information about GTT

> segment. All I can see in that view is temporary space occupied by

> and group by operations.


> Thanks,

> Paresh Patel,




Alex Fatkulin,

Received on Thu Jul 02 2009 - 16:17:07 CDT

Original text of this message