Sample Data Sets for Oracle

A collection of common data sets for testing purposes

Sometimes you need only some small tables and some rows of data to play around with, to test things out. I think most of you immediately would say EMP, DEPT…

Sure, but a bit more data would be nice. Ok, OE and HR schema…

Sure, but I have no SYS rights on the database in my current project to install the schemas, can I have it in my current dev schema?…

Sure, but then I have to fiddle a bit with the scripts to make it run in my dev schema, I need it fast, no time at all…

Mmhhh, sounds this familiar to you? Maybe I can help…

The sample data sets from Oracle were normally created for a dedicated schema. I changed that and omitted all the schema creation parts of the scripts. Instead, all objects from a data set get a prefix - an example: All objects from the customer orders schema (one of the newer data sets from Oracle) are prefixed with CO_. This allows me to use all data sets in parallel in a single schema and easily identify the data set the object belongs to.

All table scripts are created with identity columns and the simplest possible options for easy readability - no fancy constraint and index names. Constraints and indexes will be renamed with global helper scripts after the table creation. Only real indexes are coded - foreign key indexes are generated with a global helper script too.

For each data set, you have always the same three scripts to handle the installation/data refresh/uninstallation.

List of Data Sets, Copyrights

EMP & DEPT (2 tabs, 18 rows), source, Copyright Oracle (MIT license)
Order Entry & Human Resources (15 tabs, 3.002 rows), source oe, source hr, Copyright Oracle (MIT license)
Customer Orders (5 tabs, 6.325 rows), source, Copyright Oracle (MIT license)
Sakila DVD Rental Store (16 tabs, 46.273 rows), source, original source, Copyright MySQL AB documentation team, DB Software Laboratory, Lukas Eder (BSD-3-Clause license)

For more info about the Oracle sample schemas see the docs.

List of Global Helper Scripts

Disable/enable all foreign key constraints
Create missing foreign key indexes
Unify constraint names
Unify index names
Sync sequence values to data

You can find the project on GitHub.

Hope this helps someone else…

Happy data modeling and testing :-)

Ottmar

P.S. For those of you who are working with Oracle APEX: Have a look under SQL Workshop > Utilities > Sample Datasets