​[20210906]bbed讀取資料塊(bbed-wrap.sh).txt

[20210906]bbed讀取資料塊(bbed-wrap.sh).txt

--//連結:
--//Oracle MOS上的一篇內部文件"AQUICK WAY TO READ RECORDS FROM A DATA BLOCK USING BBED TOOL(Note:371546.1)"
--//,我主要目的看看它如何實現的.感興趣的部分是它的輸出.
--//另外我發現原始指令碼copy and paste 錯誤,視乎少了一些/和^,我自己做了一些修改與調式:
--//原始版本如下.無法執行透過.
#/* ---- (bbed-wrap.sh) ---- */
#!/bin/ksh
# $Id: bbed-wrap.sh,v1.1 2006/05/24 09:44:03 oracle Exp oracle $
# $Author: mmalvezz $
FILE=$1
BLOCK=$2
ORADATATYPE=${3:-"/rn2cntn"}
BBED=$ORACLE_HOME/bin/bbed
PARFILE=$(pwd)/bbed.par
DBA="set  file   ${FILE}  block  ${BLOCK}"
export DBA ORADATATYPE
PORT=$(uname)
[ $PORT == "Linux" ] && AWK=awk
[ $PORT == "SunOS" ] && AWK=nawk
NUMROWS=$($BBED  parfile=bbed.par <<EOF|
${DBA}
p kdbh.kdbhnrow
EOF
grep kdbhnrow |${AWK} '{print $5}')
###~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[ $NUMROWS -eq 0 ] && exit
echo "There are $NUMROWS rows in block $BLOCK on file $FILE"
Idx=0
while [ $Idx -lt $NUMROWS ]
do
$BBED parfile=bbed.par <<EOF|
   ${DBA}
   x *kdbr[$Idx]
   x ${ORADATATYPE}
EOF
${AWK} -F: '
    #formtting the output
    BEGIN {
      flag=0;
      cnt=0;
    }
    { if($1  ~ cols/) numcol=$2; }
####不對,似乎少了/^.
    # read only rows that are not chained, see kd3.h for details
    {
      if(($1 ~ flag/) && ($2 ~ KDRHFL, KDRHFF, KDRHFH/ ))
         flag=1;
    }
    {
      if(($1 ~ col ) && (flag==1))
      {
         printf("\x22%s\x22", $2);
         if(++cnt < numcol)
           printf(",");
      }
    }
   END { printf("\n"); }
'
((Idx+=1))
done

--//我自己做了改寫.版本如下:
#/* ---- (bbed-wrap.sh) ---- */
#!/bin/bash
# $Id: bbed-wrap.sh,v1.1 2006/05/24 09:44:03 oracle Exp oracle $
# $Author: mmalvezz $
FILE=$1
BLOCK=$2
ORADATATYPE=${3:-"/rn2cntn"}
BBED=$ORACLE_HOME/bin/bbed
PARFILE=$(pwd)/bbed.par
DBA="set  file   ${FILE}  block  ${BLOCK}"
export DBA ORADATATYPE

PORT=$(uname)
[ $PORT == "Linux" ] && AWK=awk
[ $PORT == "SunOS" ] && AWK=nawk
AWK=awk

NUMROWS=$(echo "p /d dba $FILE,$BLOCK kdbh.kdbhnrow" | rlbbed | grep kdbhnrow |awk '{print $NF}')
[ $NUMROWS -eq 0 ] && exit

echo "There are $NUMROWS rows in block $BLOCK on file $FILE"

Idx=0
while [ $Idx -lt $NUMROWS ]
do
    echo -n "x $ORADATATYPE dba $FILE,$BLOCK *kdbr[$Idx]" | rlbbed | ${AWK} -F': ' '
    #formtting the output
    BEGIN {
      flag=0;
      cnt=0;
    }

    { if($1  ~ /^cols/) numcol=$2; }
    # read only rows that are not chained, see kd3.h for details
    {
      if(($1 ~ /^flag/) && ($2 ~ /(KDRHFL, KDRHFF, KDRHFH)/))
         flag=1;
    }
    {
      if(($1 ~ /^col/ ) && (flag==1))
      {
         #printf("\x22%s\x22", $2);
         printf("%s", $2);
         if(++cnt < numcol)
           printf(",");
      }
    }
   END { printf("\n"); }
'
    ((Idx+=1))
done

--//仔細讀了一下,該版本也沒有解決字串超長的問題.以及數字以及日期型別結尾空格問題.
--//另外我不大喜歡原作者EOF|這樣的寫法,注意看前面下劃線grep那行,非常容易出現歧義.
--//在測試環境測試看看.

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

SCOTT@book> select rowid,emp.* from emp where rownum=1;
ROWID                   EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAVREAAEAAAACXAAA       7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

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 ;

SCOTT@book> @ bbedcol scott emp
DISPLAY BBED EXAMINE(X) FORMAT
C80
--------
nccntnnn

2.測試:
$ . bbed-wrap.sh 4 151 /rnccntnnn
There are 14 rows in block 151 on file 4
7369 ,SMITH,CLERK,7902 ,1980-12-17 00:00:00 ,800 ,*NULL*,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 ,*NULL*,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 ,*NULL*,30
7782 ,CLARK,MANAGER,7839 ,1981-06-09 00:00:00 ,2450 ,*NULL*,10
7788 ,SCOTT,ANALYST,7566 ,1987-04-19 00:00:00 ,3000 ,*NULL*,20
7839 ,KING,PRESIDENT,*NULL*,1981-11-17 00:00:00 ,5000 ,*NULL*,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 ,*NULL*,20
7900 ,JAMES,CLERK,7698 ,1981-12-03 00:00:00 ,950 ,*NULL*,30
7902 ,FORD,ANALYST,7566 ,1981-12-03 00:00:00 ,3000 ,*NULL*,20
7934 ,MILLER,CLERK,7782 ,1982-01-23 00:00:00 ,1300 ,*NULL*,10

--//數字與日期後面的空格存在,*NULL*表示null,感覺沒有我寫的那個版本好,^_^.
--//注我的rlbbed定義是一個函式,無法使用./bbed-wrap.sh方式執行,只能使用. bbed-wrap.sh方式呼叫.
--//如果你想使用函式或者別名呼叫,必須寫在指令碼里面.
--//我記憶裡面我當時選擇定義函式主要原因是引數parfile,cmdfile的路徑問題,順便解答一些網友的問題.為什麼選擇函式定義.
--//實際上定義如下,使用alias也是一樣的問題.
$ type rlbbed
rlbbed is a function
rlbbed ()
{
    cd /home/oracle/bbed;
    $RLWRAP -s 9999 -c -r -i -f /usr/local/share/rlwrap/bbed $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=cmd.par
}

$ . bbed-wrap.sh 1  521 /rnnc | head
There are 24 rows in block 521 on file 1
-1 ,-1 ,8.0.0.0.0
0 ,0 ,CREATE ROLLBACK SEGMENT SYSTEM STORAGE (  INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 128))
20 ,20 ,CREATE TABLE ICOL$("OBJ#" NUMBER NOT NULL,"BO#" NUMBER NOT NULL,"COL#" NUMBER NOT NULL,"POS#" NUMBER NOT NULL,"SEGCOL#" NUMBER NOT NULL,"SEGCOLLENGTH" NUMBER NOT NULL,"OFFSET" NUMBER NOT NULL,"INTCOL#" NUMBER NOT NULL,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4"
42 ,42 ,CREATE INDEX I_ICOL1 ON ICOL$(OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 42 EXTENTS (FILE 1 BLOCK 384))
28 ,28 ,CREATE TABLE CON$("OWNER#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"CON#" NUMBER NOT NULL,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (  INITIAL 64K
51 ,51 ,CREATE UNIQUE INDEX I_CON1 ON CON$(OWNER#,NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 51 EXTENTS (FILE 1 BLOCK 456))
52 ,52 ,CREATE UNIQUE INDEX I_CON2 ON CON$(CON#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 52 EXTENTS (FILE 1 BLOCK 464))
15 ,15 ,CREATE TABLE UNDO$("US#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"USER#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"SCNBAS" NUMBER,"SCNWRP" NUMBER,"XACTSQN" NUMBER,"UNDOSQN" NUMBER,"INST#" NUMBER,"STATUS$" NUMBER NOT NULL,"TS#" NUMBER,"UGRP#" NUMB
34 ,34 ,CREATE UNIQUE INDEX I_UNDO1 ON UNDO$(US#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 34 EXTENTS (FILE 1 BLOCK 320))
--//也沒有解決字串超長的問題.