[20191011]拆分rowid 2.txt

[20191011]拆分rowid 2.txt --//有了链接http://blog.itpub.net/267265/viewspace-2659612/=>[20191011]bash任意进制编码表.txt --//转化拆分rowid在bash变得非常容易,自己写一个脚本看看. --//Rowid 格式为：OOOOOOFFFBBBBBBRRR, data_object_id占6个字符,file占3个字符，block占6个字符,row占3个字符。当然如果存在在 --//存储中占用10个字节(32bit data_object_id +10 bit rfile# +22bit block + row 16bit)。 --//其中，O是对象ID，F是文件ID，B是块ID，R是行ID。 --//当然在普通索引中仅仅占6字节(注没有32bit data_object_id少4个字节，因为全部data_object_id都是一样的)。 --//分区表的全局索引中占10字节。 1.简单说明: Rowid采用64位进制编码，编码如下： A-Z <==> 0 - 25 (26) a-z <==> 26 - 51 (26) 0-9 <==> 52 - 61 (10) +/  <==> 62 - 63 (2) --//一般通过调用DBMS_ROWID很容易获得相关信息，我经常使用的脚本rowid.sql如下： set verify off column dba format a20 column text format a40 SELECT DBMS_ROWID.ROWID_OBJECT ('&1') "OBJECT",        DBMS_ROWID.ROWID_RELATIVE_FNO ('&1') "FILE",        DBMS_ROWID.ROWID_BLOCK_NUMBER ('&1') "BLOCK",        DBMS_ROWID.ROWID_ROW_NUMBER ('&1') "ROW",        lpad('0x'||trim(to_char(dbms_utility.MAKE_DATA_BLOCK_ADDRESS(dbms_rowid.ROWID_RELATIVE_FNO('&1'),dbms_rowid.ROWID_BLOCK_NUMBER('&1')), 'XXXXXXXX')), 10) rowid_dba,           DBMS_ROWID.ROWID_RELATIVE_FNO ('&1')        || ','        || DBMS_ROWID.ROWID_BLOCK_NUMBER ('&1')           "DBA",           'alter system dump datafile '        || DBMS_ROWID.ROWID_RELATIVE_FNO ('&1')        || ' block '        || DBMS_ROWID.ROWID_BLOCK_NUMBER ('&1')        || ' ;'           text   FROM DUAL; 2.测试: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 SCOTT@test01p> select rowid ,t1.* from t1 where id in (63,64); ROWID                      ID T1NAME ------------------ ---------- ---------------------- AAAG2DAALAAAADDAA+         63 t10000000063 AAAG2DAALAAAADDAA/         64 t10000000064 SCOTT@test01p> @ rowid AAAG2DAALAAAADDAA+     OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------      28035         11        195         62  0x2C000C3           11,195               alter system dump datafile 11 block 195 $ cat rowid.txt AAAG2DAALAAAADDAA+ AAAG2DAALAAAADDAA/ AAAG2DAALAAAADDAA1 $ cat rowid.txt | xargs -I {} ./rowidx.sh {} rowid=AAAG2DAALAAAADDAA+; data_object_id = 28035; file = 11; block = 195; row = 62 rowid=AAAG2DAALAAAADDAA/; data_object_id = 28035; file = 11; block = 195; row = 63 rowid=AAAG2DAALAAAADDAA1; data_object_id = 28035; file = 11; block = 195; row = 53 3.rowidx.sh脚本如下: $ cat rowidx.sh #! /bin/bash # split rowid to object#,file#,block#,row# odebug=${ODEBUG:-0} v_rowid="$*" if [ ${#v_rowid} -ne 18 ]; then     echo "$v_rowid is illegal! length <>18"     exit 2 fi if [ $odebug -eq 1 ] ; then         echo rowid="$v_rowid" fi out=(data_object_id file block row) a=0 echo -n rowid="$v_rowid" for i in ${v_rowid:0:6} ${v_rowid:6:3} ${v_rowid:9:6} ${v_rowid:15:3} do         #echo $i $a         echo -n ";" ${out[$a]} "=" $(( 64#$( echo $i | tr $( echo {A..Z} {a..z} {0..9} +/ | tr -d " ")  $( echo {0..9} {a..z} {A..Z} @ _| tr -d " ")) ))         (( a+=1)) done echo