ITPub博客

首页 > 数据库 > Oracle > ​[20210906]bbed读取数据块(bbed-wrap.sh).txt

​[20210906]bbed读取数据块(bbed-wrap.sh).txt

原创 Oracle 作者:lfree 时间:2021-09-07 08:57:10 0 删除 编辑

[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))
--//也没有解决字符串超长的问题.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2790709/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    3075
  • 访问量
    6801818