1

Rowid is a unique identifier of a row of a table in database(oracle). I wanted to know if the rowid can repeat between tables in different schemas in the same database and different schemas in different databases ?

For example -

  1. Database (D1) has Schemas (S1 and S2). Can a table S1.Customer have same rowid as S2.Customer ?
  2. Databases(D1 and D2) have Schemas (S1 and S2) respectively. Can a table D1.S1.Customer have same rowid as D2.S2.Customer ?
FirstName
  • 377
  • 2
  • 6
  • 21

2 Answers2

2

There is no reason (and no promise from Oracle) why rowid's in different databases should be different.

Rows in the same database are generally different across all schemas and tables, but rows in different tables that are stored in the same cluster may have the same rowid. (This is straight from the documentation: https://docs.oracle.com/cd/B28359_01/server.111/b28286/pseudocolumns008.htm)

0

When the table is not clustered then yes, every row in an Oracle database has a unique ROWID associated with it. Note though that due to certain database operations (re-organization ofthe database) the ROWID a row may change, and also that ROWID might be re-used (delete and then insert in the same table might reuse the ROWID). ROWID stores unique information about a row, like the OBJID, the FILENO (relative to the tablespace the object is stored in), BLOCKNO (relative BLOCKNO within the datafile) and the ROWNUM, the relative rownumber within the block. For normal tables (not clustered or index organised) the ROWID is a unique and fast access path to the row.

Rob Heusdens
  • 155
  • 5