在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了嗎？
From:http://tw.wingwit.com/Article/program/Oracle/201311/17417.html
