it would be an enhancement request at all. I can either think of a fixed array of n SQL_IDs per transaction - this can waste some space for short transactions and is allways too small for transactions with many different statements. But it's easy to code and cheap regarding latches/mutex. Other way is to create a real list, but this might hammer SGA a lot. I would prefer a list of arrays - so new memory structures are needed rarely (and only for 'big' transactions) but still all SQL_IDs are stored in order of their execution. For some reasons Oracle instrumentation seems to be very session-focused. Or is it just me and the way I learned it? Martin On Tue, Jul 3, 2012 at 6:28 PM, Leyi Kamus Zhang <kamusis@xxxxxxxxx> wrote: > Hi Yong > > Yes, I agree with you, It definitely helps application troubleshooting. > If Oracle can store all the SQL text somewhere for active transactions > (for example, with Idle event over 5 minutes but still not commit, > just like another type v$session_longops), it will help us a lot. > > -- > Kamus <kamusis@xxxxxxxxx> > > Visit my blog for more : http://www.dbform.com > Join ACOUG: http://www.acoug.org > > > On Wed, Jul 4, 2012 at 12:11 AM, Yong Huang <yong321@xxxxxxxxx> wrote: > >> I tried to answer this question some time ago and came to the > conclusion: > > you can't! > > > > I agree. There's no guaranteed way. This is equivalent to trying to > > disassemble binary executable code back to the exact source code; some > > tokens in the text will definitely be lost. V$open_cursor will not retain > > the original DML unless the original session never executed any other SQL > > after that so there's still a "breakable parse lock" on the cursor. If > > keeping that SQL is so important, the application may need to log it to a > > table or file, or use dbms_shared_pool to "keep" it in shared pool > > (cumbersome and not elegant). I wish Oracle would store these SQLs > > somewhere, maybe store the last one or two in v$transaction. It > definitely > > helps application troubleshooting. > > > > Yong Huang > -- //www.freelists.org/webpage/oracle-l