[20210323]bbed读取数据块5.txt
[20210323]bbed读取数据块5.txt
--//上个星期做了bbed读取数据块的测试，生成的文本存在一些小问题，要通过vim替换。
--//今天没事，完善这部分的处理。
1.环境：
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.准备：
SCOTT@book> select rowid from emp where rownum=1;
ROWID
------------------
AAAVREAAEAAAACXAAA
SCOTT@book> @ rowid AAAVREAAEAAAACXAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
87108 4 151 0 0x1000097 4,151 alter system dump datafile 4 block 151 ;
2.建立脚本，测试读取看看。
$ cat fff.sh
#! /bin/bash -x
# argv1=file_number argv2=begin_block argvs3=end_block argv4=data_object_id argv5=bbed /x format
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
# scan1 begin_block to end_block,define Scope.
/bin/rm scan1.txt 2>/dev/null
while [ $begin_block -le $end_block ]
do
v_object_id=$(echo "p /d dba $file_number,$begin_block ktbbh.ktbbhsid.ktbbhod1" | rlbbed | grep ktbbhod1 |awk '{print $NF}')
if [ -z "$v_object_id" ]
then
v_object_id=0
fi
if (( $v_object_id == $data_object_id ))
then
echo $file_number,$begin_block >> scan1.txt
fi
begin_block=$[ begin_block + 1 ]
done
# scan2 kdbr and display record.
cat scan1.txt |while read dba
do
#echo set dba $dba
kdbr_size=$(echo map dba $dba | rlbbed | grep "sb2 kdbr" | sed -e "s/^.*\[//;s/].*$//")
#echo $kdbr_size
begin=0
end=$[ kdbr_size -1 ]
#echo $begin $end
while [ $begin -le $end ]
do
kdbr_off=$(echo p dba $dba offset 0 kdbr | rlbbed | grep "sb2 kdbr\[$begin\]" | awk '{print $NF'})
#echo $kdbr_off
#if [ $kdbr_off -gt $kdbr_size ]
if (( $kdbr_off > $kdbr_size ))
then
echo -n "x $ff dba $dba *kdbr[$begin]" | rlbbed | grep "^col " | cut -c20- | sed -f ff.sed | paste -sd'|'
fi
begin=$[ begin + 1 ]
done
done
$ . fff.sh 4 151 151 87108 nccntnnn
7369|SMITH|CLERK|7902|1980-12-17 00:00:00|800||20
7499|ALLEN|SALESMAN|7698|1981-02-20 00:00:00|1600|300|30
7521|WARD|SALESMAN|7698|1981-02-22 00:00:00|1250|500|30
7566|JONES|MANAGER|7839|1981-04-02 00:00:00|2975||20
7654|MARTIN|SALESMAN|7698|1981-09-28 00:00:00|1250|1400|30
7698|BLAKE|MANAGER|7839|1981-05-01 00:00:00|2850||30
7782|CLARK|MANAGER|7839|1981-06-09 00:00:00|2450||10
7788|SCOTT|ANALYST|7566|1987-04-19 00:00:00|3000||20
7839|KING|PRESIDENT||1981-11-17 00:00:00|5000||10
7844|TURNER|SALESMAN|7698|1981-09-08 00:00:00|1500|0|30
7876|ADAMS|CLERK|7788|1987-05-23 00:00:00|1100||20
7900|JAMES|CLERK|7698|1981-12-03 00:00:00|950||30
7902|FORD|ANALYST|7566|1981-12-03 00:00:00|3000||20
7934|MILLER|CLERK|7782|1982-01-23 00:00:00|1300||10
--//这样前面生成文本文件的一些弊端都给解决了。
3.尝试sqlldr导入：
SCOTT@book> create table empx as select * from emp where 1=2;
Table created.
$ cat test.ctl
load data
CHARACTERSET ZHS16GBK
infile 'laji.txt'
append into table empx
fields terminated by '|'
TRAILING NULLCOLS
(
EMPNO ,
ENAME ,
JOB ,
MGR ,
HIREDATE DATE "YYYY-MM-DD HH24:MI:SS" ,
SAL ,
COMM ,
DEPTNO
)
$ sqlldr userid=scott/book control=test.ctl log=1.log bad=1.bad discard=1.discard
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Mar 23 08:50:04 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 14
SCOTT@book> select * from empx minus select * from emp;
no rows selected
SCOTT@book> select * from emp minus select * from empx;
no rows selected
4.总结：
--//不实用，仅仅当作自己学习bash shell的一次练习。
