oracle 如何检索以前的sql命令

how to retrieve previous sql commands

提问by JCX

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

想问一下如何在那个会话中检索我过去所有的数据库sql查询？谢谢

回答by Vincent Malgrat

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:

我很确定 Oracle 不会为每个会话保留所有过去查询（关闭游标）的数据。但是，我可以想到几种获取此数据的方法：

• If you're using PL/SQL, most of your past cursors will remain in your session cache (up to the cursor_sharinginitialization 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).

• 如果您使用 PL/SQL，大多数过去的游标将保留在会话缓存中（直到cursor_sharing初始化参数）。您可以查询视图v$open_cursor：

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

  将此视图加入v$sqltext（或v$sqltext_with_newlines）以获取完整的 sql 文本：

   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;
  

• 您可以跟踪您的会话，一旦会话终止，打开生成的跟踪文件将显示所有 SQL（此外，您可以 tkprof 跟踪文件以获取摘要和统计信息）。

回答by Alain Pannetier

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

正如文森特指出的那样，唯一的方法是（afaik）在客户端级别跟踪会话。

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

除了打开的游标（Toad 就是这样做的）之外，另一种不太精确的方法是使用 ASH（活动会话历史记录）。

The problems with ASH are that

ASH 的问题在于

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.

1. 它每秒钟对活动会话进行采样（因此您会错过所有快速会话），
2. 它是一个循环缓冲区（由 DBA_HIST_ACTIVE_SESS_HISTORY 视图支持），因此您会错过旧的缓冲区。

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') ;