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の定義はここ）
もう、もっと楽にブロックダンプを解析することができるでしょう。
Posts
