Re: Deadlock & DBMS_ROWID weirdness.

On Thu, Jan 3, 2019 at 12:12 PM Norman Dunbar <oracle_at_dunbar-it.co.uk> wrote:
>
> Happy New Year everyone.
Happy new year!

> The table might have been "defragmented" at some point using
>
> ALTER TABLE ... MOVE;
Great background information!

> Problem:
>
> Then OBJECT_ID, 1008338, given above is the correct one for the table in
> the DELETE statement that is deadlocking. However,
> DBMS_ROWID.ROWID_INFO, or, DBMS_ROWID.ROWID_OBJECT, give a completely
> different object number given the ROWIDs above - AAEGjbAQAAApJ2CAAY and
> AAEGjbAQAAApJ3rAAD, or indeed, for every rowid in the table.
>
> Querying the database, I get the following:
>
>
> -- Retrieve the table's OBJECT_ID:
>
> OBJECT_ID
> ---------
> 1008338
>

What is the DATA_OBJECT_ID for the table?

> -- Retrieve the OBJECT_ID from the ROWIDs:
> select dbms_rowid.rowid_object(chartorowid('AAEGjbAQAAApJ3rAAD')) as
> OBJECT_ID from dual;
>
> OBJECT_ID
> ---------
> 1075419

Since version 8? rowid's mostly use the DATA_OBJECT_ID (physical location/id, segment) from dba_objects table instead of object_id (logical id). Since several operations eg rebuild, move, partitioning, maybe truncate can leave the logical object in place but can change the physical location the definitions don't match 1:1.

> select rowid as ri
> from TABLE_NAME_IN_QUESTION
> where rowid in ('AAEGjbAQAAApJ2CAAY', 'AAEGjbAQAAApJ3rAAD');
>
> RI
> ------------------
> AAEGjbAACAAJJ2CAAY
> AAEGjbAACAAJJ3rAAD
> ^^ ^
Probably somewhere there's a good explanation why this works. I assume this was left in for backwards compatibility for installations which use rowid's or store them in other tables. For  those to still keep working they had to leave the possibility for using object_id and doing the lookup to data_object_id behind the scenes.

Also seems the deadlock trace does the reverse lookup for you

Andre

--
http://www.freelists.org/webpage/oracle-l