在Oracle中rowid唯一标识每条记录所在的位置它作为一个伪列在查询中出现
select rowidid
from test_table
where rownum<= ;
ROWID ID
AAAVcbAAPAAAAALAAA
AAAVcbAAPAAAAALAAB
AAAVcbAAPAAAAALAAC
AAAVcbAAPAAAAALAAD
AAAVcbAAPAAAAALAAE
AAAVcbAAPAAAAALAAF
AAAVcbAAPAAAAALAAG
AAAVcbAAPAAAAALAAH
AAAVcbAAPAAAAALAAI
AAAVcbAAPAAAAALAAJ
rowid是由个字符组成分个部分分别是
个字符的对象编号个字符的文件号个字符的块编号个字符的行编号
每一个字符的取值范围以及对应的数值是
| A| | | a| | | | |
| B| | | b| | | | |
| C| | | c| | | | |
| D| | | d| | | | |
| E| | | e| | | | |
| F| | | f| | | | |
| G| | | g| | | | |
| H| | | h| | | | |
| I| | | i| | | | |
| J| | | j| | | | |
| K| | | k| | | +| |
| L| | | l| | | /| |
| M| | | m| | | | |
| N| | | n| | | | |
| O| | | o| | | | |
| P| | | p| | | | |
| Q| | | q| | | | |
| R| | | r| | | | |
| S| | | s| | | | |
| T| | | t| | | | |
| U| | | u| | | | |
| V| | | v| | | | |
| W| | | w| | | | |
| X| | | x| | | | |
| Y| | | y| | | | |
| Z| | | z| | | | |
可以看到rowid是一个进制的表示方式利用上述对应表即可计算出
对象编号AAAVcb =
文件号AAP =
块号AAAAAL =
行号AAA~AAJ = ~
进制的转换完全可以交给机器去做Oracle也是这么认为的于是提供了一个叫做dbms_rowid的包它包含了一系列的方法我们借助这个包就可完成上述的工作了
select rowid
substr(rowid) || : || dbms_rowidrowid_object(rowid) 数据对象编号/object_id
substr(rowid) || : || dbms_rowidrowid_relative_fno(rowid) 文件编号/file_id
substr(rowid)|| : || dbms_rowidrowid_block_number(rowid) 块编号/block_id
substr(rowid)|| : || dbms_rowidROWID_ROW_NUMBER(rowid) 行编号/row_num
from test_table
where rownum<=;
ROWID 数据对象编号/object_id 文件编号/file_id 块编号/block_id 行编号/row_num
AAAVcbAAPAAAAALAAA AAAVcb : AAP : AAAAAL : AAA :
AAAVcbAAPAAAAALAAB AAAVcb : AAP : AAAAAL : AAB :
AAAVcbAAPAAAAALAAC AAAVcb : AAP : AAAAAL : AAC :
AAAVcbAAPAAAAALAAD AAAVcb : AAP : AAAAAL : AAD :
AAAVcbAAPAAAAALAAE AAAVcb : AAP : AAAAAL : AAE :
AAAVcbAAPAAAAALAAF AAAVcb : AAP : AAAAAL : AAF :
AAAVcbAAPAAAAALAAG AAAVcb : AAP : AAAAAL : AAG :
AAAVcbAAPAAAAALAAH AAAVcb : AAP : AAAAAL : AAH :
AAAVcbAAPAAAAALAAI AAAVcb : AAP : AAAAAL : AAI :
AAAVcbAAPAAAAALAAJ AAAVcb : AAP : AAAAAL : AAJ :
这个结果对不对呢？我们可以这样验证注意以下查询需要DBA权限
首先是object_id
select
ownerobject_nameobject_id
from dba_objects
where object_name=TEST_TABLE;
OWNER OBJECT_NAME OBJECT_ID
TEST TEST_TABLE
然后是文件编号和块编号
select
ownersegment_namesegment_typeextent_id
file_idblock_idblocksbytes
from dba_extents
where segment_name=TEST_TABLE;
OWNER SEGMENT_NAME SEGMENT_TYPE EXTENT_ID FILE_ID BLOCK_ID BLOCKS BYTES
TEST TEST_TABLE TABLE
TEST TEST_TABLE TABLE
编号为的块落在了编号为的exntent上只能说是验证了一半接下来我们将数据块dump出来看看不过做之前先为这一行打上标记看以下过程
test$logdw@logdw SQL> select rowidt* from test_table t where rownum<=;
ROWID ID DATA
AAAVcbAAPAAAAALAAA Q
AAAVcbAAPAAAAALAAB Q
AAAVcbAAPAAAAALAAC Q
AAAVcbAAPAAAAALAAD Q
AAAVcbAAPAAAAALAAE Q
rows selected
test$logdw@logdw SQL> update test_table set data=lpad(killkill) where id=;
row updated
test$logdw@logdw SQL> select rowidt* from test_table t where rownum<=;
ROWID ID DATA
AAAVcbAAPAAAAALAAA killkill
AAAVcbAAPAAAAALAAB Q
AAAVcbAAPAAAAALAAC Q
AAAVcbAAPAAAAALAAD Q
AAAVcbAAPAAAAALAAE Q
rows selected
test$logdw@logdw SQL> commit;
做好了标记可以dump数据块了
sys$logdw@logdw SQL> select get_trace_name() from dual ;
GET_TRACE_NAME()
/u/app/oracle/diag/rdbms/logdw/logdw/trace/logdw_ora_trc
sys$logdw@logdw SQL> alter system dump datafile block ;
System altered
打开trc文件摘录如下
Start dump data blocks tsn: file#: minblk maxblk
Dump of memory from xAFA to xAFA
AFF CB [ kil]
AFF CBC CC EC [lkill ]
block_row_dump:
tab row @xac
tl: fb: HFL lb: x cc:
col : [ ] c
col : []
b c
c b c c
你找到killkill了吗？
