[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