ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 通过dbms_rowid.rowid_create来生成rowid

通过dbms_rowid.rowid_create来生成rowid

原创 Linux操作系统 作者:eric0435 时间:2013-10-04 13:45:58 0 删除 编辑

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. Set
it 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 a
restricted 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          0


sys@JINGYONG>  select data_object_id from dba_objects where object_id=53365;

DATA_OBJECT_ID
--------------
         53365

sys@JINGYONG>  select dbms_rowid.rowid_create(1,53365,1,61466,0) create_rowid fr
om 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 a10
column lock_type format a20
column object_name format a15
select 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,
       xidsqn
from v$locked_object,all_objects
where 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               724
SYS   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_num
from v$session
where row_wait_obj#=&object_id;
Enter value for object_id: 53365
old   7: where row_wait_obj#=&object_id
new   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 fr
om dual;

CREATE_ROWID
------------------
AAANB1AABAAAPAaAAA

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

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

请登录后发表评论 登录
全部评论
系统架构师 Oracle ACE

注册时间:2011-10-12

  • 博文量
    560
  • 访问量
    6232336