FW: mapping a GTT to space usage in the Temp Tablespace
- From: "Chitale, Hemant K" <Hemant-K.Chitale@xxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 13 Mar 2014 14:25:30 +0800
Correction : I have 3 GTTs in use but four entries in v$TEMPSEG_USAGE.
Hemant K Chitale
_____________________________________________
From: Chitale, Hemant K
Sent: Thursday, March 13, 2014 2:23 PM
To: ORACLE-L
Subject: 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.
Other related posts:
