ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
ORADEBUG is an undocumented debugging utility built into the Oracle Database
Credit
Credit: A few examples on this page, specifically PEEK and POKE, were copied from one of Julian Dyke's web pages which is no longer on the web a few others from Tanel Poder's
excellent 2006 presentation for NoCOUG titled "Advanced Research Techniques in Oracle"
Related Queries
Background Processes
SELECT b.name, p.pid
FROM v$bgprocess b, v$process p
WHERE b.paddr = p.addr
ORDER BY 1;
SELECT d.name, p.pid
FROM v$dispatcher d, v$process p
WHERE d.paddr = p.addr;
NAME PID
--------------- ----------
D000 30
Parallel Execution Dependent Processes
SELECT pid, server_name, status
FROM v$px_process;
PID SERV STATUS
----- ---- ---------
56 P000 AVAILABLE
57 P001 AVAILABLE
58 P002 AVAILABLE
59 P003 AVAILABLE
60 P004 AVAILABLE
61 P005 AVAILABLE
62 P006 AVAILABLE
63 P007 AVAILABLE
64 P008 AVAILABLE
65 P009 AVAILABLE
66 P00A AVAILABLE
67 P00B AVAILABLE
68 P00C AVAILABLE
69 P00D AVAILABLE
70 P00E AVAILABLE
71 P00F AVAILABLE
Shared Server Processes
SELECT s.name, p.pid
FROM v$shared_server s, v$process p
WHERE s.paddr = p.addr;
NAME PID
------------------------------ ----------
S000 41
SGA Variables
SELECT ksmfsnam
FROM x$ksmfsv
WHERE ksmfsnam LIKE '%\_' ESCAPE '\';
SQL> oradebug call ksmget_sgamaxalloc
ORA-32519: insufficient privileges to
execute ORADEBUG command: execution of ORADEBUG commands is disabled for
this instance.
-- if the exception shown above is returned have the
Database Vault administrator
-- enable oradebug as follows:
conn dvsys
Enter password:
Connected.
exec dbms_macadm.enable_oradebug;
CLOSE_TRACE
Close the trace file
oradebug close_trace
SQL> oradebug close_trace Statement processed.
CORE
Dump core without crashing process
oradebug core
SQL> oradebug core Statement processed.
CURRENT_SQL
Get the current SQL statement
oradebug current_sql
SYS
UWCLASS
conn sys@pdbdev as sysdba
conn uwclass/uwclass@pdbdev
GRANT execute ON dbms_support TO uwclass;
SQL> SELECT pid
2 FROM v$process
3 WHERE addr = (
4 SELECT paddr
5 FROM v$session
6 WHERE sid = sys.dbms_support.mysid);
SQL> oradebug delete
local watchpoint 3
ORA-32519: insufficient privileges to execute
ORADEBUG command: execution of ORADEBUG commands is disabled for this
instance.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file C:\ORACLE\diag\rdbms\orabase\orabase\trace\orabase_ora_10988.trc
/* This example requires two sessions, session 1 logged on SYS AS SYSDBA and session 2 which executes the query. In session 2 identify the PID using */
SELECT pid
FROM v$process
WHERE addr IN (
SELECT paddr
FROM v$session
WHERE sid = dbms_support.mysid);
-- In this example the PID was 12
-- In session 1 set the Oracle PID using
oradebug setorapid 12
-- In session 2 start the query
SELECT ... FROM t1 ORDER BY ....
-- In session 1 suspend session 2
oradebug suspend
-- The query in session 2 will be suspended
-- In session 1 run the heap dump
oradebug dump heapdump 1
-- The heapdump will show the memory structures allocated for the sort. At this point further dumps e.g. subheap dumps can be taken.
-- In session 1 resume session 2
oradebug resume
-- The query in session 2 will resume execution
Oradebug Used To Test ADR Functionality
-- To create an incident, along with a couple of trace files, use the following command from SQL*Plus. You must connect as SYS as SYSDBA
/* You can then view, edit, and package the incident(s) with the Enterprise Manager Support Workbench (doc'd in Ch 8 of the Database Adminstrator's Guide) or with the ADRCI utility (doc'd in the Utilities Guide). */
Take a System State Dump
oradebug setmypid
oradebug unlimit
oradebug -g all dump systemstate 10
-- wait a minutes
-- rerun so that a comparison can be made to identify a hang
oradebug setmypid
oradebug unlimit
oradebug -g all dump systemstate 266
