dbms_rowid

彦祖的小号

于 2021-07-15 09:35:19 发布

阅读量288

点赞数

分类专栏： Oracle 文章标签：算法

本文链接：https://blog.youkuaiyun.com/linsuhangoracle/article/details/118752177

版权

Oracle 专栏收录该内容

193 篇文章

订阅专栏

本文详细介绍了在Oracle数据库中通过SQL和PL/SQL两种方式获取ROWID的各个部分：DATA_OBJECT_ID、RELATIVE_FNO、BLOCK_NO和ROW_NO。通过实例展示了如何使用DBMS_ROWID函数获取和解读ROWID类型信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

方式一：
SELECT ROWID,
dbms_rowid.rowid_object (ROWID) data_object_id,
dbms_rowid.rowid_relative_fno (ROWID) relative_fno,
dbms_rowid.rowid_block_number (ROWID) block_no,
dbms_rowid.rowid_row_number (ROWID) row_no
FROM T1;
ROWID DATA_OBJECT_ID RELATIVE_FNO BLOCK_NO ROW_NO
------------------ -------------- ------------ ---------- ----------
AAASQdAABAAAY/5AAA 74781 1 102393 0

方式二：
set serveroutput on
declare
row_id rowid;
rowidtype1 number;
rowidtype2 varchar2(10);
data_object_id number;
relative_fno number;
block_no number;
row_no number;
begin
select rowid into row_id from t1 where id=111;
DBMS_ROWID.ROWID_INFO ( rowid_in =>row_id, rowid_type=> rowidtype1, object_number=>data_object_id, relative_fno=>relative_fno,
block_number=>block_no, row_number =>row_no);
SELECT decode(rowidtype1,0,'RESTRICED',1, 'EXTENDED') INTO rowidtype2 FROM DUAL;
dbms_output.put_line('ROWID IS '|| row_id|| ', TYPE IS '|| rowidtype2);
dbms_output.put_line('DATA_OBJECT_ID IS '||data_object_id||', RELATIVE_FNO IS '|| relative_fno||', BLOCK_NUMBER IS '|| block_no||', ROW_NUMBER IS '|| row_no);
end;
/