[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的一次練習。