Bake your own cake
Pre-cooked example databases are cool for a lot of things. The first being that everyone can have access to them, so they can follow along with your demos without building tables or inserting a bunch of data. If you mess something up, it’s easy to restore a copy. The main problem is that they were usually designed by someone who didn’t have your issues.
Most of the time it only takes a table or two to prove your point, you just need to cook up some data that doesn’t have anyone’s real information in there. With Oracle, you have a couple different options.
SQL Server-ish
Baked into every Oracle table I’ve queried thus far, are two intrinsic columns: ROWID and ROWNUM. The ROWNUM column, at least, gives us the ability to skip over generating sequential numbers with the ROW_NUMBER function. The ROWID column is a confusing string of nonsense.

AAAY LMAO
To die for
Have you ever wished you could create a table… Let’s say a demo table! And it since it’s of no consequence, SQL just wouldn’t log any changes to it? Or even that it ever existed? I mean, we have minimal logging, but what if we wanted no logging at all? Minimal logging doesn’t always work, and requires a few pre-requisites, and, well, you get the idea. If it’s high tide on St. Patrick’s Day and Jesus is eating Cobb Salad with a T-Rex in a rowboat, your inserts will be minimally logged.
Oracle can do that, with the magic of NO LOGGING!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE HR.T1 NOLOGGING --YOU CAN'T LOG ME! AS SELECT ROWNUM AS "ID", TRUNC(MOD(ABS(ORA_HASH(SYS_GUID() )), 10000) + 1) AS "ORDER_ID", TRUNC(MOD(ABS(ORA_HASH(SYS_GUID() )), 100) + 1) AS "SALES_REP_ID", TRUNC(MOD(ABS(ORA_HASH(SYS_GUID() )), 1000000) + 1) AS "CUSTOMER_ID", SYSTIMESTAMP - ROWNUM AS "ORDER_DATE", SYSTIMESTAMP - ROWNUM + 1 AS "PROCESS_DATE", SYSTIMESTAMP - ROWNUM + 3 AS "SHIP_DATE", SUBSTR(SYS_GUID(), 0, 7) AS "CUST_FIRST_NAME", SUBSTR(SYS_GUID(), 0, 10) AS "CUST_LAST_NAME", TRUNC(MOD(ABS(ORA_HASH(SYS_GUID() )), 900000000) + 100000000) AS "CUST_PHONE", CASE WHEN TRUNC(MOD(ABS(ORA_HASH(SYS_GUID() )), 3)) = 0 THEN 1 ELSE 0 END AS "IS_SOMETHING" FROM ALL_OBJECTS WHERE ROWNUM <= 10000; |
I tried to make the rest of the code as close to the usual demo table SELECT INTO stuff I normally do.
- ID is an incrementing integer
- ORDER_ID is a random number between 1 and 10,000
- SALES_REP_ID is a random number between 1 and 100
- CUSTOMER_ID is a random number between 1 and 1 million
- The three date columns use the ROWNUM to subtract a span of days, and then a static number of days are added to put a little distance between each activity
- The two name columns are based on substrings of GUIDs
- CUST_PHONE is a random 9 digit number
- IS_SOMETHING is a random 1 or 0 bit column
Easy enough! And quick. 10,000 rows get inserted on my woeful VM in 0.297 ms. That’s about as long as it just took you to blink.
Of course, there are some built in Oracle goodies to generate data a little differently, but they’re (in my mind, anyway) a bit more complicated. They rely on the DMBS_RANDOM functions. There’s a lot you can do with them! The documentation is right over this way. In particular, the STRING subprogram can give you all sorts of nice junk data.
Here’s a quick example using DBMS_RANDOM.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE HR.T2 NOLOGGING --YOU CAN'T LOG ME! AS SELECT ROWNUM AS "ID", ABS(TRUNC(DBMS_RANDOM.VALUE(1,10000))) AS "ORDER_ID", ABS(TRUNC(DBMS_RANDOM.VALUE(1,100))) AS "SALES_REP_ID", ABS(TRUNC(DBMS_RANDOM.VALUE(1,1000000))) AS "CUSTOMER_ID", TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_CHAR(SYSDATE, 'J'), TO_CHAR(SYSDATE , 'J'))), 'J') AS "ORDER_DATE", TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_CHAR(SYSDATE, 'J'), TO_CHAR(SYSDATE, 'J'))), 'J') + 1 AS "PROCESS_DATE", TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_CHAR(SYSDATE, 'J'), TO_CHAR(SYSDATE, 'J'))), 'J') + 3 AS "SHIP_DATE", DBMS_RANDOM.STRING('U', 1) || DBMS_RANDOM.STRING('L', DBMS_RANDOM.VALUE(1,6)) AS "FIRST_NAME", DBMS_RANDOM.STRING('U', 1) || DBMS_RANDOM.STRING('L', DBMS_RANDOM.VALUE(1,10)) AS "LAST_NAME", ABS(TRUNC(DBMS_RANDOM.VALUE(200000000,999999999))) "CUST_PHONE", ROUND(DBMS_RANDOM.VALUE) AS "IS_SOMETHING" FROM ALL_OBJECTS WHERE ROWNUM <= 10000; |
Quick example, he said! Alright then! The date stuff in here took me quite a while to get right. If you follow along, you have to: cast the truncated value from a range between the current system date cast as a string in Julian date format as a Julian date and… I think there’s more? I forgot this as soon as I went to bed.
But the number and string stuff is really easy! Feeding in a range of numbers is super simple. The string stuff is just one upper case character with a random-length string appended to it. These look more like names that GUID substrings, but are probably only useful to anyone trying to come up with names for an entire colony of aliens.

I apologize if your name is in here.
This insert took 1.466 seconds, plus who knows how long getting date ranges figured out. Julian! JULIAN! Why I never.
So now we have some tables
We should probably add some indexes, and figure out how to join them, huh?
Those sound like good future blog post topics.
Thanks for reading!
10 Comments. Leave new
If anyone over at Oracle knew an admitted miscer was using their precious snowflake of a database their heads would explode. You may have dropped a John Cena (!!!) meme in there too.
Back on topic: a post on Flashback would be awesome, and make all the SQL Server guys (like myself) super jelly.
I wrote a little Flashback guide a while ago – it’s a brief introduction, so do check out the doco.
http://oracle-dba-scripts.blogspot.co.uk/2010/12/flashback-explained.html
EDIT: looks like our ol’ buddy Jeremiah Peschka already did a post on Flashback:
https://www.brentozar.com/archive/2015/01/oracle-flashback-undeleting-data/
Nice!
Generation of rows is usually done in Oracle with the “dual CONNECT BY LEVEL” trick:
http://www.orafaq.com/wiki/Oracle_Row_Generator_Techniques#CONNECT_BY_LEVEL
SELECT LEVEL just_a_column
FROM dual
CONNECT BY LEVEL <= 365
You don't need to care about how many rows you actually have in the underlying table/view and in newer Oracle versions you don't generate any IO whatsoever.
The comments about logging are a bit puzzling. NOLOGGING only affects specific operations such as direct-path load. What is “minimal logging”?
William – go ahead and Google for minimal logging in SQL Server, and you’ll find a ton of information about it. Also check out the Data Loading Performance Guide.
I was referring to the above post, which is about Oracle.
William — I’m talking about Minimal Logging in SQL Server and, how it’s different from NO LOGGING in Oracle.
Oh. Does it say that somewhere I missed? How is Oracle NOLOGGING different exactly? The article doesn’t really say much about either of them.
William — No, the article is about generating test data. If you’re looking for information about something else, this is the wrong blog post.