RE: Size of Global temporary table.
Date: Thu, 2 Jul 2009 14:17:07 -0700
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?
From: Alex Fatkulin [mailto:afatkulin_at_gmail.com] 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);
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
On Thu, Jul 2, 2009 at 3:23 PM, Paresh
> 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.
> Paresh Patel,
-- Alex Fatkulin, http://afatkulin.blogspot.com http://www.linkedin.com/in/alexfatkulin -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 02 2009 - 16:17:07 CDT