Dump commands in Oracle

Common dump commands in Oracle for future reference.

1. Memory Dumps

1). Global Area
Alter session set events 'immediate trace name global_area level n ';
1 Including PGA
2 contains SGA
4 include UGA
8 contains indrect memory

2). Library Cache
Alter session set events 'immediate trace name library_cache level n ';
1. library cache statistics
2 contains hash table histogram
3 include object handle
4 contains the object structure (Heap 0)

3). Row Cache
Alter session set events 'immediate trace name row_cache level n ';
1 row cache statistics
2 contains hash table histogram
8 contains the object structure

4). Buffers
Alter session set events 'immediate trace name buffers level n ';
1 buffer header
2 level 1 + block header
3 level 2 + block contents
4 level 1 + hash chain
5 level 2 + hash chain
6 level 3 + hash chain
8 level 4 + users/waiters
9 level 5 + users/waiters
10 level 6 + users/waiters

5). Buffer
Alter session set events 'immediate trace name buffer level n ';
N is the rdba of a specified block. This command can be used to dump all versions of a block in the buffer.

6). Heap
Alter session set events 'immediate trace name heapdump level level ';
1 PGA Summary
2 SGA Abstract
4 UGA Summary
8 Current call (CGA) Abstract
16 User call (CGA) Abstract
32 Large call (LGA) Abstract
1025 PGA content
2050 SGA content
4100 UGA content
8200 Current call content
16400 User call content
32800 Large call content

7). Sub Heap
Before Oracle 9.0.1
Alter session set events 'immediate trace name heapdump_addr level n ';
If n is the subheap address, the digest information of the subheap is dumped.
If n is the subheap address + 1, the dumped content is the subheap content.
Versions later than Oracle 9.2.0
Alter session set events 'immediate trace name heapdump_addr level n, addr m ';
M is the subheap address.
N is the digest of 1 dump subheap, n is the content of 2 dump subheap

8). Process State
Alter session set events 'immediate trace name processstate level n ';

9). System State
Alter session set events 'immediate trace name systemstate level n ';

10). Error State
Alter session set events 'immediate trace name errorstack level n ';
0 Error stack
1 level 0 + function call stack
2 level 1 + process state
3 level 2 + context area
11). Hang Analysis
Alter session set events 'immediate trace name hanganalyze level n ';

12). Work Area
Alter session set events 'immediate trace name workareatab_dump level n ';
1. SGA Information
2. Workarea Table Summary
3. Workarea Table details

13). Latches
Alter session set events 'immediate trace name latches level n ';
1. latch Information
2. Statistics

14). Events
Alter session set events 'immediate trace name events level n ';
1 session
2 process
3 system

15). Locks
Alter session set events 'immediate trace name locks level n ';

16). Shared Server Process
Alter session set events 'immediate trace name shared_server_state level n ';
N is 1 ~ 14

17). Background Messages
Alter session set events 'immediate trace name bg_messages level n ';
N is pid + 1

Ii. File Dumps

1). Block
Before Oracle 7
Alter session set events 'immediate trace name blockdump level n ';
Rdba whose n is block
After Oracle8
Alter system dump datafile file # BLOCK block #;
Alter system dump datafile file #
Block min minimum_block #
Block max maximum_block #;

2). Tree Dump
Alter session set events 'immediate trace name treedump level n ';
N is object_id

3). Undo Segment Header
Alter system dump UNDO_HEADER 'segment _ name ';

4). Undo for a Transaction
Alter system dump undo block 'segment _ name' XID xidusn xidslot xidsqn;

5). File Header
Alter session set events 'immediate trace name file_hdrs level n ';
1. File Header Information in the control file
2 level 1 + file header information
3 level 2 + data file header information
10 level 3

6). Control file
Alter session set events 'immediate trace name controlf level n ';
1. File Header Information
2 level 1 + database information + checkpoint Information
3 level 2 + reusable section information
10 level 3

7). Redo log Header
Alter session set events 'immediate trace name redohdr level n ';
1. redo log information in the control file
2 level 1 + file header information
3 level 2 + Log File Header Information
10 level 3

8). Redo log
Alter system dump logfile 'filename ';
Alter system dump logfile 'filename'
Scn min MinimumSCN
Scn max MaximumSCN
Time min MinimumTime
Time max MaximumTime
LAYER Layer
OPCODE Opcode
Dba min FileNumber. BlockNumber
Dba max FileNumber. BlockNumber
Rba min LogFileSequenceNumber. BlockNumber
Rba max LogFileSequenceNumber. BlockNumber;
Where time = (yyyy-1988) * 12 + mm-1) * 31 + dd-1) * 24 + hh) * 60 + mi) * 60 + ss;

9). Loghist
Alter session set events 'immediate trace name loghist level n ';
1. The earliest and latest log history items in the dump Control File
1 dump 2 ^ n log history items