Assuming you use a ROWID short period of time after you SELECT it, that the table is a standard table with a bunch, and that the database administrator does not do something in the table (which is a fairly safe assumption if the application is connected to the network), ROWID will be stable. It would be preferable to use a primary key, but when the primary key is unavailable, a large number of tools and frameworks developed by Oracle will use the ROWID for short periods of time. It would be unsafe if you intended to use a ROWID for a long period of time after you SELECT it, for example, if you allow users to edit data locally, and then synchronize an arbitrary period of time later with the main database.
ROWID is simply the physical location of the string, so anything that causes a change in location will change the value of the ROWID .
- If you use indexed tables or partitioned tables, updates to the row may change where the row is physically located, which will change the
ROWID value. - If a row is removed from a heap-organized table, a subsequent
INSERT can insert data with completely different data that uses the same ROWID previously deleted row. - Various administrative tasks can lead to a change in the
ROWID . Exporting and importing the table will change, for example, the ROWID , but it will do something like the new-ish online shrink command. These administrative tasks are usually not performed while the application is running, and will almost certainly not be done during the day. But this can lead to problems if the application does not shut down, when the database administrator does such things, or if the application saves data.
As time goes on, new features for changing ROWID become more common for new features. For example, indexed tables and the online shrink option are relatively new features. In the future, there are likely to be more opportunities that will include the potential, at least for changing the ROWID .
Of course, if we are pedantic, it is also unsafe to rely on the primary key. It is possible that another session arrives and updates the primary key of the row after reading it, or that some other session deletes the row after it is selected and inserts a new row with the same data and a different primary key. In any case, this helps to gain some local knowledge about what database applications actually use. It would be extremely rare, for example, to allow primary key updates or to reuse primary keys so that you can generally determine that it is safe to use the primary key. Similarly, quite often it is concluded that, given how you use partitioning or determine how you specified an index in your index-organized table, updates will not actually change the ROWID . If you know that the table is partitioned by LOAD_DATE , for example, and that you never update LOAD_DATE , you will not actually experience changes in the ROWID due to the update. If you know that the table is organized by indexes, but you are not updating the column that is part of this index, the ROWID will not change to UPDATE .