All about rowid in Oracle Database 11g
程序员文章站
2022-05-17 13:43:30
...
SQL select t.rowid,t.* from scott.test t; ROWID DEPTNO DNAME LOC ------------------ ---------- -------------- ------------- AAAPtpAAEAAAADbAAA 10 ACCOUNTING NEW YORK AAAPtpAAEAAAADbAAB 20 RESEARCH DALLAS AAAPtpAAEAAAADbAAC 30 SALES CHICAGO
SQL> select t.rowid,t.* from scott.test t;ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAPtpAAEAAAADbAAA 10 ACCOUNTING NEW YORK
AAAPtpAAEAAAADbAAB 20 RESEARCH DALLAS
AAAPtpAAEAAAADbAAC 30 SALES CHICAGO
AAAPtpAAEAAAADbAAD 40 OPERATIONS BOSTON
rowid(AAAPtpAAEAAAADbAAD)详解
AAAPtp --object#
AAE --file#
AAAADb --block#
AAD --row#
SQL> select rowid ,
2 substr(rowid,1,6) "OBJECT#",
3 substr(rowid,7,3) "FILE#",
4 substr(rowid,10,6) "BLOCK#",
5 substr(rowid,16,3) "ROW#"
6 from scott.test;
ROWID OBJECT# FILE# BLOCK# ROW#
------------------ ------------------------ ------------ ------------------------ ------------
AAAPtpAAEAAAADbAAA AAAPtp AAE AAAADb AAA
AAAPtpAAEAAAADbAAB AAAPtp AAE AAAADb AAB
AAAPtpAAEAAAADbAAC AAAPtp AAE AAAADb AAC
AAAPtpAAEAAAADbAAD AAAPtp AAE AAAADb AAD
转换 rowid 的 object#
SQL> select DBMS_ROWID.ROWID_OBJECT('AAAPtpAAEAAAADbAAD') from dual
2 union all
3 select object_id from dba_objects where object_name = 'TEST' AND owner='SCOTT';
DBMS_ROWID.ROWID_OBJECT('AAAPTPAAEAAAADBAAD')
---------------------------------------------
64361
64361
转换 rowid 的 file# (相对文件编号)
SQL> select DBMS_ROWID.ROWID_RELATIVE_FNO('AAAPtpAAEAAAADbAAD') from dual
2 union all
3 select a.file_id from dba_data_files a,dba_segments b where b.SEGMENT_NAME ='TEST' and b.owner='SCOTT' and a.tablespace_name=b.tablespace_name;
DBMS_ROWID.ROWID_RELATIVE_FNO('AAAPTPAAEAAAADBAAD')
---------------------------------------------------
4
4
转换 rowid 的 block#
SQL> select dbms_rowid.rowid_block_number('AAAPtpAAEAAAADbAAD') from dual;
DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAPTPAAEAAAADBAAD')
---------------------------------------------------
219
转换 rowid 的 row#
SQL> select DBMS_ROWID.ROWID_ROW_NUMBER(rowid) from scott.test;
DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)
----------------------------------
0
1
2
3
转换 rowid 的绝对文件编号
SQL> select DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO('AAAPtpAAEAAAADbAAD','SCOTT','TEST') from scott.test;
DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO('AAAPTPAAEAAAADBAAD','SCOTT','TEST')
---------------------------------------------------------------------
4
4
4
4
作者:xiangsir
QQ:444367417
MSN:xiangsir@hotmail.com
上一篇: php7和nginx1.9安装
推荐阅读
-
Window下Oracle Database 11g 发行版2安装教程
-
Oracle Database 11g在Win8 上的卸载过程记录
-
Oracle 11g 使用duplicate from active database 创建物理ADG的操作详细教程
-
Oracle Database 11g MySQL 5.6开发手册》试读有感
-
配置Oracle 11g的Dataguard测试,创建物理备库(Physical Standby Database)
-
All about control file in Oracle Database
-
Oracle Database 11g回滚段命名的变化
-
Oracle Database 11g之数据仓库和商业智能
-
Window下Oracle Database 11g 发行版2安装教程
-
分享Tom Kyte的ALL ABOUT BINDS 关注ORACLE绑定变量技术