Sample Data Sets for Oracle

A collection of common data sets for testing purposes

Posted by Ottmar Gobrecht on , tagged with Open Source Project, Oracle and Script

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

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