UTL_RAW パッケージでブロックダンプをdecodingする

次のブロックダンプの結果を見てみましょう。

block_row_dump:
tab 0, row 0, @0x1f3d
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02  <-- RawにEncodingされている！
col  1: [ 2]  58 31 <-- RawにEncodingされている！
tab 0, row 1, @0x1f46
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 2]  58 32
...
tab 0, row 9, @0x1f8e
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 0b
col  1: [ 3]  58 31 30
end_of_block_dump

上のようにEncodingされた値はブロックダンプを解析する時に大変不便なことになります。でも、UTL_RAWパッケージを適当に使用すればEncodingされたColumnの値を手軽にDecodingできます。

下の例を御覧なさい。

UKJA@ukja102> create table t1(c1 number, c2 varchar2(10));

Table created.

Elapsed: 00:00:00.01
UKJA@ukja102>
UKJA@ukja102> insert into t1
  2  select level, 'X'||level
  3  from dual
  4  connect by level <= 10
  5  ;

10 rows created.

Elapsed: 00:00:00.01
UKJA@ukja102>
UKJA@ukja102> col f# new_value fno
UKJA@ukja102> col b# new_value bno
UKJA@ukja102>
UKJA@ukja102> select dbms_rowid.rowid_relative_fno(rowid) as f#,
  2     dbms_rowid.rowid_block_number(rowid) as b#
  3  from t1
  4  ;

        F#         B#
---------- ----------
         6        836
         6        836
         6        836
         6        836
         6        836
         6        836
         6        836
         6        836
         6        836
         6        836                                                           

10 rows selected.

Elapsed: 00:00:00.03
UKJA@ukja102>
UKJA@ukja102> alter system dump datafile &fno block &bno;
old   1: alter system dump datafile &fno block &bno
new   1: alter system dump datafile          6 block        836

UKJA@ukja102> @decode_block_dump T1    <-- 自動化したスクリプト

block_row_dump:
tab 0, row 0, @0x1f3d
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02 means C1 = 1
col  1: [ 2]  58 31 means C2 = X1
tab 0, row 1, @0x1f46
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03 means C1 = 2
col  1: [ 2]  58 32 means C2 = X2
tab 0, row 2, @0x1f4f
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04 means C1 = 3
col  1: [ 2]  58 33 means C2 = X3
tab 0, row 3, @0x1f58
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 05 means C1 = 4
col  1: [ 2]  58 34 means C2 = X4
tab 0, row 4, @0x1f61
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 06 means C1 = 5
col  1: [ 2]  58 35 means C2 = X5
tab 0, row 5, @0x1f6a
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 07 means C1 = 6
col  1: [ 2]  58 36 means C2 = X6
tab 0, row 6, @0x1f73
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 08 means C1 = 7
col  1: [ 2]  58 37 means C2 = X7
tab 0, row 7, @0x1f7c
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 09 means C1 = 8
col  1: [ 2]  58 38 means C2 = X8
tab 0, row 8, @0x1f85
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 0a means C1 = 9
col  1: [ 2]  58 39 means C2 = X9
tab 0, row 9, @0x1f8e
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 0b means C1 = 10
col  1: [ 3]  58 31 30 means C2 = X10
end_of_block_dump                                                               

DECODE_BLOCK_DUMP.SQLスクリプトは次のようです。UTL_RAWパッケージを呼び出す部分を注意して見てください。

define __TABLE_NAME = &1

set serveroutput on

declare
 v_varchar2  varchar2(4000);
 v_number   number;
 col_idx    number;
 col_type   varchar2(200);
 col_name   varchar2(100);
 col_value   varchar2(4000);

begin
 for r in (select column_value as txt from table(get_trace_file1)) loop
  dbms_output.put(r.txt);
  if regexp_like(r.txt, 'col[[:space:]]+[[:digit:]]+:') then 

   col_idx := regexp_replace(r.txt, 'col[[:space:]]+([[:digit:]])+: [[:print:]]+', '\1');

   select column_name, data_type into col_name, col_type
   from user_tab_cols
   where table_name = upper('&__TABLE_NAME')
      and column_id = col_idx+1
   ;

   col_value := replace(regexp_replace(r.txt, 'col[[:space:]]+[[:digit:]]+:[[:space:]]+\[[[:space:]]+[[:digit:]]\][[:space:]]+([[:print:]]+)', '\1'), ' ', '');
   if col_type = 'NUMBER' then
    --dbms_stats.convert_raw_value(col_value, v_number);
    v_number := utl_raw.cast_to_number(col_value);
    dbms_output.put(' means ' || col_name || ' = ' || v_number);
   elsif col_type = 'VARCHAR2' then
    --dbms_stats.convert_raw_value(col_value, v_varchar2);
    v_varchar2 := utl_raw.cast_to_varchar2(col_value);
    dbms_output.put(' means ' || col_name || ' = ' || v_varchar2);
   end if;
  end if;

  dbms_output.new_line;
 end loop;
end;
/

set serveroutput off

(GET_TRACE_FILE1の定義はここ）

もう、もっと楽にブロックダンプを解析することができるでしょう。