How to Use ORADEBUG CURRENT_SQL to See Other Session SQL

advertisements

_____________________________________________________________________________________________________________________

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>

_____________________________________________________________________________________________________________________

Website Stats