Wednesday, September 10, 2008


SELECT department_id, rowid FROM hr.departments;
————- ——————

In this example:
• AAABQM is the data object number
• AAF is the relative file number
• AAAAA6 is the block number
• AAA is the row number for the department with ID = 10

  • every row in a non clustered table of Oracle database is assigned a unique ROWID
  • corresponds to the physical address of the row
  • every table contains a pseudo column called ROWID
  • fastest way to access a row
  • Import, Export operations affect the values of ROWID
  • the ROWID of a deleted row may be reassigned by Oracle
  • cannot be modified
  • can be queried
  • may not be supported in non-Oracle databases
  • can be used to understand a table's organization
  • use of locks while using ROWID is recommended
  • ORACLE8 uses extended rowid format
  • Object Ids can refer to various row objects
  • ROWID changes especially with index organized or partitioned tables. Because ROWIDs represent the physical location of a record/row, the ROWID will chang every time the record is physically moved
  • The ROWID is intended to be immutable (that is, unchangeable)
  • The ROWID is a valid data type of a column and is available in both SQL and Pl/SQL. It stores the ROWID pseudo column value of a row in the database.
  • It's important to note that ROWID values are not necessarily unique within a database. It is entirely possible for two rows of two different tables stored in the same cluster to have the same ROWID
  • ROWID may change if the row is physically moved on disk, such as:
                    Doing an export or import of the table
                    Doing ALTER TABLE XXXX MOVE
                    Doing ALTER TABLE XXXX SHRINK SPACE
                    Doing FLASHBACK TABLE XXXX
                    When splitting a partition
                    When updating a value so that it moves to a new partition
                    When combining two partitions

No comments: