Generate random ROWID

Question

I'm trying to create a random ROWID for testing purposes. I know it's not the way to go, but since I have no data (yet) I just need to guarantee when inserting the "value" of the ROWID (using ROWIDTOCHAR function) I have a different string everytime.

I've been reading DBMS_ROWID from Oracle documentation and I've found a way to generate a ROWID for testing purposes using the ROWID_CREATE function.

DBMS_ROWID.ROWID_CREATE (
   rowid_type    IN NUMBER, 
   object_number IN NUMBER,
   relative_fno  IN NUMBER,
   block_number  IN NUMBER,
   row_number    IN NUMBER) 
  RETURN ROWID;

My question is: Is there a way to instantiate a random ROWID using this funcion? (Like a random function to generate the IN parameters)

Accordingly to what I've read in the DBMS_RANDOM page

• DBMS_RANDOM.RANDOM produces integers in [-2^^31, 2^^31).
• DBMS_RANDOM.VALUE produces numbers in [0,1) with 38 digits of precision.

And I'm not sure if the generated values are always valid to be IN parameters and generate a ROWID. I just need a valid ROWID so I can use the ROWIDTOCHAR function

Answer 1

Rather than generate your own random value, which may or may not point to an actual object (but probably not), you could get the real ROWID of a random row from a real table. If you don't have your own data you could use any table you can see; look in all_tables for the highest num_rows value.

As an example you could use SYS.OBJ$, if you have priviliges to query that directly:

select rowidtochar(min(rowid)) from SYS.OBJ$ sample(0.001);

Adjust the sample size so you always get some data, but as little as possible; you'll still get multiple rows back, so the min() picks one. It's not guaranteed to get a different value every time of course, but neither is dbms_random, and with a big enough table it should be good enough.

---

If you don't have access to a suitably large table, you can generate random values as you described:

select rowidtochar(dbms_rowid.rowid_create(
   rowid_type => 1, 
   object_number => ceil(dbms_random.value(0, 1023)),
   relative_fno => ceil(dbms_random.value(0, 1023)),
   block_number => ceil(dbms_random.value(0, 1023)),
   row_number => ceil(dbms_random.value(0, 1023))))
from dual;

For each of the integer values this is using the overloaded dbms_random.value function to get a value in a range, and then using ceil to make it an integer. (You could also trunc, or round, or cast; zero may not be a valid argument though, so you may need the value range to start from one instead with those).

As David Aldridge said there will be limits to some of those arguments (which you may be able to infer from the documentation but they aren't validated beyond that; and running repeatedly gives different values:

ROWIDTOCHAR(DBMS_R
------------------
AAAAODADcAAAAOfABa
AAAAHTADOAAAANaAEb
AAAAI+ANtAAAAMuAKl
AAAAOdAI2AAAAMiAJ4
AAAADNAFAAAAAJRAIn

Answer 2

I think that the function is pretty dumb, in that it is not going to check that a particular object number exists as part of generating the the ROWID. The same might not be true for the rowid_type though.

There is going to be an upper bound on many of the numbers of course, but you could infer those from the ROW ID documentation which explains the format.

Answer 3

I think this is more for DBAs. For example when you face problems with datafile corruptions. Tools like dbv or statement alter database dump block or various database crash dumps contain database block address and the other numbers to be passed to this function. So this can be used to spot particular data in the database.

This function does not check whether the resulting number really addressees a valid row within particular data segment.

If you want to see some random table data use sample clause:

select * from t sample(5);