Re: SQLs with temp segments

Thanks for the answer. Unfortunately, for me it's not very clear what is the cause of this behavior. Following your scenario I obtain:

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

Table created

SQL> insert into gtt values (1);

1 row inserted

SQL> select 'x' from dual;

'X'

---
x

SQL>
SQL> select sql_text from v$sqlarea s, v$tempseg_usage t where
  2  s.ADDRESS = t.SQLADDR and s.HASH_VALUE = t.SQLHASH;

SQL_TEXT
--------------------------------------------------------------------------------
begin :id := sys.dbms_transaction.local_transaction_id; end;

As far as I could understand from your reply the cause might be the
existence of a temporary table used by some sessions. However, it's
quite unclear to me how specifying the segtype='<some relevant tempseg
type>' could help. At the time the
"select sql_text from v$sqlarea s, v$tempseg_usage t where
s.ADDRESS = t.SQLADDR and s.HASH_VALUE = t.SQLHASH;" was issued I had
three different segment types: DATA, LOB_DATA, INDEX and because each
row form v$tempseg_usage has the same SQLHASH and SQLADDR I could
conclude that a single SQL statement has generated those three
temporary segments. But, the question is how the "select 'x' from
dual;" statement can generate those temp segs?

Alec.