CODEWITHSUNDEEP
oracle-databaseoracle11g
pOrinG
pOrinG

Reputation: 935

Trying to figure out max length of Rowid in Oracle

As per my design I want to fetch rowid as in

select rowid r from table_name;

into a C variable. I was wondering what is the max size / length in characters of the rowid.

Currently in one of the biggest tables in my DB we have the max length as 18 and its 18 throughout the table for rowid.

Thanks in advance.

Edit: Currently the below block of code is iterated and used for multiple tables hence in-order to make the code flexible without introducing the need of defining every table's PK in the query we use ROWID.

select rowid from table_name ... where ....;

delete from table_name where rowid = selectedrowid;

I think as the rowid is picked and used then and there without storing it for future, it is safe to use in this particular scenario.

Please refer to below answer: Is it safe to use ROWID to locate a Row/Record in Oracle?

I'd say no. This could be safe if for instance the application stores ROWID temporarily(say generating a list of select-able items, each identified with ROWID, but the list is routinely regenerated and not stored). But if ROWID is used in any persistent way it's not safe.

Upvotes: 1

Views: 4697

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

According documentation ROWID has a length of 10 Byte:

Rowids of Row Pieces

A rowid is effectively a 10-byte physical address of a row.

Every row in a heap-organized table has a rowid unique to this table that corresponds to the physical address of a row piece. For table clusters, rows in different tables that are in the same data block can have the same rowid.

Oracle also documents the (current) format see, Rowid Format

In general you could use the ROWID in your application, provided the affected rows are locked!

Thus your statement may look like this:

CURSOR ... IS
select rowid from table_name ... where .... FOR UPDATE;

delete from table_name where rowid = selectedrowid;

see SELECT FOR UPDATE and FOR UPDATE Cursors

Oracle even provides a shortcut. Instead of where rowid = selectedrowid you can use WHERE CURRENT OF ...

Upvotes: 1

wolφi
wolφi

Reputation: 8361

A physical ROWID has a fixed size in a given Oracle version, it does not depend on the number of rows in a table. It consists of the number of the datafile, the number of the block within this file, and the number of the row within this block. Therefore it is unique in the whole database and allows direct access to the block and row without any further lookup.

As things in the IT world continue to grow, it is safe to assume that the format will change in future.

Besides volume there are also structural changes, like the advent of transportable tablespaces, which made it necessary to store the object number (= internal number of the table/partition/subpartion) inside the ROWID.

Or the advent of Index organized tables (mentioned by @ibre5041), which look like a table, but are in reality just an index without such a physical address (because things are moving constantly in an index). This made it necessary to introduce UROWIDs which can store physical and index-based ROWIDs.

Please be aware that a ROWID can change, for instance if the row moves from one table partition to another one, or if the table is defragmented to fill the holes left by many DELETEs.

Upvotes: 2

Related Questions