Maybe the other Cary will pitch in here, but I think Mark is right. Cursor numbers can be reused in trace files so even if there was a place where they were stored along with the sql (which I don't think there is), you wouldn't know which number 2 to look at.
Kerry Osborne Enkitec blog: kerryosborne.oracle-guy.com On Jul 7, 2009, at 7:37 PM, Bobak, Mark wrote:
Hi Chen,I don't think it's in the SGA. You and I could reference the same SQL, and have different cursor #'s in our respective trace files. I suspect that information is private to the PGA, and I can't think where it would be mapped to an X$/V$....Honestly, I think you're out of luck..... If anyone has any better ideas, I'd be happy to be wrong.... -Mark -----Original Message-----From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx ] On Behalf Of Chen ShapiraSent: Tuesday, July 07, 2009 7:04 PM To: Daniel Fink; oracle-l Subject: Re: Trying to locate a cursor with very little information I don't have the sql text in the trace file, but Oracle still have the cursor in the shared pool. And Oracle knows that cursor #2 from a specific process is related to a specific open cursor in the pool (otherwise exec and fetch calls would fail). So there must be a way to find the sql text from Oracle's SGA. I was hoping someone already figured it out... ChenOn Tue, Jul 7, 2009 at 3:51 PM, Daniel Fink<daniel.fink@xxxxxxxxxxxxxx> wrote:If you don't have the PARSE or PARSING lines, you won't have the sql text.However, if the STAT lines were written, you might be able to reverseengineer the statement from the plan. Use the STAT lines to see what plansuse those operations and what statements use those plans.-- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l
-- //www.freelists.org/webpage/oracle-l