跟踪oracle语句 [英] trace oracle statements

问题描述

我正在使用Oracle数据库开发应用程序，我当然希望拥有一个可以跟踪会话和处理语句的SQL语句跟踪程序，例如Toad的SQL Tracker/Monitor.但是，既然好人要花很多钱，我正在考虑自己建造一个小人. 关于wich的任何想法都将是跟踪oracle sql语句的最佳解决方案?

I'm developing an app using Oracle database and I would surely like to have an SQL statements tracer that could trace sessions and processes statements - like Toad's SQL Tracker/Monitor. But since the good one cost alot of money I'm thinking about building a small one myself. Any ideeas about wich would be the best solution for tracing oracle sql statements?

推荐答案

Sql Plus + tkprof.

Sql Plus + tkprof.

alter session set timed_statistics = true; 
alter session set sql_trace = true; 
show parameter user_dump_dest



tkprof <trc-файл> <txt-файл>

如果您需要跟踪任何会话(不仅是您自己的会话):

If you need to trace any session (not only your own):

 select sid,serial# from v$session

查看会话的sid和

begin
  sys.dbms_system.set_ev(sid, serial#, 10046, 12, '');
end;

否则，您可以使用登录触发器:

Otherwise you can use logon trigger:

CREATE OR REPLACE TRIGGER SYS.TRACE_A_USER
AFTER
LOGON ON <some_db_user>.SCHEMA
DECLARE
user_sid NUMBER;
user_serial# NUMBER;
user_program VARCHAR2(48);
BEGIN
-- Collect the current user session details.
SELECT sid, serial#, UPPER(program)
INTO user_sid, user_serial#, user_program
FROM v$session
WHERE audsid = USERENV('SESSIONID');
-- Start tracing if the user is running the identified application.
IF user_program = 'SOMECODE.EXE' THEN
-- Enable tracing. Note level 12 tracing includes bind variable
-- and wait statistics.
sys.dbms_system.set_ev(user_sid, user_serial#, 10046, 12, '');
END IF;
END;

这篇关于跟踪oracle语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！