Monday, 22 July 2013

ORADEBUG session for AQ

SQL> select owner, job_name from dba_scheduler_jobs 
SQL> where lower(job_action) like '%register_driver%'
OWNER                          JOB_NAME                     
------------------------------ ------------------------------
SYS                            AQ$_PLSQL_NTFN21               

SQL> select SESSION_ID, SLAVE_PROCESS_ID, SLAVE_OS_PROCESS_ID 
SQL> from DBA_SCHEDULER_RUNNING_JOBS where job_name='AQ$_PLSQL_NTFN21'
SESSION_ID SLAVE_PROCESS_ID SLAVE_OS_PROCESS_ID
---------- ---------------- -------------------
        16               26 26823               

SQL> select sid, serial#, paddr, program from v$session where sid=16
SID SERIAL# PADDR            PROGRAM                                        
--- ------- ---------------- ------------------------------------------------
 16       1 000000009F67D7A8 oracle@dt-bpss-db-04-2 (J000)                    

SQL> select spid from v$process where addr='000000009F67D7A8'
SPID                   
------------------------
26823                    
-------------------
SQL> oradebug setospid 26823
Oracle pid: 26, Unix process pid: 26823, image: oracle@dt-bpss-db-04-2 (J000)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug Event 10046 trace name context forever, level 12
Statement processed.
SQL> select sysdate from dual;

SYSDATE
-------------------
2013-07-18 13:57:50

SQL> oradebug Event 10046 trace name context off
Statement processed.