I recently worked on a project where I have an interval-partitioned table that I insert into daily.  After the insert, I wanted to gather statistics on the partition that I’d just inserted to.  I thought back through some recent reading and remembered that the ROWID of an object contains the following elements:

  • Letters 1-6 = the OBJECT_ID
  • Letters 7-9 = the relative FILE_ID
  • Letters 10-15 = the BLOCK within the file
  • Letters 16-18 = the ROW within the block

I knew there was a package called DBMS_ROWID (although I had never used it) but I guessed there would be a procedure that I might could acquire an OBJECT_ID for a given ROWID.

And I was right.

I was happy with my new discovery, ran the ETL process a few times, and truncated the table to get rid of the “test” runs.

When I went to re-run my process, I got a surprising error.

Turns out, what happens is when you first create a table, Oracle creates an OBJECT_ID for the table, and then creates a DATA_OBJECT_ID for the underlying segment.  Usually what I’ve witnessed is the first partition of the table has a DATA_OBJECT_ID equal to that of the OBJECT_ID.   Well, if you truncate a table, you drop the underlying segment, so your DATA_OBJECT_ID changes.

The solution is to match on DATA_OBJECT_ID, not OBJECT_ID.