显示进入 Oracle 数据库的所有查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9125223/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Show all queries coming to an Oracle database
提问by user1186971
I need to see all queries coming to database. How to do that? I could not get proper results from a Google search.
我需要查看进入数据库的所有查询。怎么做？我无法从 Google 搜索中获得正确的结果。
回答by Sathyajith Bhat
Enable SQL Trace& all the queries coming to the database will be logged.
启用SQL 跟踪，所有进入数据库的查询都将被记录。
ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = mysqltrace;
The trace file will be present in the udump directory.
跟踪文件将出现在 udump 目录中。
If you want to audit the database, look at my previous answer.
如果要审计数据库，看我之前的回答。
回答by mike
If you need to see all queries from all sessionsfor a SHORT window of time and you need a really simple solution, this is what I do. (The above answers will only show you SQL being run in one session, this gives all SQL across all sessions, easily.)
如果您需要在短时间内查看所有会话的所有查询，并且您需要一个非常简单的解决方案，这就是我所做的。（以上答案只会显示您在一个会话中运行的 SQL，这可以轻松提供所有会话中的所有 SQL。）
1). Create a temp table to store all the retrieved SQL:
1）。创建一个临时表来存储所有检索到的 SQL：
-- Fabien pointed out out that 'port may be inaccessible on 10.2
CREATE TABLE "MIKE"."TMP"
( "LOOP_NO" NUMBER(10,0),
"SID" NUMBER,
"SERIAL#" NUMBER,
"PROCESS" VARCHAR2(24 BYTE),
"PROGRAM" VARCHAR2(48 BYTE),
"MODULE" VARCHAR2(64 BYTE),
"OSUSER" VARCHAR2(30 BYTE),
"SCHEMANAME" VARCHAR2(30 BYTE),
"ACTION" VARCHAR2(64 BYTE),
"MACHINE" VARCHAR2(64 BYTE),
"PORT" NUMBER,
"TERMINAL" VARCHAR2(30 BYTE),
"ADDRESS" RAW(8),
"PIECE" NUMBER,
"SQL_TEXT" VARCHAR2(4000)
)
2). Run a nasty polling loop in an anonymous block to gather all SQL run on the system, as long as the block is running:
2）。在匿名块中运行一个讨厌的轮询循环以收集系统上运行的所有 SQL，只要该块正在运行：
declare
begin
for j in 1.. 1000 loop
insert into mike.tmp
SELECT j, b.sid, b.serial#, b.process, b.program, b.module, b.osuser, b.schemaname, b.action, b.machine, b.port, b.terminal,a.address, a.piece, a.sql_text
FROM V$sqltext_With_Newlines a
join V$Session b on a.address = b.sql_address
WHERE A.ADDRESS NOT IN (select address FROM mike.tmp)
ORDER BY b.sid, a.piece;
commit;
end loop;
end;
3). Query to retrieve SQL:
3）。查询以检索 SQL：
select distinct osuser, a.address, a.sid, a.piece, a.sql_text
from mike.tmp a
join (select loop_no, sid from mike.tmp where sql_text like '%anytexthere%') b
on a.loop_no = b.loop_no
and a.sid = b.sid
order by a.sid, a.address, a.piece
... please mind that this is just a quick way to trap SQL when you are in a "what the heck is going on here?" situation, and you DO NOT have GUI Tools, and you DO NOT have file access to USER_DUMP_DEST.
...请注意，当您处于“这到底是怎么回事？”时，这只是一种捕获 SQL 的快速方法。情况，并且您没有 GUI 工具，并且您没有对USER_DUMP_DEST.
回答by Daniel Williams
Running XE in Windows here is how I do it to find what user is doing. Start up SQLPlus and run:
在 Windows 中运行 XE 是我如何查找用户正在执行的操作。启动 SQLPlus 并运行：
> SELECT USERNAME, SID, SERIAL# FROM v$session WHERE userName = '<your user>'
This gets you two integer values, SID and SERIAL# for your user. Your user may have more than one session open. Run this to turn on logging:
这将为您的用户提供两个整数值，SID 和 SERIAL#。您的用户可能打开了多个会话。运行此命令以打开日志记录：
> execute dbms_system.set_sql_trace_in_session(<SID>, <SERIAL#>, true)
Next have your app do some work... Find out where the data went using:
接下来让你的应用做一些工作......找出数据使用的地方：
> SHOW PARAMETERS user_dump_dest
And you'll get something like: C:\oraclexe\app\oracle\diag\rdbms\xe\xe\trace where you will find a number of trace logs. The .trc files are simply text
您会得到类似以下内容的信息：C:\oracle\app\oracle\diag\rdbms\xe\xe\trace，您可以在其中找到许多跟踪日志。.trc 文件只是文本
When done, turn off the logging so you don't fill up files or slow your db down.
完成后，关闭日志记录，以免填满文件或减慢数据库速度。
> execute dbms_system.set_sql_trace_in_session(<SID>, <SERIAL#>, false)
There you go - happy diagnosing and reverse engineering!
你去 - 快乐的诊断和逆向工程！
