How to Use ORADEBUG CURRENT_SQL to See Other Session SQL

Facts on ORADEBUG
  1. You have to connect as sysdba to use/execute the ORADEBUG
  2. It can be executed from the SQLPlus prompt
Example
Here I am giving you an example of ORADEBUG to check the SQL statement running on other session using a session with sysdba privilege.


Session 1
SQL> SELECT a.username, a.sid, a.serial#, b.spid, b.pid
     FROM v$session a, v$process b
     WHERE a.paddr = b.addr AND a.username = 'SCOTT';


USERNAME          SID    SERIAL# SPID                            PID
---------- ---------- ---------- ------------------------ ----------
SCOTT             653      14811 23936                           106

Execute a procedure which takes at least run for one minute. See a sample pl/sql block below.
declare
x number;
begin
for i in 1 .. 1000000 loop
select 1 into x from dual;
end loop;
end;

Session 2
SQL> oradebug setospid 15103
ORA-01031: insufficient privileges

You have to be with sysdba privilege to execute ORADEBUG.

SQL> connect / as sysdba
Connected.
SQL> show user
USER is "SYS"

Connecting to the Session 1 using ORADEBUG and OS process id (OSPID-SPID)
SQL> oradebug setospid 23936
Oracle pid: 106, Unix process pid: 23936, image: oracle@prod.diamond.net (TNS V)
SQL> oradebug current_sql
SELECT 1 FROM DUAL
Still running…
SQL> oradebug current_sql
SELECT 1 FROM DUAL

Execution Completed…
SQL> oradebug current_sql
<none>

Reset back the session to original session
SQL> oradebug setmypid
Statement processed.
SQL> oradebug current_sql
<none>
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google