[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