mapping a GTT to space usage in the Temp Tablespace

I can identify a Global Temporary Tablespace consuming space in the Temp tablespace by querying V$SORT_USAGE / V$TEMPSEG_USAGE. It appears as a row with CONTENTS='TEMPORARY' SEGTYPE= 'DATA'

If I have multiple GTTs being used by a single session , how do I identify which entry in V$TEMPSEG_USAGE is which GTT ? I Need to "size" the GTTs and cannot use DBA/USER_SEGMENTS.

For example , the session with SERIAL#49681 is currently using 4 GTTs. SQL_ID doesn't help because it is the SQL_ID of the last SQL from the session.

HEMANT>l
  1* select * from v$tempseg_usage where tablespace='HEMANT_TEMP' HEMANT>/

USERNAME                       USER
SESSION_ADDR     SESSION_NUM SQLADDR          SQLHASH SQL_ID


------------------------------ ------------------------------

---------------- ----------- ---------------- ---------- -------------

TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#
SEGBLK#    EXTENTS     BLOCKS   SEGRFNO#


------------------------------- --------- --------- ----------

---------- ---------- ---------- ----------

HEMANT                       HEMANT
000000742D7717B8       12387 000000740D009DB0 2978105261 0890n6fss4jxd
HEMANT_TEMP                TEMPORARY LOB_DATA        1050    1127424
1        128       2

HEMANT                       HEMANT
000000742CC87A28       49681 0000006EC8CA75B8 4269071746 0j88ahzz79rc2
HEMANT_TEMP                TEMPORARY LOB_DATA        1050    1163776
1        128       2

HEMANT                       HEMANT
000000742CC87A28       49681 0000006EC8CA75B8 4269071746 0j88ahzz79rc2
HEMANT_TEMP                TEMPORARY DATA            1050    1162240
544      69632       2

HEMANT                       HEMANT
000000742D36DE50        8017 0000006ED576DE00 2252965756 2j2xhw634m1vw
HEMANT_TEMP                TEMPORARY DATA            1038    3917184
1031     131968       1

HEMANT                       HEMANT
000000742D36DE50        8017 0000006ED576DE00 2252965756 2j2xhw634m1vw
HEMANT_TEMP                TEMPORARY DATA            1038    4045056
516      66048       1

HEMANT                       HEMANT
000000742D36DE50        8017 0000006ED576DE00 2252965756 2j2xhw634m1vw
HEMANT_TEMP                TEMPORARY DATA            1038    4063744
518      66304       1

Hemant K Chitale

This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html.
--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 13 2014 - 07:22:55 CET