CODEWITHSUNDEEP
sqloracle-database
JCX
JCX

Reputation: 1449

how to retrieve previous sql commands

would like to ask how to retrieve all my past database sql queries within that session? thanks

Upvotes: 2

Views: 13308

Answers (2)

Alain Pannetier
Alain Pannetier

Reputation: 9514

As Vincent pointed out the only way would be (afaik) to trace the session at the client level.

In addition to the open cursors (which is how Toad does it), another, less precise, way would be to use ASH (Active Session History).

The problems with ASH are that

  1. it samples every seconds for active sessions (so you are missing all the quick ones),
  2. it's a circular buffer (backed up by the DBA_HIST_ACTIVE_SESS_HISTORY view) so that you are missing the older ones.

This is because it's only meant to "catch" long running queries for performance purpose.
It is well adapted however if one is only interested in the queries with long response time.

For what it's worth, here is a simple query returning a session's history of long queries.

select 
  sqla.sql_text
from 
  v$active_session_history hist,
  v$sqlarea sqla,
  v$session ss 
 where 
   sqla.sql_id = hist.sql_id and
   ss.sid = hist.session_id and
   ss.serial# = hist.session_serial# and
   ss.audsid = sys_context('USERENV', 'SESSIONID') ;

Upvotes: 2

Vincent Malgrat
Vincent Malgrat

Reputation: 67802

I'm pretty sure Oracle doesn't keep data on all past queries (closed cursors) for each session. I can think of a couple of ways to get this data however:

  • If you're using PL/SQL, most of your past cursors will remain in your session cache (up to the cursor_sharing initialization parameter). You can query the view v$open_cursor:

    SELECT * FROM v$open_cursor WHERE sid=to_number(sys_context('USERENV','SID'))

    Join this view to v$sqltext (or v$sqltext_with_newlines) to get the full sql text:

     SELECT o.saddr, s.address, o.hash_value, s.piece, s.sql_text
   FROM v$open_cursor o
   JOIN v$sqltext_with_newlines s ON o.address = s.address
                                 AND o.hash_value = s.hash_value
  WHERE sid = to_number(sys_context('USERENV', 'SID'))
  ORDER BY o.saddr,  s.address, o.hash_value, s.piece;

  • You could trace your session, opening the resulting trace file once the session terminates will reveal all SQL (furthermore, you can tkprof the trace file to get a summary and statistics).

Upvotes: 6

Related Questions