当前位置：文江博客话题详情

如何检索以前的sql命令

发布于 2024-10-20 03:59:03 字数 38 浏览 2 评论 0原文

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

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

岁月无声 2024-10-27 03:59:03

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

如果您使用 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'))
  按 o.saddr、s.address、o.hash_value、s.piece 排序；

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

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).

回复收藏 0 原文

千柳 2024-10-27 03:59:03

正如 Vincent 指出的，唯一的方法是（据我所知）在客户端级别跟踪会话。

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

ASH 的问题在于

它每秒对活动会话进行采样（因此您会丢失所有快速会话），
它是一个循环缓冲区（由 DBA_HIST_ACTIVE_SESS_HISTORY 视图备份），因此您会丢失较旧的会话。

这是因为它只是为了“捕获”长时间运行的查询以提高性能。
然而，如果人们只对响应时间较长的查询感兴趣，那么它就很适合。

无论如何，这是一个简单的查询，返回会话的长查询历史记录。

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

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

it samples every seconds for active sessions (so you are missing all the quick ones),
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') ;

回复收藏 0 原文

~没有更多了~