Oracle DBMS_ROWID 与 ORA-01446 说明

一网友说参考我的Blog，执行SQL 报错：

http://blog.csdn.net/tianlesoftware/article/details/6529346

SELECTROWID,

DBMS_ROWID.rowid_relative_fno (ROWID)rel_fno,

DBMS_ROWID.rowid_block_number (ROWID)blockno,

DBMS_ROWID.rowid_row_number (ROWID)rowno

FROM user_objects

WHEREobject_id =100;

selectrowid,dbms_rowid.rowid_relative_fno(rowid)rel_fno,dbms_rowid.rowid_block_number(rowid)blockno,dbms_rowid.rowid_row_number(rowid)rowno fromuser_objects where object_id=100

ERRORatline 1:

ORA-01446:cannot selectROWIDfrom,orsample,aviewwithDISTINCT,GROUPBY,etc.

[oracle@dave ~]$ oerr ora 1446

01446, 00000, "cannot select ROWID from, orsample, a view with DISTINCT, GROUP BY, etc."

// *Cause:

// *Action:

[oracle@dave ~]$

开始我扫了一眼，以为和版本或者bug有关系，因为这个SQL我也执行过很多，一直都没有遇到过问题。

在MOS上搜了一下，还真找到2个相关的Bug：

（1）10.2.0.5之后的版本，可能遇到这个bug：

ORA-1445 or ORA-1446 Referencing ROWID from a Viewwith Subquery (文档 ID 1364607.1)

（2）Oracle12.0.0的，可能遇到这个bug：

Select from AR_LOCATION_VALUES_DFV ErrorsORA-01446: Cannot Select Rowid From, Or Sample, A View With Distinct (文档 ID 980435.1)

但这里明显和我们的情况不符不符合。又仔细看了一眼，知道原因在哪了：

下面是我Blog里的原SQL:

SQL> SELECT

2 dbms_rowid.rowid_relative_fno(rowid)REL_FNO,

3 dbms_rowid.rowid_block_number(rowid)BLOCKNO,

4 empno, ename

5 FROM emp WHERE empno =7369;

REL_FNO BLOCKNO EMPNOENAME

---------- ---------- ---------- ----------

4 20 7369SMITH

网页的SQL:

SELECTROWID,

DBMS_ROWID.rowid_relative_fno (ROWID)rel_fno,

DBMS_ROWID.rowid_block_number (ROWID)blockno,

DBMS_ROWID.rowid_row_number (ROWID)rowno

FROMuser_objects

WHEREobject_id =100;

他查询的是user_objects 视图，而非物理表。

所以我们这里的错误：

ORA-01446:cannot selectROWIDfrom,orsample,aviewwithDISTINCT,GROUPBY,etc.

就是因为USER_OBJECTS 视图里的代码导致的。

采用实际的物理表，就不会出现这种问题：

SQL> create table dave as select * from dba_objects;

Table created.

SQL> SELECT

2 dbms_rowid.rowid_relative_fno(rowid)REL_FNO,

3 dbms_rowid.rowid_block_number(rowid)BLOCKNO,

4 dbms_rowid.rowid_row_number(rowid) ROWNO,

5 OBJECT_NAME

6 FROM dave WHERE OBJECT_ID = 20;

REL_FNO BLOCKNO ROWNO OBJECT_NAME

---------- --------- -------- --------------------------------------

1 92737 0 ICOL$

--------------------------------------------------------------------------------------------

QQ: 251097186

Skype: tianlesoftware

Email: tianlesoftware@gmail.com

Blog: http://blog.csdn.net/tianlesoftware

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware