Dumping Oracle
Version 12.1.0.1

General Information
This isn't what you were thinking ... but you have to admit it is a great name. Why would anyone "dump" the best commercial database product on the planet?

Well to create trace files of course ... so we shall. And this page will become an accumulation of tips-and-tricks on various techniques for doing so.
 
Database
Dump database block

Demo provided by Richard Foote, Canberra, Australia
CREATE TABLE bowie_stuff (
album  VARCHAR2(30),
year   NUMBER,
rating VARCHAR2(30));

INSERT INTO bowie_stuff VALUES ('Man Who Sold The World', 1970, 'Bloody Good!!');

INSERT INTO bowie_stuff VALUES ('Diamond Dogs', 1974 , 'Brilliant');

INSERT INTO bowie_stuff VALUES ('Outside', 1995, 'Underrated Masterpiece');
COMMIT;

ALTER SYSTEM CHECKPOINT;

SELECT *
FROM bowie_stuff;

SELECT album, dbms_rowid.rowid_to_absolute_fno(rowid, 'UWCLASS', 'BOWIE_STUFF') ABSOLUTE_FNO,
dbms_rowid.rowid_block_number(rowid) BLOCKNO, dbms_rowid.rowid_row_number(rowid) ROWNUMBER
FROM bowie_stuff
WHERE album LIKE '%Dogs%';

ALTER SYSTEM DUMP DATAFILE 14 BLOCK 20238;

Trace file C:\APP\ORACLE\diag\rdbms\orabase2\orabase2\trace\orabase2_ora_8284.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU : 4 - type 8664, 2 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:3425M/8075M, Ph+PgF:11352M/16148M
Instance name: orabase2
Redo thread mounted by this instance: 1
Oracle process number: 46
Windows thread id: 8284, image: ORACLE.EXE (SHAD)


*** 2014-01-05 10:17:07.304
*** SESSION ID:(246.1839) 2014-01-05 10:17:07.304
*** CLIENT ID:() 2014-01-05 10:17:07.304
*** SERVICE NAME:(pdbdev) 2014-01-05 10:17:07.304
*** MODULE NAME:(SQL*Plus) 2014-01-05 10:17:07.304
*** ACTION NAME:() 2014-01-05 10:17:07.304
*** CONTAINER ID:(4) 2014-01-05 10:17:07.304

Start dump data blocks tsn: 4 file#:14 minblk 20238 maxblk 20238
Block dump from cache:
Dump of buffer cache at level 4 for pdb=4 tsn=4 rdba=58740494
BH (0x7ff15f85a98) file#: 14 rdba: 0x03804f0e (14/20238) class: 1 ba: 0x7ff153c6000
  set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 29,28
  dbwrid: 0 obj: 93311 objn: 93311 tsn: [4/4] afn: 14 hint: f
  hash: [0x7ff6fff2b10,0x7ff91b40628] lru: [0x7ff15f85cc8,0x7ff15f85a48]
  ckptq: [NULL] fileq: [NULL]
  objq: [0x7ff15f85cf0,0x7ff8010fa28] objaq: [0x7ff15f85d00,0x7ff8010fa18]
  st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' fscn: 0x0.626ef5 tch: 5
  flags: block_written_once
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 4 rdba: 0x03804f0e (14/20238)
scn: 0x0.626ef6 seq: 0x01 flg: 0x06 tail: 0x6ef60601
frmt: 0x02 chkval: 0x0fd5 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000015B46000 to 0x0000000015B48000
015B46000 0000A206 03804F0E 00626EF6 06010000 [.....O...nb.....]
015B46010 00000FD5 00000001 00016C7F 00626EF5 [.........l...nb.]
015B46020 00000000 00320002 03804F00 00100001 [......2..O......]
015B46030 00001311 01400C03 002605CC 00002003 [......@...&.. ..]
015B46040 00626EF6 00000000 00000000 00000000 [.nb.............]
015B46050 00000000 00000000 00000000 00000000 [................]
015B46060 00000000 00030100 0018FFFF 1F101F28 [............(...]
015B46070 00001F10 1F6C0003 1F281F4E 00000000 [......l.N.(.....]
015B46080 00000000 00000000 00000000 00000000 [................]
        Repeat 495 times
015B47F80 00000000 00000000 00000000 0703012C [............,...]
015B47F90 7374754F 03656469 166014C2 65646E55 [Outside...`.Unde]
015B47FA0 74617272 4D206465 65747361 65697072 [rrated Masterpie]
015B47FB0 012C6563 69440C03 6E6F6D61 6F442064 [ce,...Diamond Do]
015B47FC0 C2037367 42094B14 6C6C6972 746E6169 [gs...K.Brilliant]
015B47FD0 1603012C 206E614D 206F6857 646C6F53 [,...Man Who Sold]
015B47FE0 65685420 726F5720 C203646C 420D4714 [ The World...G.B]
015B47FF0 646F6F6C 6F472079 2121646F 6EF60601 [loody Good!!...n]
Block header dump: 0x03804f0e
 Object id on Block? Y
 seg/obj: 0x16c7f csc: 0x00.626ef5 itc: 2 flg: E typ: 1 - DATA
     brn: 0 bdba: 0x3804f00 ver: 0x01 opc: 0
     inc: 0 exflg: 0

 Itl           Xid                   Uba         Flag  Lck        Scn/Fsc
0x01   0x0001.010.00001311   0x01400c03.05cc.26  --U-    3  fsc 0x0000.00626ef6
0x02   0x0000.000.00000000   0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x03804f0e
data_block_dump,data header at 0x15b46064
===============
tsiz: 0x1f98
hsiz: 0x18
pbl:  0x15b46064
      76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f28
avsp=0x1f10
tosp=0x1f10
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f6c
0x14:pri[1]     offs=0x1f4e
0x16:pri[2]     offs=0x1f28
block_row_dump:
tab 0, row 0, @0x1f6c
tl: 44 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [22]
 4d 61 6e 20 57 68 6f 20 53 6f 6c 64 20 54 68 65 20 57 6f 72 6c 64
col 1: [ 3] c2 14 47
col 2: [13] 42 6c 6f 6f 64 79 20 47 6f 6f 64 21 21
tab 0, row 1, @0x1f4e
tl: 30 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [12] 44 69 61 6d 6f 6e 64 20 44 6f 67 73
col 1: [ 3] c2 14 4b
col 2: [ 9] 42 72 69 6c 6c 69 61 6e 74
tab 0, row 2, @0x1f28
tl: 38 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 7] 4f 75 74 73 69 64 65
col 1: [ 3] c2 14 60
col 2: [22]
 55 6e 64 65 72 72 61 74 65 64 20 4d 61 73 74 65 72 70 69 65 63 65
end_of_block_dump
End dump data blocks tsn: 4 file#: 14 minblk 20238 maxblk 20238
File Headers ALTER SYSTEM SET EVENTS 'immediate trace name file_hdrs level 1';

-- does not appear to generate a file
Redlo Log File Headers ALTER SYSTEM SET EVENTS 'immediate trace name redohdr level 1';

-- does not appear to generate a file
Control File

Level 1: file header
Level 2: database and checkpoint records
Level 3: circular reuse record types
Level 10: dump contents
col value format a50

SELECT value
FROM gv$parameter
WHERE name LIKE 'back%dump%dest';

ALTER SESSION SET EVENTS 'immediate trace name controlf level 3';

[Click here to view trace file (116K)]
 
Instance
PGA (Process Global Area) ORADEBUG DUMP HEAPDUMP 1

ALTER SESSION SET EVENTS 'immediate trace name global_area level 1';
SGA (System Global Area) ORADEBUG DUMP HEAPDUMP 2

ALTER SESSION SET EVENTS 'immediate trace name global_area level 2';
PGA + UGA ORADEBUG DUMP HEAPDUMP 3

ALTER SESSION SET EVENTS 'immediate trace name global_area level 3';
UGA (User Global Area) ORADEBUG DUMP HEAPDUMP 4

ALTER SESSION SET EVENTS 'immediate trace name global_area level 4';
PGA + UGA ORADEBUG DUMP HEAPDUMP 5

ALTER SESSION SET EVENTS 'immediate trace name global_area level 5';
SGA + UGA ORADEBUG DUMP HEAPDUMP 6

ALTER SESSION SET EVENTS 'immediate trace name global_area level 6';
PGA + SGA + UGA ORADEBUG DUMP HEAPDUMP 7

ALTER SESSION SET EVENTS 'immediate trace name global_area level 7';
System State ALTER SESSION SET EVENTS 'immediate trace name SYSTEMSTATE level 10';

ALTER SESSION SET EVENTS 'immediate trace name SYSTEMSTATE_CACHE OFF';

[Click here to view trace file (1.5M)]
Process State ALTER SESSION SET EVENTS 'immediate trace name PROCESSSTATE level 10';

ALTER SESSION SET EVENTS 'immediate trace name PROCESSSTATE OFF';

[Click here to view trace file (347K)]
Library Cache ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 10';

ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE OFF';

[Click here to view trace file (12K)]
 
Segments
Datafile Block ALTER SYSTEM DUMP DATAFILE 6 BLOCK 21229;
Dumping an index tree including branch block headers, leaf block headers, and leaf block contents col object_name format a30

SELECT object_name, object_id
FROM user_objects;

ALTER SESSION SET EVENTS 'immediate trace name treedump level 54220';
Alternative index dump ORADEBUG DUMP TREEDUMP 54220;

Related Topics
DBMS_JAVA_DUMP
DBMS_ROWID
ORADEBUG
SYSTEM

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved