| GENERAL |
| Background Processes List |
SELECT b.name, p.pid
FROM gv$bgprocess b, gv$process p
WHERE b.paddr = p.addr
ORDER BY 1; |
| Dispatcher Processes List |
SELECT d.name, p.pid
FROM gv$dispatcher d, gv$process p
WHERE d.paddr = p.addr; |
| Job Queue Process List |
SELECT s.paddr, s.sid, j.job
FROM gv$session s, dba_jobs_running j
WHERE s.sid = j.sid;
SELECT pid
FROM gv$process
WHERE addr = '17'; |
| Parallel Execution Slave Processes List |
SELECT pid, server_name, status
FROM gv$px_process; |
| Shared Server Processes List |
SELECT s.name, p.pid
FROM gv$shared_server s, gv$process p
WHERE s.paddr = p.addr; |
| SGA Variables List |
SELECT ksmfsnam
FROM x$ksmfsv
WHERE ksmfsnam LIKE '%\_' ESCAPE '\'; |
| Switches |
| Switch |
Format |
Description |
| -G |
<Inst-List | def | all> |
Parallel oradebug command prefix |
| -R |
<Inst-List | def | all> |
Parallel oradebug prefix return output |
|
| |
| CALL |
| Invoke function with arguments |
oradebug call <func> [arg1] ... [argn] |
| SQL> oradebug call ? |
| |
| CLOSE_TRACE |
| Close trace file |
oradebug close_trace |
| SQL> oradebug close_trace |
| |
| CORE |
| Dump core without crashing process |
oradebug core |
| SQL> oradebug core |
| |
| CURRENT_SQL |
Get current SQL |
oradebug current_sql |
| SYS |
UWCLASS |
| conn / as sysdba |
conn uwclass/uwclass |
GRANT execute ON dbms_support
TO uwclass; |
|
| |
SELECT pid
FROM gv$process
WHERE addr = (
SELECT paddr
FROM gv$session
WHERE sid =
sys.dbms_support.mysid); |
| oradebug setorapid 26 |
|
| |
SELECT t.tablespace_name
FROM all_tables t, all_indexes i
WHERE t.tablespace_name = i.tablespace_name; |
| oradebug core |
|
|
| |
| DELETE |
| Delete a watchpoint |
oradebug delete <local|global|target> watchpoint <id> |
| SQL> oradebug delete ? |
| |
| DIRECT_ACCESS |
| Fixed table access |
oradebug direct_access <set/enable/disable command | select query> |
| TBD |
| |
| DMPCOWSGA |
| Dump & map SGA as COW |
oradebug dmpcowsga <SGA dump dir> |
| SQL> oradebug dmpcowsga "c: emp" |
| |
| DUMP |
| Invoke named dump |
oradebug dump <dump_name> <lvl> [addr] |
-- dump_name values can be obtained from oradebug dumplist
SQL> oradebug setmypid
SQL> oradebug dump library_cache 4 |
| Invoke ASH dump |
SQL> oradebug setmypid
SQL> oradebug dump ashdump 10 |
| |
| DUMPLIST |
Print a list of available dumps |
oradebug dumplist |
SQL> oradebug dumplist
|
Available Dumps
|
| ADJUST_SCN |
HANGDIAG_HEADER |
MMAN_ALLOC_MEMORY |
| ALRT_TEST |
HEAPDUMP |
MMAN_CREATE_DEF_REQUEST |
| ARCHIVE_ERROR |
HEAPDUMP_ADDR |
MMAN_CREATE_IMM_REQUEST |
| ASHDUMP |
HM_FW_TRACE |
MMAN_IMM_REQUEST |
| ATSK_TEST |
INSTANTIATIONSTATE |
MMON_TEST |
| AWR_FLUSH_TABLE_OFF |
IOERREMUL |
MODIFIED_PARAMETERS |
| AWR_FLUSH_TABLE_ON |
IOERREMULRNG |
NEXT_SCN_WRAP |
| AWR_TEST |
IR_FW_TRACE |
OBJECT_CACHE |
| BC_SANITY_CHECK |
JAVAINFO |
OCR |
| BG_MESSAGES |
KCBO_OBJ_CHECK_DUMP |
OLAP_DUMP |
| BLK0_FMTCHG |
KDLIDMP |
OPEN_FILES |
| BUFFER |
KRA_OPTIONS |
PGA_DETAIL_CANCEL |
| BUFFERS |
KRA_TRACE |
PGA_DETAIL_DUMP |
| CALLSTACK |
KRB_BSET_DAYS |
PGA_DETAIL_GET |
| CHECK_ROREUSE_SANITY |
KRB_CORRUPT_INTERVAL |
PIN_BLOCKS |
| CONTEXTAREA |
KRB_CORRUPT_REPEAT |
PIN_RANDOM_BLOCKS |
| CONTROLF |
KRB_CORRUPT_SIZE |
POKE_ADDRESS |
| CROSSIC |
KRB_CORRUPT_SPBAD_INTERVAL |
POKE_LENGTH |
| CRS |
KRB_CORRUPT_SPBAD_REPEAT |
POKE_VALUE |
| CSS |
KRB_CORRUPT_SPBITMAP_INTER |
POKE_VALUE0 |
| CURSOR_STATS |
KRB_CORRUPT_SPBITMAP_REPEA |
POOL_SIMULATOR |
| CURSORDUMP |
KRB_CORRUPT_SPHEADER_INTER |
PROCESSSTATE |
| CURSORTRACE |
KRB_CORRUPT_SPHEADER_REPEA |
REALFREEDUMP |
| DATA_ERR_OFF |
KRB_FAIL_INPUT_FILENO |
RECORD_CALLSTACK |
| DATA_ERR_ON |
KRB_OPTIONS |
RECOVERY |
| DBSCHEDULER |
KRB_OVERWRITE_ACTION |
REDOHDR |
| DROP_SEGMENTS |
KRB_PIECE_FAIL |
REDOLOGS |
| DUMP_ADV_SNAPSHOTS |
KRB_SET_TIME_SWITCH |
REFRESH_OS_STATS |
| DUMP_ALL_COMP_GRANULES |
KRB_SIMULATE_NODE_AFFINITY |
ROW_CACHE |
| DUMP_ALL_COMP_GRANULE_ADDR |
KRB_TRACE |
RULESETDUMP |
| DUMP_ALL_OBJSTATS |
KRB_UNUSED_OPTION |
RULESETDUMP_ADDR |
| DUMP_ALL_REQS |
KRBMRSR_LIMIT |
SAVEPOINTS |
| DUMP_PINNED_BUFFER_HISTORY |
KRBMROR_LIMIT |
SELFTESTASM |
| DUMP_TEMP |
KRC_TRACE |
SET_NBLOCKS |
| DUMP_TRANSFER_OPS |
KSDTRADV_TEST |
SET_TSN_P1 |
| DUMPGLOBALDATA |
KSFQP_LIMIT |
SHARED_SERVER_STATE |
| ENQUEUES |
KSKDUMPTRACE |
SIMULATE_EOV |
| ERRORSTACK |
KTPR_DEBUG |
SYSTEMSTATE |
| EVENT_TSM_TEST |
KSTDUMPALLPROCS |
SYSTEMSTATE_GLOBAL |
| EXCEPTION_DUMP |
KSTDUMPALLPROCS_CLUSTER |
TEST_DB_ROBUSTNESS |
| FAILOVER |
KSTDUMPCURPROC |
TEST_GET_CALLER |
| FBHDR |
KUPPLATCHTEST |
TEST_SPACEBG |
| FBINC |
KXFPCLEARSTATS |
TEST_STACK_DUMP |
| FBTAIL |
KXFPDUMPTRACE |
TR_CRASH_AFTER_WRITE |
| FILE_HDRS |
KXFPBLATCHTEST |
TR_CORRUPT_ONE_SIDE |
| FLASHBACK_GEN |
KXFXCURSORSTATE |
TR_READ_ONE_SIDE |
| FLUSH_CACHE |
KXFXSLAVESTATE |
TR_SET_ALL_BLOCKS |
| FLUSH_JAVA_POOL |
LATCHES |
TR_SET_BLOCK |
| FULL_DUMPS |
LDAP_KERNEL_DUMP |
TR_SET_SIDE |
| GC_ELEMENTS |
LDAP_USER_DUMP |
TRACE_BUFFER_OFF |
| GES_STATE |
LIBRARY_CACHE |
TRACE_BUFFER_ON |
| GLOBAL_AREA |
LOCKS |
TREEDUMP |
| HANGANALYZE |
LOGERROR |
TR_RESET_NORMAL |
| HANGANALYZE_PROC |
LOGHIST |
UPDATE_BLOCK0_FORMAT |
| HANGANALYZE_GLOBAL |
LONGF_CREATE |
WORKAREATAB_DUMP |
|
| |
| DUMPSGA |
| Dump fixed SGA |
oradebug dumpsga
oradebug dumpsga <bytes> |
SQL> oradebug dumpsga
c:\oracle\product\admin\orabase\udump\orabase_ora_2120.trc |
| |
| DUMPTYPE |
| Print/dump an address with type info |
oradebug dumptype <address> <type> <count> |
| TBD |
| |
| DUMPVAR |
| Print/dump a fixed PGA/SGA/UGA variable |
oradebug dumpvar <pga|sga|uga> <name> [level] |
SQL> oradebug setmypid
SQL> oradebug dumpvar SGA kcbnbh |
| |
| EVENT |
| Set trace event in process |
oradebug EVENT <event>
TRACE NAME CONTEXT FOREVER, LEVEL <level> |
SQL> oradebug setmypid
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
SQL> oradebug unlimit |
| |
| FFBEGIN |
| Flash Freeze the Instance |
oradebug ffbegin |
| SQL> oradebug ffbegin |
| |
| FFDEREGISTER |
| FF deregister instance from cluster |
oradebug ffderegistger |
| SQL> oradebug ffderegister |
| |
| FFRESUMEINST |
| Resume the flash frozen instance |
oradebug ffresumeinst |
| SQL> oradebug ffresumeinst |
| |
| FFSTATUS |
| Flash freeze status of instance |
oradebug ffstatus |
| SQL> oradebug ffstatus |
| |
| FFTERMINST |
| Call exit and terminate instance |
oradebug ffterminst |
| SQL> oradebug ffterminst |
| |
| FLUSH |
| Flush the current contents of the trace buffer to the trace file use |
oradebug flush |
| SQL> oradebug flush |
| |
| HANGANALYZE |
Analyze system hang for stand-alone |
oradebug hanganalzye [level] [syslevel] |
SQL> oradebug setmypid;
SQL> oradebug hanganalyze;
SQL> oradebug flush; |
| Analyze system hang for RAC |
SQL> oradebug setmypid;
SQL> oradebug -g def hanganalyze 1
SQL> oradebug flush; |
| |
| HELP |
| Describe one or all commands |
oradebug help
oradebug help <command> |
SQL> oradebug help
SQL> oradebug help flush |
| |
| LKDEBUG |
| Invoke global enqueue service debugger |
oradebug lkdebug |
| SQL> oradebug lkdebug |
| |
| MAPCOWSGA |
| Map SGA as COW |
oradebug mapcowsga <SGA dump dir> |
| SQL> oradebug mapcowsga "c: emp" |
| |
| NSDBX |
| Invoke CGS name-service debugger |
oradebug nsdbx |
| SQL> oradebug nsdbx |
| |
| PEEK |
| Print/Dump memory |
oradebug peek <addr> <len> [level] |
| SQL> oradebug peek 0x20005F0C 12 |
| |
| POKE |
| Modify memory. Never perform this function on a production database! |
oradebug poke <addr> <len> <value> |
| SQL> oradebug poke 0x20005F0C 4 0x46495845 |
| |
| PROCSTAT |
| Dump process statistics |
oradebug procstat |
SQL> oradebug setmypid
SQL> oradebug procstat |
| |
| RESUME |
| Resume execution |
oradebug resume |
| SQL> oradebug resume |
| |
| SESSION_EVENT |
| Set trace event in session |
oradebug session_event <text> |
| SQL> oradebug session_event 10053 TRACE NAME CONTEXT FOREVER, LEVEL 1 |
| |
| SETINST |
| Set instance list |
oradebug setinst <instance# .. | all> |
| SQL> oradebug setinst "1" |
| |
| SETMYPID |
| Sets the oradebug PID to the current process |
oradebug setmypid |
| SQL> oradebug setmypid |
| |
| SETORAPID |
Set PID of Oracle process to debug |
oradebug setorapid <orapid> ['force'] |
SELECT pid
FROM gv$process
WHERE addr = (
SELECT paddr
FROM gv$session
WHERE sid = DBMS_SUPPORT.MYSID);
or
SELECT pid
FROM gv$process
WHERE addr = (
SELECT paddr
FROM gv$session
WHERE sid = (SELECT sid FROM gv$mystat WHERE ROWNUM = 1));
/*
or one of the processes from the background, dispatcher, job queue, parallel, or shared server process lists
*/
SQL> oradebug setorapid 19 |
| |
| SETOSPID |
Set OS pid of process to debug
The operating system process ID is the PID on Unix systems and the thread number for Windows systems |
oradebug setospid <ospid> |
| Do not use as it often fails. Use setorapid instead. |
| |
| SETORAPNAME |
| Set Oracle process name to debug |
oradebug setorapname <orapname> |
| TBD |
| |
| SETVAR |
| Modify a fixed PGA/SGA/UGA variable |
oradebug setvar <pga|sga|uga> <name> <value> |
| SQL> oradebug setvar SGA kcfdfk 200 |
| |
| SGATOFILE |
| Dump SGA to file |
oradebug sgatofile <SGA dump dir> |
SQL> oradebug ffbegin
SQL> oradebug sgatofile "c: emp"
SQL> oradebug ffresumeinst |
| |
| SHORT_STACK |
| Get abridged OS stack |
oradebug short_stack |
| SQL> oradebug short_stack |
| |
| SHOW |
| Show watchpoints |
oradebug show <local|global|target> watchpoint <id> |
| SQL> oradebug show? |
| |
| SKDSTTPCS |
| Helps translate PCs to names |
oradebug skdsttpcs <ifname> <ofname> |
| SQL> oradebug skdsttpcs? |
| |
| SUSPEND |
| Suspends the current process |
oradebug suspend |
| SQL> oradebug suspend |
| |
| TRACEFILE_NAME |
Get trace file name
Will not return a value on Windows systems |
oradebug tracefile_name |
| SQL> oradebug tracefile_name |
| |
| UNLIMIT |
| Unlimit the size of the trace file |
oradebug unlimit |
| SQL> oradebug unlimit |
| |
| WAKEUP |
| Wake up Oracle process |
oradebug wakeup <orapid> |
SELECT pid
FROM gv$process
WHERE addr = (
SELECT paddr
FROM gv$bgprocess
WHERE name = 'SMON');
SQL> oradebug wakeup 7 |
| |
| WATCH |
| Watch a region of memory |
oradebug watch <address> <len> <self|exist|all|target> |
| SQL> oradebug watch? |
| |
| Demo |
Taking a heap dump during a large (sorting) query |
-- 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 gv$process
WHERE addr IN (
SELECT paddr
FROM gv$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 |
