[20250513]bbed读取数据块8 fffext.sh.txt
[20250513]bbed读取数据块8 fffext.sh.txt
--//测试看看，如果出现行链接是否可行。看了笔记以前测试过，再重复测试看看。
--//链接：[20210319]bbed读取数据块3.txt，另外以前的读取脚本有1个小问题，这次更正
--//grep -B1 --no-group-separator "ub4 ktbbhod1" ,这样匹配输出前一行一定是dba信息。
--//以前测试过，看看我修改的版本能否解决以前遇到的问题(注:以前版本删除记录后输出空行)以及行迁移问题。
1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.测试环境建立：
SCOTT@book01p> create table t6 as select * from all_objects where rownum<=1000;
Table created.
SCOTT@book01p> @ o2z t6
==============================
O_OWNER : SCOTT
O_OBJECT_NAME : T6
O_OBJECT_TYPE : TABLE
SEG_PART_NAME :
O_STATUS : VALID
OID : 129064
D_OID : 129064
CREATED : 2025-05-13 15:54:43
LAST_DDL_TIME : 2025-05-13 15:54:43
PL/SQL procedure successfully completed.
SCOTT@book01p> @ bbedcol12 scott t6
Display bbed examine(x) FORMAT scott.t6 : cccnncttcccccncccccccccnnnn
SCOTT@book01p> select rowid from t6 where rownum=1;
ROWID
------------------
AAAfgoAAMAAAACrAAA
SCOTT@book01p> @ rowid AAAfgoAAMAAAACrAAA
DATA_OBJECT_ID FILE BLOCK ROW ROWID_DBA DBA TEXT
-------------- ---------- ---------- ---------- -------------------- -------------------- --------------------------------------------------
129064 12 171 0 0x30000AB 12,171 alter system dump datafile 12 block 171 ;
SCOTT@book01p> column PARTITION_NAME noprint
SCOTT@book01p> select * from dba_extents where segment_name='T6';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
----- ------------ ------------ --------------- --------- ---------- ---------- ---------- ---------- ------------
SCOTT T6 TABLE USERS 0 12 168 65536 8 12
SCOTT T6 TABLE USERS 1 12 176 65536 8 12
SCOTT T6 TABLE USERS 2 12 184 65536 8 12
3.删除记录测试：
SCOTT@book01p> delete from t6 where mod(object_id,100)=0;
8 rows deleted.
SCOTT@book01p> commit ;
Commit complete.
SCOTT@book01p> alter system checkpoint;
System altered.
$ . fffext.sh 12 168 $[ 184+8-1 ] 129064 cccnncttcccccncccccccccnnnn|wc
992 3442 135704
--//可以发现新的版本已经解决这个问题。
SCOTT@book01p> @ versions t6 OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID s '' '' "versions_operation='D' "
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V ROWID OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- -------------------- ----------------- --------------- ---------------- - ------------------ ----- ------------------------------ ---------- --------------
2025-05-13 16:05:35. 41624605 010020006F1F0000 D AAAfgoAAMAAAACsAAg SYS SEQ$ 100 100
2025-05-13 16:05:35. 41624605 010020006F1F0000 D AAAfgoAAMAAAACvAAM SYS TRIGGERJAVAF$ 300 300
2025-05-13 16:05:35. 41624605 010020006F1F0000 D AAAfgoAAMAAAACwAAZ SYS I_SETTINGS1 400 400
2025-05-13 16:05:35. 41624605 010020006F1F0000 D AAAfgoAAMAAAAC7AAT SYS STREAMS$_COMPONENT_PROP_IND 1100 1100
2025-05-13 16:05:35. 41624605 010020006F1F0000 D AAAfgoAAMAAAACzAAd SYS RADM_TD$ 600 600
2025-05-13 16:05:35. 41624605 010020006F1F0000 D AAAfgoAAMAAAAC0AAv SYS I_WRI$_OPTSTAT_IND_OBJ#_ST 700 700
2025-05-13 16:05:35. 41624605 010020006F1F0000 D AAAfgoAAMAAAAC5AAy SYS APPLY$_CHANGE_HANDLERS 1000 1000
2025-05-13 16:05:35. 41624605 010020006F1F0000 D AAAfgoAAMAAAACyAAA SYS I_DIR$ESCALATE_UI 500 500
8 rows selected.
SCOTT@book01p> @ rowid AAAfgoAAMAAAACsAAg
DATA_OBJECT_ID FILE BLOCK ROW ROWID_DBA DBA TEXT
-------------- ---------- ---------- ---------- -------------------- -------------------- --------------------------------------------------
129064 12 172 32 0x30000AC 12,172 alter system dump datafile 12 block 172 ;
BBED> x /rcccnncttcccccncccccccccnnnn dba 12,172 *kdbr[32]
rowdata[3367] @4478
-------------
flag@4478: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@4479: 0x02
cols@4480: 0
--//脚本里面 sed -n '/^col /,/^BBED> /{s/^col .\{13\}: //;/^$/d;/^BBED> /s/^.*$//;p;}'，这样不会输出，自然过滤掉了。
4.行迁移测试：
SCOTT@book01p> select count(*) from t6 where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)=171;
COUNT(*)
----------
66
--//dba = 12,171有66条记录。
SCOTT@book01p> update t6 set owner=lpad('A',30,'A') where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)=171;
66 rows updated.
SCOTT@book01p> commit ;
Commit complete.
SCOTT@book01p> alter system checkpoint;
System altered.
$ . fffext.sh 12 171 171 129064 cccnncttcccccncccccccccnnnn|wc
58 525 10701
--//如果仅仅扫描dba=12，171,看到仅仅58条，有8条记录发生了行迁移，移动到另外的数据块了，在该块仅仅保留rowid部分信息。
$ . fffext.sh 12 168 $[ 184+8-1 ] 129064 cccnncttcccccncccccccccnnnn|wc
992 3767 139317
--//可以发现扫描数据块区域，还是可以得到完成的记录，也就是出现整体的行迁移使用没有问题。出现行链接估计就不行，这部分太难
--//解决，放弃。
--//顺便看看行迁移的情况：
SCOTT@book01p> alter system dump datafile 12 block 171;
System altered.
$ grep -B2 "^nrid" --no-group-separator /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3915.trc | grep ^tab | sort | uniq -c
2 tab 0, row 33, @0xe84
2 tab 0, row 38, @0xc63
2 tab 0, row 42, @0xaa2
2 tab 0, row 47, @0x896
2 tab 0, row 52, @0x65c
2 tab 0, row 56, @0x4e0
2 tab 0, row 60, @0x31e
2 tab 0, row 64, @0x185
--//转储输出了2次，修改整个数据块，大约间隔4，5条记录出现1次行迁移。
--//大致可以猜测整个过程从行号0开始update，到行号33时该块已经无法放下，发生行迁移，这样腾出部分空间，继续update，到行号
--//38该块再次满了，发生行迁移，这样腾出部分空间，如此循环，因为每个记录长度基本差不多，导致间隔4，5条记录出现1次行迁移。
BBED> x /rcccnncttcccccncccccccccnnnn dba 12,171 *kdbr[33]
rowdata[3457] @3840
-------------
flag@3840: 0x20 (KDRHFH)
lock@3841: 0x02
cols@3842: 0
nrid@3843:0x030000bd.0
--//与前面情况类似，过滤掉了。
5.附上更新的源代码：
--//注意^G,^F的输入.linux下ctrl+v ctrl+g,ctrl+v ctrl+f. windows下ctrl+q ctrl+g,ctrl+q ctrl+f.
--//也就是不能拷贝粘贴直接使用，必须修改替换里面的^G,^F。
alias rlbbed='cd /home/oracle/bbed;/bin/rlwrap -s 9999 -c -r -i /u01/app/oracle/product/21.0.0/dbhome_1/bin/bbed parfile=bbed.par cmdfile=cmd.par '
$ cat -v fffext.sh
#! /bin/bash
# argv1=file_number argv2=begin_block argvs3=end_block argv4=data_object_id argv5=bbed /x format
#set -x
file_number=$1
begin_block=$2
end_block=$3
data_object_id=$4
ff="/r"${5}
# create sed script.
a=$5
len=$( echo ${#a} )
seq $len | xargs -IQ expr substr $a Q 1 | grep -n '[nt]' | sed 's+:.$+s/ $//+' >| ff.sed
echo 's+^\*NULL\*$++g' >> ff.sed
# scan begin_block to end_block,define search scope.
/bin/rm scan1.txt 2>/dev/null
#seq -f "%-1.0f" $begin_block $end_block | xargs -IQ echo -e "host echo $file_number,Q \np /d dba $file_number,Q ktbbh.ktbbhsid.ktbbhod1" | \
#rlbbed | grep "^BBED" | egrep "$file_number,|ub4 ktbbhod1" | awk '{print $NF}' | paste -d" " - - | awk -v a=$data_object_id '$2==a {print $1}' >| scan1.txt
seq -f "%-1.0f" $begin_block $end_block | xargs -IQ echo -e "host echo -n $file_number,Q \np /d dba $file_number,Q ktbbh.ktbbhsid.ktbbhod1" | \
rlbbed | grep -B1 --no-group-separator "ub4 ktbbhod1" | awk '{print $NF}' | paste -d" " - - | awk -v a=$data_object_id '$2==a {print $1}' >| scan1.txt
# scan2 kdbr and display record.
cat scan1.txt |while read dba
do
#echo $dba
kdbr_size=$(echo p dba $dba kdbt[0].kdbtnrow | rlbbed| grep kdbtnrow | awk '{print $NF}')
#echo $kdbr_size
begin=0
end=$[ kdbr_size -1 ]
#echo $begin $end
#echo set dba $dba
IFSOLD=$IFS
echo p dba $dba offset 0 kdbr | rlbbed | grep "sb2 kdbr" | sed "1s/^BBED> //" | awk -F"[][ ]+" -v a=$kdbr_size '$NF > a {print $3}' | \
sed "s+^+x $ff dba $dba *kdbr[+;s+$+]+"| rlbbed | sed -n '/^col /,/^BBED> /{s/^col .\{13\}: /^G/;/^$/d;/^BBED> /s/^.*$/^F/;p;}'| tr -d "\n\r" | \
awk 'BEGIN{RS="^F"} {print $0}' | while read line
do
echo "$line" | sed "s/^G//" | tr "^G" "\n" | sed -f ff.sed| paste -sd"|"
done
done
--//测试看看，如果出现行链接是否可行。看了笔记以前测试过，再重复测试看看。
--//链接：[20210319]bbed读取数据块3.txt，另外以前的读取脚本有1个小问题，这次更正
--//grep -B1 --no-group-separator "ub4 ktbbhod1" ,这样匹配输出前一行一定是dba信息。
--//以前测试过，看看我修改的版本能否解决以前遇到的问题(注:以前版本删除记录后输出空行)以及行迁移问题。
1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.测试环境建立：
SCOTT@book01p> create table t6 as select * from all_objects where rownum<=1000;
Table created.
SCOTT@book01p> @ o2z t6
==============================
O_OWNER : SCOTT
O_OBJECT_NAME : T6
O_OBJECT_TYPE : TABLE
SEG_PART_NAME :
O_STATUS : VALID
OID : 129064
D_OID : 129064
CREATED : 2025-05-13 15:54:43
LAST_DDL_TIME : 2025-05-13 15:54:43
PL/SQL procedure successfully completed.
SCOTT@book01p> @ bbedcol12 scott t6
Display bbed examine(x) FORMAT scott.t6 : cccnncttcccccncccccccccnnnn
SCOTT@book01p> select rowid from t6 where rownum=1;
ROWID
------------------
AAAfgoAAMAAAACrAAA
SCOTT@book01p> @ rowid AAAfgoAAMAAAACrAAA
DATA_OBJECT_ID FILE BLOCK ROW ROWID_DBA DBA TEXT
-------------- ---------- ---------- ---------- -------------------- -------------------- --------------------------------------------------
129064 12 171 0 0x30000AB 12,171 alter system dump datafile 12 block 171 ;
SCOTT@book01p> column PARTITION_NAME noprint
SCOTT@book01p> select * from dba_extents where segment_name='T6';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
----- ------------ ------------ --------------- --------- ---------- ---------- ---------- ---------- ------------
SCOTT T6 TABLE USERS 0 12 168 65536 8 12
SCOTT T6 TABLE USERS 1 12 176 65536 8 12
SCOTT T6 TABLE USERS 2 12 184 65536 8 12
3.删除记录测试：
SCOTT@book01p> delete from t6 where mod(object_id,100)=0;
8 rows deleted.
SCOTT@book01p> commit ;
Commit complete.
SCOTT@book01p> alter system checkpoint;
System altered.
$ . fffext.sh 12 168 $[ 184+8-1 ] 129064 cccnncttcccccncccccccccnnnn|wc
992 3442 135704
--//可以发现新的版本已经解决这个问题。
SCOTT@book01p> @ versions t6 OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID s '' '' "versions_operation='D' "
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V ROWID OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- -------------------- ----------------- --------------- ---------------- - ------------------ ----- ------------------------------ ---------- --------------
2025-05-13 16:05:35. 41624605 010020006F1F0000 D AAAfgoAAMAAAACsAAg SYS SEQ$ 100 100
2025-05-13 16:05:35. 41624605 010020006F1F0000 D AAAfgoAAMAAAACvAAM SYS TRIGGERJAVAF$ 300 300
2025-05-13 16:05:35. 41624605 010020006F1F0000 D AAAfgoAAMAAAACwAAZ SYS I_SETTINGS1 400 400
2025-05-13 16:05:35. 41624605 010020006F1F0000 D AAAfgoAAMAAAAC7AAT SYS STREAMS$_COMPONENT_PROP_IND 1100 1100
2025-05-13 16:05:35. 41624605 010020006F1F0000 D AAAfgoAAMAAAACzAAd SYS RADM_TD$ 600 600
2025-05-13 16:05:35. 41624605 010020006F1F0000 D AAAfgoAAMAAAAC0AAv SYS I_WRI$_OPTSTAT_IND_OBJ#_ST 700 700
2025-05-13 16:05:35. 41624605 010020006F1F0000 D AAAfgoAAMAAAAC5AAy SYS APPLY$_CHANGE_HANDLERS 1000 1000
2025-05-13 16:05:35. 41624605 010020006F1F0000 D AAAfgoAAMAAAACyAAA SYS I_DIR$ESCALATE_UI 500 500
8 rows selected.
SCOTT@book01p> @ rowid AAAfgoAAMAAAACsAAg
DATA_OBJECT_ID FILE BLOCK ROW ROWID_DBA DBA TEXT
-------------- ---------- ---------- ---------- -------------------- -------------------- --------------------------------------------------
129064 12 172 32 0x30000AC 12,172 alter system dump datafile 12 block 172 ;
BBED> x /rcccnncttcccccncccccccccnnnn dba 12,172 *kdbr[32]
rowdata[3367] @4478
-------------
flag@4478: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@4479: 0x02
cols@4480: 0
--//脚本里面 sed -n '/^col /,/^BBED> /{s/^col .\{13\}: //;/^$/d;/^BBED> /s/^.*$//;p;}'，这样不会输出，自然过滤掉了。
4.行迁移测试：
SCOTT@book01p> select count(*) from t6 where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)=171;
COUNT(*)
----------
66
--//dba = 12,171有66条记录。
SCOTT@book01p> update t6 set owner=lpad('A',30,'A') where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)=171;
66 rows updated.
SCOTT@book01p> commit ;
Commit complete.
SCOTT@book01p> alter system checkpoint;
System altered.
$ . fffext.sh 12 171 171 129064 cccnncttcccccncccccccccnnnn|wc
58 525 10701
--//如果仅仅扫描dba=12，171,看到仅仅58条，有8条记录发生了行迁移，移动到另外的数据块了，在该块仅仅保留rowid部分信息。
$ . fffext.sh 12 168 $[ 184+8-1 ] 129064 cccnncttcccccncccccccccnnnn|wc
992 3767 139317
--//可以发现扫描数据块区域，还是可以得到完成的记录，也就是出现整体的行迁移使用没有问题。出现行链接估计就不行，这部分太难
--//解决，放弃。
--//顺便看看行迁移的情况：
SCOTT@book01p> alter system dump datafile 12 block 171;
System altered.
$ grep -B2 "^nrid" --no-group-separator /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3915.trc | grep ^tab | sort | uniq -c
2 tab 0, row 33, @0xe84
2 tab 0, row 38, @0xc63
2 tab 0, row 42, @0xaa2
2 tab 0, row 47, @0x896
2 tab 0, row 52, @0x65c
2 tab 0, row 56, @0x4e0
2 tab 0, row 60, @0x31e
2 tab 0, row 64, @0x185
--//转储输出了2次，修改整个数据块，大约间隔4，5条记录出现1次行迁移。
--//大致可以猜测整个过程从行号0开始update，到行号33时该块已经无法放下，发生行迁移，这样腾出部分空间，继续update，到行号
--//38该块再次满了，发生行迁移，这样腾出部分空间，如此循环，因为每个记录长度基本差不多，导致间隔4，5条记录出现1次行迁移。
BBED> x /rcccnncttcccccncccccccccnnnn dba 12,171 *kdbr[33]
rowdata[3457] @3840
-------------
flag@3840: 0x20 (KDRHFH)
lock@3841: 0x02
cols@3842: 0
nrid@3843:0x030000bd.0
--//与前面情况类似，过滤掉了。
5.附上更新的源代码：
--//注意^G,^F的输入.linux下ctrl+v ctrl+g,ctrl+v ctrl+f. windows下ctrl+q ctrl+g,ctrl+q ctrl+f.
--//也就是不能拷贝粘贴直接使用，必须修改替换里面的^G,^F。
alias rlbbed='cd /home/oracle/bbed;/bin/rlwrap -s 9999 -c -r -i /u01/app/oracle/product/21.0.0/dbhome_1/bin/bbed parfile=bbed.par cmdfile=cmd.par '
$ cat -v fffext.sh
#! /bin/bash
# argv1=file_number argv2=begin_block argvs3=end_block argv4=data_object_id argv5=bbed /x format
#set -x
file_number=$1
begin_block=$2
end_block=$3
data_object_id=$4
ff="/r"${5}
# create sed script.
a=$5
len=$( echo ${#a} )
seq $len | xargs -IQ expr substr $a Q 1 | grep -n '[nt]' | sed 's+:.$+s/ $//+' >| ff.sed
echo 's+^\*NULL\*$++g' >> ff.sed
# scan begin_block to end_block,define search scope.
/bin/rm scan1.txt 2>/dev/null
#seq -f "%-1.0f" $begin_block $end_block | xargs -IQ echo -e "host echo $file_number,Q \np /d dba $file_number,Q ktbbh.ktbbhsid.ktbbhod1" | \
#rlbbed | grep "^BBED" | egrep "$file_number,|ub4 ktbbhod1" | awk '{print $NF}' | paste -d" " - - | awk -v a=$data_object_id '$2==a {print $1}' >| scan1.txt
seq -f "%-1.0f" $begin_block $end_block | xargs -IQ echo -e "host echo -n $file_number,Q \np /d dba $file_number,Q ktbbh.ktbbhsid.ktbbhod1" | \
rlbbed | grep -B1 --no-group-separator "ub4 ktbbhod1" | awk '{print $NF}' | paste -d" " - - | awk -v a=$data_object_id '$2==a {print $1}' >| scan1.txt
# scan2 kdbr and display record.
cat scan1.txt |while read dba
do
#echo $dba
kdbr_size=$(echo p dba $dba kdbt[0].kdbtnrow | rlbbed| grep kdbtnrow | awk '{print $NF}')
#echo $kdbr_size
begin=0
end=$[ kdbr_size -1 ]
#echo $begin $end
#echo set dba $dba
IFSOLD=$IFS
echo p dba $dba offset 0 kdbr | rlbbed | grep "sb2 kdbr" | sed "1s/^BBED> //" | awk -F"[][ ]+" -v a=$kdbr_size '$NF > a {print $3}' | \
sed "s+^+x $ff dba $dba *kdbr[+;s+$+]+"| rlbbed | sed -n '/^col /,/^BBED> /{s/^col .\{13\}: /^G/;/^$/d;/^BBED> /s/^.*$/^F/;p;}'| tr -d "\n\r" | \
awk 'BEGIN{RS="^F"} {print $0}' | while read line
do
echo "$line" | sed "s/^G//" | tr "^G" "\n" | sed -f ff.sed| paste -sd"|"
done
done
浙公网安备 33010602011771号