Question on Oracle rowid column in every table

oracle

I want to know how to use the object number that extract from rowid column? It's seems that this oracle number is not the same as object_id in dba_objects view. Anyone can help me with it please? Thanks.

sys@ORCL>l
  1  SELECT rowid, DBMS_ROWID.Rowid_object(ROWID)   "OBJECT",
  2     DBMS_ROWID.Rowid_relative_fno(ROWID) "FILE",
  3     DBMS_ROWID.Rowid_block_number(ROWID) "BLOCK",
  4     DBMS_ROWID.Rowid_row_number(ROWID)   "ROW",
  5     employee_id, first_name
  6  FROM   hr.employees
  7* WHERE  department_id = 30
sys@ORCL>/

ROWID              OBJECT       FILE      BLOCK    ROW EMPLOYEE_ID FIRST_NAME
------------------ ---------- ---------- ---------- ---------- ----------- --------------------
AAAR5pAAFAAAADPAAO  73321          5    207     14         114 first_name
AAAR5pAAFAAAADPAAP  73321          5    207     15         115 first_name
AAAR5pAAFAAAADPAAQ  73321          5    207     16         116 first_name
AAAR5pAAFAAAADPAAR  73321          5    207     17         117 first_name
AAAR5pAAFAAAADPAAS  73321          5    207     18         118 first_name
AAAR5pAAFAAAADPAAT  73321          5    207     19         119 first_name

6 rows selected.

sys@ORCL>select * from dba_objects where object_id = 73321;

no rows selected

sys@ORCL>

Best Answer

I found the answer of my own question. The so called object number from DBMS_ROWID.Rowid_object corresponds to dba_objects.data_object_id, not object_id.

sys@ORCL>select owner, object_name, object_id, data_object_id from dba_objects where data_object_id = 73321;

OWNER    OBJECT_NAME OBJECT_ID  DATA_OBJECT_ID
-------- ----------- ---------- --------------
HR       EMPLOYEES   73953      73321

sys@ORCL>

Related Solutions

Objects in v$bh but not dba_objects

The majority of the unmatched objects will be Undo.

You can join v$bh with DBA_ROLLBACK_SEGS to identify the Undo segments involved.

SELECT segment_name, owner, tablespace_name, class#, count(class#)
FROM v$bh bh, dba_rollback_segs drs
WHERE NOT EXISTS (SELECT 1 FROM sys.obj$ o2 WHERE o2.dataobj# = bh.objd)
AND drs.segment_id = class#
GROUP BY segment_name, owner, tablespace_name, class#
HAVING count(class#) > 1;

ORA-01410: invalid ROWID

In your code, you are locking the following records in the c1 cursor result set for the column sal:

     EMPNO ENAME             SAL DNAME
---------- ---------- ---------- --------------
      7782 CLARK            9451 ACCOUNTING
      7839 KING            12001 ACCOUNTING
      7934 MILLER           8301 ACCOUNTING

For example, from another session this query

SELECT sal FROM emp FOR UPDATE;

will block until the next commit or rollback statement will be issued (check out v$session).

I guess the problem is that you are updating the sal column values of table emp1 (and not emp one), but these records are not locked. You plan on updating emp1 records that have not been referenced by the previous SELECT FOR UPDATE statement, so their ROWIDs become invalids. From here: The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.

In fact, if you change the code like this:

...
IF c2.dname = 'ACCOUNTING' THEN
    UPDATE emp1
    SET    sal = sal + 1000;
END IF;
...

or if you replace emp1 with emp:

...
IF c2.dname = 'ACCOUNTING' THEN
    UPDATE emp
    SET    sal = sal + 1000;
    WHERE  CURRENT OF c1;
END IF;
...

it would work.

Best Answer

Related Solutions

Objects in v$bh but not dba_objects

ORA-01410: invalid ROWID

Related Question