dbms_rowid的使用

最新推荐文章于 2021-07-03 15:44:36 发布

citao9160

最新推荐文章于 2021-07-03 15:44:36 发布

阅读量169

点赞数

select rowid,
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) num
from test
where rownum <= 5;

SQL> desc dbms_rowid
FUNCTION ROWID_BLOCK_NUMBER RETURNS NUMBER // --返回该ROWID所在的BLOCK号
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
TS_TYPE_IN VARCHAR2 IN DEFAULT
FUNCTION ROWID_CREATE RETURNS ROWID    // --输入相应信息后自己创建一个ROWID并返回，主要用于测试比对
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROWID_TYPE NUMBER IN
OBJECT_NUMBER NUMBER IN
RELATIVE_FNO NUMBER IN
BLOCK_NUMBER NUMBER IN
ROW_NUMBER NUMBER IN
PROCEDURE ROWID_INFO       // --返回ROWID确定的各种信息
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROWID_IN ROWID IN
ROWID_TYPE NUMBER OUT
OBJECT_NUMBER NUMBER OUT
RELATIVE_FNO NUMBER OUT
BLOCK_NUMBER NUMBER OUT
ROW_NUMBER NUMBER OUT
TS_TYPE_IN VARCHAR2 IN DEFAULT
FUNCTION ROWID_OBJECT RETURNS NUMBER // --返回该ROWID对应的OBJECT的OBJ#
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_RELATIVE_FNO RETURNS NUMBER //--返回该ROWID对应的对应文件号
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
TS_TYPE_IN VARCHAR2 IN DEFAULT
FUNCTION ROWID_ROW_NUMBER RETURNS NUMBER //--返回该行数据在BLOCK中的相对位置
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_TO_ABSOLUTE_FNO RETURNS NUMBER // --返回相关的完全数据文件号
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
FUNCTION ROWID_TO_EXTENDED RETURNS ROWID // --将restricted类型的ROWID修改为extended
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OLD_ROWID ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
CONVERSION_TYPE NUMBER(38) IN
FUNCTION ROWID_TO_RESTRICTED RETURNS ROWID // --将extended类型的ROWID修改为restricted
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OLD_ROWID ROWID IN
CONVERSION_TYPE NUMBER(38) IN
FUNCTION ROWID_TYPE RETURNS NUMBER      // --返回ROWID类型(restricted or extended)
Argument Name Type In/Out Default?        // ro w id_type_restricted constant integer := 0;
------------------------------ ----------------------- ------ -------- // rowid_type_extended   constant integer := 1;
ROW_ID ROWID IN
FUNCTION ROWID_VERIFY RETURNS NUMBER    // --查看是否可以对ROWID的类型进行修改
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROWID_IN ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
CONVERSION_TYPE NUMBER(38) IN

SQL> select rowid,
2 dbms_rowid.rowid_object(rowid) object_id,
3 dbms_rowid.rowid_relative_fno(rowid) file_id,
4 dbms_rowid.rowid_block_number(rowid) block_id,
5 dbms_rowid.rowid_row_number(rowid) num
6 from emp where rownum <=4;

ROWID OBJECT_ID FILE_ID BLOCK_ID NUM
------------------ ---------- ---------- ---------- ----------
AAAO/9AAEAAACr0AAA 61437 4 10996 0
AAAO/9AAEAAACr0AAB 61437 4 10996 1
AAAO/9AAEAAACr0AAC 61437 4 10996 2
AAAO/9AAEAAACr0AAD 61437 4 10996 3

SQL>
SQL> select dbms_rowid.rowid_type('AAAO/9AAEAAACr0AAD') from dual;

DBMS_ROWID.ROWID_TYPE( 'AAAO/9AAEAAACR0AAD')
-------------------------------------------
1

SQL> select dbms_rowid.rowid_block_number(' AAAO/9AAEAAACr0AAD') from dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAO/9AAEAAACR0AAD')
---------------------------------------------------
10996

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

转载于:http://blog.itpub.net/29289867/viewspace-1979112/