API tools faq
paste
ExaGridDba

IOT leaf and overflow block dump

ExaGridDba
May 17th, 2015
352
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Oracle 11 13.45 KB | None | 0 0
raw download clone embed print report
  1. Here are block dumps OF an IOT, showing PRIMARY KEY VALUES, non-KEY VALUES, overflow VALUES, AND in-ROW LOB VALUES.
  2.  
  3. Notes:
  4. 1. DATABASE character SET IS AL32UTF8. LOB characters are stored 2-bytes Unicode.
  5. 2. It IS necessary TO checkpoint the instance TO get reliable block DUMP info.
  6. 3. The NEW TABLESPACE IS FOR a clean slate.
  7. 4. We INSERT one ROW, DUMP ALL blocks IN extent 1, AND search FOR the columns WITH a text editor.
  8.  
  9. [oracle@stormking cdb12102 iot]$ sqlplus /nolog @ iotdump.SQL
  10.  
  11. SQL*Plus: RELEASE 12.1.0.2.0 Production ON Sun May 17 22:07:27 2015
  12.  
  13. Copyright (c) 1982, 2014, Oracle.  ALL rights reserved.
  14.  
  15. Connected.
  16. SQL> COLUMN segment_name new_value segment_name format a30
  17. SQL> COLUMN index_name new_value index_name format a30
  18. SQL> COLUMN table_name new_value table_name format a30
  19. SQL>
  20. SQL> CREATE TABLESPACE users;
  21.  
  22. TABLESPACE created.
  23.  
  24. SQL> ALTER USER u DEFAULT TABLESPACE users;
  25.  
  26. USER altered.
  27.  
  28. SQL>
  29. SQL> CREATE TABLE tbliot
  30.   2  (
  31.   3  p VARCHAR2(10),
  32.   4  q VARCHAR2(10),
  33.   5  a VARCHAR2(10),
  34.   6  b VARCHAR2(10),
  35.   7  CONSTRAINT tbliot_pk PRIMARY KEY( p,q),
  36.   8  l clob
  37.   9  )
  38.  10  ORGANIZATION INDEX
  39.  11  including a
  40.  12  overflow
  41.  13  lob(l) store AS securefile
  42.  14  (enable storage IN ROW);
  43.  
  44. TABLE created.
  45.  
  46. SQL>
  47. SQL> SELECT segment_name, segment_type, segment_subtype, tablespace_name FROM user_segments;
  48.  
  49. SEGMENT_NAME                   SEGMENT_TYPE       SEGMENT_SU TABLESPACE_NAME
  50. ------------------------------ ------------------ ---------- ------------------------------
  51. SYS_IOT_OVER_93296             TABLE              ASSM       USERS
  52. SYS_IL0000093296C00005$$       LOBINDEX           ASSM       USERS
  53. TBLIOT_PK                      INDEX              ASSM       USERS
  54. SYS_LOB0000093296C00005$$      LOBSEGMENT         SECUREFILE USERS
  55.  
  56. SQL> INSERT INTO tbliot(p,q,a,b,l) VALUES ( 'IIII','QQQQ','AAAA','BBBB','LLLL');
  57.  
  58. 1 ROW created.
  59.  
  60. SQL> COMMIT;
  61.  
  62. COMMIT complete.
  63.  
  64. SQL> ALTER SYSTEM checkpoint;
  65.  
  66. SYSTEM altered.
  67.  
  68. SQL> SELECT segment_name, segment_type, segment_subtype, tablespace_name FROM user_segments;
  69.  
  70. SEGMENT_NAME                   SEGMENT_TYPE       SEGMENT_SU TABLESPACE_NAME
  71. ------------------------------ ------------------ ---------- ------------------------------
  72. SYS_IOT_OVER_93296             TABLE              ASSM       USERS
  73. SYS_IL0000093296C00005$$       LOBINDEX           ASSM       USERS
  74. TBLIOT_PK                      INDEX              ASSM       USERS
  75. SYS_LOB0000093296C00005$$      LOBSEGMENT         SECUREFILE USERS
  76.  
  77. SQL>
  78. SQL> ALTER SESSION SET tracefile_identifier = iotpk;
  79.  
  80. SESSION altered.
  81.  
  82. SQL> @ dumpextent.SQL U TBLIOT_PK 0
  83. SQL> define owner=&&1
  84. SQL> define segment_name=&&2
  85. SQL> define exent_id=&&3
  86. SQL>
  87. SQL> COLUMN owner format a30
  88. SQL> COLUMN segment_name format a30
  89. SQL>
  90. SQL> COLUMN file_id new_value file_id
  91. SQL> COLUMN block_min new_value block_min
  92. SQL> COLUMN block_max new_value block_max
  93. SQL>
  94. SQL> SELECT owner, segment_name, extent_id, blocks, file_id, block_id,
  95.   2  block_id block_min, block_id + blocks - 1 block_max
  96.   3  FROM dba_extents
  97.   4  WHERE owner = '&&owner'
  98.   5  AND segment_name = '&&segment_name'
  99.   6  AND partition_name IS NULL
  100.   7  AND extent_id = &&exent_id;
  101. old   4: WHERE owner = '&&owner'
  102. NEW   4: WHERE owner = 'U'
  103. old   5: AND segment_name = '&&segment_name'
  104. NEW   5: AND segment_name = 'TBLIOT_PK'
  105. old   7: AND extent_id = &&exent_id
  106. NEW   7: AND extent_id = 0
  107.  
  108. OWNER                          SEGMENT_NAME                    EXTENT_ID     BLOCKS    FILE_ID   BLOCK_ID  BLOCK_MIN  BLOCK_MAX
  109. ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
  110. U                              TBLIOT_PK                               0          8         34        160        160        167
  111.  
  112. SQL>
  113. SQL> ALTER SYSTEM DUMP datafile &&file_id block MIN &&block_min block MAX &&block_max;
  114. old   1: ALTER SYSTEM DUMP datafile &&file_id block MIN &&block_min block MAX &&block_max
  115. NEW   1: ALTER SYSTEM DUMP datafile         34 block MIN        160 block MAX        167
  116.  
  117. SYSTEM altered.
  118.  
  119. SQL>
  120. SQL> @ tracefile.SQL
  121. SQL> COLUMN tracefile new_value tracefile format a120
  122. SQL>
  123. SQL> SELECT p.tracefile
  124.   2  FROM v$process p
  125.   3  join v$session s ON p.addr = s.paddr
  126.   4  WHERE s.sid = SYS_CONTEXT('userenv', 'sid' );
  127.  
  128. TRACEFILE
  129. ------------------------------------------------------------------------------------------------------------------------
  130. /u01/app/oracle/diag/rdbms/cdb12102/cdb12102/trace/cdb12102_ora_14814_IOTPK.trc
  131.  
  132. SQL>
  133. SQL>
  134. SQL> SELECT table_name, segment_name, index_name FROM user_lobs
  135.   2  WHERE table_name = 'TBLIOT' AND column_name = 'L';
  136.  
  137. TABLE_NAME                     SEGMENT_NAME                   INDEX_NAME
  138. ------------------------------ ------------------------------ ------------------------------
  139. TBLIOT                         SYS_LOB0000093296C00005$$      SYS_IL0000093296C00005$$
  140.  
  141. SQL> ALTER SESSION SET tracefile_identifier = lobseg;
  142.  
  143. SESSION altered.
  144.  
  145. SQL> @ dumpextent.SQL U &&segment_name 0
  146. SQL> define owner=&&1
  147. SQL> define segment_name=&&2
  148. SQL> define exent_id=&&3
  149. SQL>
  150. SQL> COLUMN owner format a30
  151. SQL> COLUMN segment_name format a30
  152. SQL>
  153. SQL> COLUMN file_id new_value file_id
  154. SQL> COLUMN block_min new_value block_min
  155. SQL> COLUMN block_max new_value block_max
  156. SQL>
  157. SQL> SELECT owner, segment_name, extent_id, blocks, file_id, block_id,
  158.   2  block_id block_min, block_id + blocks - 1 block_max
  159.   3  FROM dba_extents
  160.   4  WHERE owner = '&&owner'
  161.   5  AND segment_name = '&&segment_name'
  162.   6  AND partition_name IS NULL
  163.   7  AND extent_id = &&exent_id;
  164. old   4: WHERE owner = '&&owner'
  165. NEW   4: WHERE owner = 'U'
  166. old   5: AND segment_name = '&&segment_name'
  167. NEW   5: AND segment_name = 'SYS_LOB0000093296C00005$$'
  168. old   7: AND extent_id = &&exent_id
  169. NEW   7: AND extent_id = 0
  170.  
  171. OWNER                          SEGMENT_NAME                    EXTENT_ID     BLOCKS    FILE_ID   BLOCK_ID  BLOCK_MIN  BLOCK_MAX
  172. ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
  173. U                              SYS_LOB0000093296C00005$$               0         16         34        136        136        151
  174.  
  175. SQL>
  176. SQL> ALTER SYSTEM DUMP datafile &&file_id block MIN &&block_min block MAX &&block_max;
  177. old   1: ALTER SYSTEM DUMP datafile &&file_id block MIN &&block_min block MAX &&block_max
  178. NEW   1: ALTER SYSTEM DUMP datafile         34 block MIN        136 block MAX        151
  179.  
  180. SYSTEM altered.
  181.  
  182. SQL>
  183. SQL> @ tracefile.SQL
  184. SQL> COLUMN tracefile new_value tracefile format a120
  185. SQL>
  186. SQL> SELECT p.tracefile
  187.   2  FROM v$process p
  188.   3  join v$session s ON p.addr = s.paddr
  189.   4  WHERE s.sid = SYS_CONTEXT('userenv', 'sid' );
  190.  
  191. TRACEFILE
  192. ------------------------------------------------------------------------------------------------------------------------
  193. /u01/app/oracle/diag/rdbms/cdb12102/cdb12102/trace/cdb12102_ora_14814_LOBSEG.trc
  194.  
  195. SQL>
  196. SQL> ALTER SESSION SET tracefile_identifier = lobidx;
  197.  
  198. SESSION altered.
  199.  
  200. SQL> @ dumpextent.SQL U &&index_name 0
  201. SQL> define owner=&&1
  202. SQL> define segment_name=&&2
  203. SQL> define exent_id=&&3
  204. SQL>
  205. SQL> COLUMN owner format a30
  206. SQL> COLUMN segment_name format a30
  207. SQL>
  208. SQL> COLUMN file_id new_value file_id
  209. SQL> COLUMN block_min new_value block_min
  210. SQL> COLUMN block_max new_value block_max
  211. SQL>
  212. SQL> SELECT owner, segment_name, extent_id, blocks, file_id, block_id,
  213.   2  block_id block_min, block_id + blocks - 1 block_max
  214.   3  FROM dba_extents
  215.   4  WHERE owner = '&&owner'
  216.   5  AND segment_name = '&&segment_name'
  217.   6  AND partition_name IS NULL
  218.   7  AND extent_id = &&exent_id;
  219. old   4: WHERE owner = '&&owner'
  220. NEW   4: WHERE owner = 'U'
  221. old   5: AND segment_name = '&&segment_name'
  222. NEW   5: AND segment_name = 'SYS_IL0000093296C00005$$'
  223. old   7: AND extent_id = &&exent_id
  224. NEW   7: AND extent_id = 0
  225.  
  226. OWNER                          SEGMENT_NAME                    EXTENT_ID     BLOCKS    FILE_ID   BLOCK_ID  BLOCK_MIN  BLOCK_MAX
  227. ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
  228. U                              SYS_IL0000093296C00005$$                0          8         34        152        152        159
  229.  
  230. SQL>
  231. SQL> ALTER SYSTEM DUMP datafile &&file_id block MIN &&block_min block MAX &&block_max;
  232. old   1: ALTER SYSTEM DUMP datafile &&file_id block MIN &&block_min block MAX &&block_max
  233. NEW   1: ALTER SYSTEM DUMP datafile         34 block MIN        152 block MAX        159
  234.  
  235. SYSTEM altered.
  236.  
  237. SQL>
  238. SQL> @ tracefile.SQL
  239. SQL> COLUMN tracefile new_value tracefile format a120
  240. SQL>
  241. SQL> SELECT p.tracefile
  242.   2  FROM v$process p
  243.   3  join v$session s ON p.addr = s.paddr
  244.   4  WHERE s.sid = SYS_CONTEXT('userenv', 'sid' );
  245.  
  246. TRACEFILE
  247. ------------------------------------------------------------------------------------------------------------------------
  248. /u01/app/oracle/diag/rdbms/cdb12102/cdb12102/trace/cdb12102_ora_14814_LOBIDX.trc
  249.  
  250. SQL>
  251. SQL>
  252. SQL> COLUMN iot_over new_value iot_over format a30
  253. SQL> SELECT table_name iot_over, iot_name FROM user_tables WHERE iot_name = 'TBLIOT';
  254.  
  255. IOT_OVER                       IOT_NAME
  256. ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
  257. SYS_IOT_OVER_93296             TBLIOT
  258.  
  259. SQL> ALTER SESSION SET tracefile_identifier = iotoverflow;
  260.  
  261. SESSION altered.
  262.  
  263. SQL> @ dumpextent.SQL U &&iot_over 0
  264. SQL> define owner=&&1
  265. SQL> define segment_name=&&2
  266. SQL> define exent_id=&&3
  267. SQL>
  268. SQL> COLUMN owner format a30
  269. SQL> COLUMN segment_name format a30
  270. SQL>
  271. SQL> COLUMN file_id new_value file_id
  272. SQL> COLUMN block_min new_value block_min
  273. SQL> COLUMN block_max new_value block_max
  274. SQL>
  275. SQL> SELECT owner, segment_name, extent_id, blocks, file_id, block_id,
  276.   2  block_id block_min, block_id + blocks - 1 block_max
  277.   3  FROM dba_extents
  278.   4  WHERE owner = '&&owner'-
  279.   5  AND segment_name = '&&segment_name'
  280.   6  AND partition_name IS NULL
  281.   7  AND extent_id = &&exent_id;
  282. old   4: WHERE owner = '&&owner'
  283. NEW   4: WHERE owner = 'U'
  284. old   5: AND segment_name = '&&segment_name'
  285. NEW   5: AND segment_name = 'SYS_IOT_OVER_93296'
  286. old   7: AND extent_id = &&exent_id
  287. NEW   7: AND extent_id = 0
  288.  
  289. OWNER                          SEGMENT_NAME                    EXTENT_ID     BLOCKS    FILE_ID   BLOCK_ID  BLOCK_MIN  BLOCK_MAX
  290. ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
  291. U                              SYS_IOT_OVER_93296                      0          8         34        128        128        135
  292.  
  293. SQL>
  294. SQL> ALTER SYSTEM DUMP datafile &&file_id block MIN &&block_min block MAX &&block_max;
  295. old   1: ALTER SYSTEM DUMP datafile &&file_id block MIN &&block_min block MAX &&block_max
  296. NEW   1: ALTER SYSTEM DUMP datafile         34 block MIN        128 block MAX        135
  297.  
  298. SYSTEM altered.
  299.  
  300. SQL>
  301. SQL> @ tracefile.SQL
  302. SQL> COLUMN tracefile new_value tracefile format a120
  303. SQL>
  304. SQL> SELECT p.tracefile
  305.   2  FROM v$process p
  306.   3  join v$session s ON p.addr = s.paddr
  307.   4  WHERE s.sid = SYS_CONTEXT('userenv', 'sid' );
  308.  
  309. TRACEFILE
  310. ------------------------------------------------------------------------------------------------------------------------
  311. /u01/app/oracle/diag/rdbms/cdb12102/cdb12102/trace/cdb12102_ora_14814_IOTOVERFLOW.trc
  312.  
  313. SQL>
  314. SQL>
  315. SQL> DROP TABLE tbliot purge;
  316.  
  317. TABLE dropped.
  318.  
  319. SQL>
  320. SQL> ALTER USER u DEFAULT TABLESPACE SYSTEM;
  321.  
  322. USER altered.
  323.  
  324. SQL> DROP TABLESPACE users including contents AND datafiles;
  325.  
  326. TABLESPACE dropped.
  327.  
  328.  
  329. The INSERT statement was:
  330.  
  331. SQL> INSERT INTO tbliot(p,q,a,b,l) VALUES ( 'IIII','QQQQ','AAAA','BBBB','LLLL');
  332.  
  333. Refer TO this ASCII TABLE:
  334.  
  335.    Hex   CHAR
  336. -----------------
  337.    41    A
  338.    42    B
  339.    49    I
  340.    4C    L
  341.    51    Q
  342.  
  343. Refer TO these trace files:
  344.  
  345. /u01/app/oracle/diag/rdbms/cdb12102/cdb12102/trace/cdb12102_ora_14814_IOTPK.trc
  346.  
  347. Leaf block DUMP
  348. ===============
  349. header address 140336316215908=0x7fa29846f264
  350. kdxcolev 0
  351. KDXCOLEV Flags = - - -
  352. kdxcolok 0
  353. kdxcoopc 0x90: opcode=0: iot flags=I-- is converted=Y
  354. kdxconco 2
  355. kdxcosdc 0
  356. kdxconro 1
  357. kdxcofbo 38=0x26
  358. kdxcofeo 8006=0x1f46
  359. kdxcoavs 7968
  360. kdxlespl 0
  361. kdxlende 0
  362. kdxlenxt 0=0x0
  363. kdxleprv 0=0x0
  364. kdxledsz 0
  365. kdxlebksz 8032
  366.  
  367. Here are PRIMARY KEY VALUES 'IIII' AND 'QQQQ' AND non-KEY VALUE 'AAAA'
  368.  
  369. ROW#0[8006] flag: K------, lock: 2, len=26
  370. col 0; len 4; (4):  49 49 49 49
  371. col 1; len 4; (4):  51 51 51 51
  372. tl: 14 fb: --H-F--- lb: 0x0  cc: 1
  373. nrid:  0x08800085.0
  374. col  0: [ 4]  41 41 41 41
  375. ----- end of leaf block Logical dump -----
  376.  
  377. /u01/app/oracle/diag/rdbms/cdb12102/cdb12102/trace/cdb12102_ora_14814_IOTOVERFLOW.trc
  378.  
  379. Here IS overflow VALUE 'BBBB' AND in-ROW LOB VALUE 'LLLL':
  380.  
  381. block_row_dump:
  382. tab 0, ROW 0, @0x1f69
  383. tl: 47 fb: -----L-- lb: 0x1  cc: 2
  384. col  0: [ 4]  42 42 42 42
  385. col  1: [38]
  386.  00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 00 1a 6a b3 00 12 48 90 00
  387.  0c 00 00 08 01 00 4c 00 4c 00 4c 00 4c
  388. LOB
  389. Locator:
  390.   LENGTH:        84(38)
  391.   Version:        1
  392.   Byte LENGTH:    2
  393.   LobID: 00.00.00.01.00.00.00.1a.6a.b3
  394.   Flags[ 0x02 0x0c 0x80 0x80 ]:
  395.     TYPE: CLOB
  396.     Storage: SecureFile
  397.     Characterset Format: IMPLICIT
  398.     Partitioned TABLE: No
  399.     Options: VaringWidthReadWrite
  400.   SecureFile Header:
  401.     LENGTH:   18
  402.     Old Flag: 0x48 [ DataInRow SecureFile ]
  403.     Flag 0:   0x90 [ INODE Valid ]
  404.     Layers:
  405.       Lengths ARRAY: INODE:12
  406.       INODE:
  407.         00 00 08 01 00 4c 00 4c 00 4c 00 4c
  408. end_of_block_dump
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!