> Ari,
> I enjoyed your session this morning on "A Bag of Tricks and Tips for
> DBAs and Developer."
> As discussed during the question and answer period,  I would like to be
> able to identify the SQL statement (and Oracle user executing the
> statement) responsible for a disk sort when a disk sort is in progress.
> I know there must be a way to extract this information from Oracle's
> data dictionary.  When temporary objects appear in the dba_segments
> view, they are owned by SYS and have a naming convention of
> ., which appears to be 
. data. I've > been unable to trace these temporary objects back to a user or SQL > statement. Can you tell me what is the proper SQL statement to query > the database for this information? > > The database in question is version 7.1.6. > > Thanks, > Bill Reidy > wreidy@csc.com > Bill, Thanks for coming to the speech and being so patient with my slow reply. I was out of the country and just recently got back. Anyway, I do remember your question and went to investigate, as I thought that I remembered the answer. From what I concluded, I could not link the user with the sort, as Oracle assigns SYS as the owner (like you mentioned). What I have been doing, however, is seeing which users are using which rollback segments. I will give you the script for you to try. It only shows transactions that take more than a few seconds to complete, which is the case when the TEMP segments are used. It is this way that I can relate the user to the TEMP segment (if there is only one such rollback segment being used in the database). Otherwise I can only limit it to those users using rollback segments at the moment. Try the following SQL to see which users are using rollback segments: set pagesize 24 select to_char(rownum+3) ||') ' || rpad(r.name,17) || rpad(to_char(p.pid),11) || rpad(p.spid,11) || rpad(nvl(p.username,'NO TRANSACTION'),17) || rpad(p.terminal,8) FROM v$lock l, v$process p, v$rollname r, v$session s WHERE l.sid = s.sid (+) and p.addr = s.paddr and trunc(l.id1(+)/65536)=r.usn and l.type(+) = 'TX' and l.lmode(+) = 6 UNION select '2) ROLLBACK SEGMENT'||' '|| 'ORACLE PID' ||' '|| 'SYSTEM PID' ||' '|| 'TRANSACTION ' ||' '|| 'TERMINAL' from dual UNION select '1) ' from dual UNION select '3) ----------------' ||' '|| '----------' ||' '|| '----------' ||' '|| '----------------' ||' '|| '--------' from dual ORDER BY 1 / Best of luck, and I hope that this helps you,

Back to Ari Kaplan's Home Page "> Back to Ari Kaplan's Home Page