dbms_rowid.rowid_create函数,此函数可以创建一个rowid,它的语法如下

DBMS_ROWID.ROWID_CREATE (rowid_type IN NUMBER,object_number IN NUMBER,relative_fno IN NUMBER,block_number IN NUMBER,row_number IN NUMBER)RETURN ROWID;

rowid_type Type (restricted or extended).

Set the rowid_type parameter to 0 for a restricted ROWID. Setit to 1 to create an extended ROWID.If you specify rowid_type as 0, then the required object_number parameter is ignored, and ROWID_CREATE returns arestricted ROWID.

object_number Data object number (rowid_object_undefined for restricted).

relative_fno Relative file number.

block_number Block number in this file.

row_number Returns row number in this block.

下面是使用dbms_rowid.rowid_create的一个例子

sys@JINGYONG> select rowid, t.*,dbms_rowid.rowid_relative_fno(rowid) relative_fno ,  2  dbms_rowid.rowid_object(rowid) object_number ,  3  dbms_rowid.rowid_block_number(rowid) block_number ,  4  dbms_rowid.rowid_row_number(rowid) row_number  5   from t where rownum<2;ROWID                      ID TEXT                           RELATIVE_FNO OBJECT_NUMBER BLOCK_NUMBER ROW_NUMBER------------------ ---------- ------------------------------ ------------ ------------- ------------ ----------AAANB1AABAAAPAaAAA         20 ICOL$                                     1  53365        61466          0sys@JINGYONG>  select data_object_id from dba_objects where object_id=53365;DATA_OBJECT_ID--------------         53365sys@JINGYONG>  select dbms_rowid.rowid_create(1,53365,1,61466,0) create_rowid from dual;CREATE_ROWID------------------AAANB1AABAAAPAaAAA

通过dbms_rowid.rowid_create(1,53365,1,61466,0)构造的rowid与原始的rowid是一样的.

参数的意思：1表示rowid的类型为扩展rowid，类型为1； data_object_id 为53365，也就是文中t表对象的id； 数据文件id为1 ，即system表空间文件；块的编号为61466号； 行数为第一行（第一行为值0） 。

通过dbms_rowid.rowid_create函数查询被锁定的具体行

查询被锁的会话和持有锁的会话，如果是exclusive锁则xidusn非零的表示已经执行并持有锁

column o_name format a10column lock_type format a20column object_name format a15select rpad(oracle_username,10) o_name,       session_id sid,       decode(locked_mode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type,       object_name,       all_objects.object_id,       xidusn,       xidslot,       xidsqnfrom v$locked_object,all_objectswhere v$locked_object.object_id=all_objects.object_id;O_NAME SID     LOCK_TYPE      OBJECT_NAME   OBJECT_ID   XIDUSN    XIDSLOT   XIDSQN---------- ------- -------------------- ---------------        -------------- ----------   ------------ ----------SYS 145      Row Exclusive    T                    53365            8             17               724SYS   148     Row Exclusive    T                    53365           0              0                 0

下面的查询可以得到被锁定的session,被锁定的对象id和row number

select sid,       row_wait_obj# object_id,       row_wait_file# file_no,       row_wait_block# block,       row_wait_row# row_numfrom v$sessionwhere row_wait_obj#=&object_id;Enter value for object_id: 53365old   7: where row_wait_obj#=&object_idnew   7: where row_wait_obj#=53365       SID OBJECT_ID        FILE_NO          BLOCK        ROW_NUM---------- --------------   ----------       ----------   ----------       148   51207           1               61466            0

如果要对应行rowid则：

sys@JINGYONG>  select dbms_rowid.rowid_create(1,53365,1,61466,0) create_rowid from dual;CREATE_ROWID------------------AAANB1AABAAAPAaAAA

有了rowid，具体的行就能定位了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26015009/viewspace-773712/，如需转载，请注明出处，否则将追究法律责任。