Which session owns a temp table
I need some help. I tried searching the board for any answers to this, but
apparently search engine skills are diminishing. I need to identify which
session owns a temp table. More specifically, I need to determine which
session is consuming temp space. I have constructed the following query that
gives me the table owner, and size from a given dbspace:
select tabname, owner, sum(pe_size*4096)/1048576 MB
from sysptnext, systabnames
where dbinfo( "DBSPACE" , pe_partnum ) = "tempdbspace"
and pe_partnum = partnum
and tabname != 'TBLSpace'
group by 1,2
order by 3 desc
however, I can't figure out how to tie this to a session so that I can kill
the session taking all of my tempspace. Any help would be greatly appreciated.
also, I wrote a query to show some info about tempdbspace usage stats. I
thought someone might find it useful.
select
name[1,18],
round(sum((chksize*a.pagesize)/1048576)) chunksize,
round((sum((chksize*a.pagesize)/1048576))-(sum((nfree*a.pagesize)/1048576)))
mbused,
round(((sum((chksize*a.pagesize)/1048576))-(sum((nfree*a.pagesize)/1048576)))/(s
um((chksize*a.pagesize)/1048576))*
100) PCTUSED
from syschunks a,sysdbspaces b
where b.is_temp = 1
and a.dbsnum = b.dbsnum
group by 1
order by 2 desc