Oracle sample schemas, I know we all have fondness for SCOTT and his pet cat, TIGER! But there are newer schemas out there now, like Human Resources (HR).

This collection of sample data was getting a bit old in the tooth, and it wasn’t as simple as it could be to install/deploy the schemas.

Well, yesterday, we released an updated copy of these sample schemas.

By ‘we’ I mean the royal we (Oracle), but our developer advocate product manager Gerald Venzl, led this project, and attended dozens of calls that I did not.

But I and everyone else gets to benefit ๐Ÿ™‚

So let’s take a quick look!

Step 1:Download and Extract

Once it’s downloaded, go ahead and extract the archive to your documents folder, or wherever you like to keep things handy.

Step 2: Startup SQLcl and connect as a DBA

I’m going to use SYSTEM, and I’m going to login to my pluggable database.

If you don’t have SQLcl, it’s easy to get, no login or license agreement required. If you’re in OCI, you can simply do ‘yum install sqlcl’ and if you’re on a Mac you can simply ‘brew install sqlcl’

It’s not necessary to drop the schemas in advance, the install script will do that for you.

I already had an SH, and I was happy to ‘nuke it from orbit.’ but before you do this, make sure no one hasn’t put application code/objects into these ‘sample’ schemas!

It wouldn’t be the first time someone used HR for something ‘real.’

I’m also going to change directories into where I downloaded and extracted the zip file, and go into the ‘sales_history’ directory.

Step 3: Run the installer SQL script

@sh_install.sql

What password to use for the new SH user?

Do you want to replace SH if it already exists? Default is YES.

Thank you, you’re welcome!

As the log scrolls down, you can see where the tables are both created AND populated.

This is the LOAD command in SQLcl.

And when the script is FINISHED, you can see we do a check on the schema to make sure it’s in the expected state.

That 2 minutes 52 seconds is for the entire SQLcl session, not the script time.

Step 0: Read the instructions?

You should probably read these? There is a main set of directions and then each schema comes with it’s own readme.

You’ll see that you need Database 19c or higher, and that you’ll need SQLcl. SQLcl is required because the scripts use the LOAD command, vs running INSERT scripts or SQLLDr.

Not needing SQLLDR or SQLPLUS means you don’t need an Oracle Client install and you don’t need any experience with SQL*Loader ๐Ÿ™‚

Let’s go look at our new data!

My SALES records and partitions are newer!

2019 SALES vs 1998 SALES ๐Ÿ™‚

Recent history.

And some more timeless types of products. You can tell this is American data as it’s Soccer vs Futbol ๐Ÿ™‚

72 products to choose from.

And the tables themselves haven’t really changed, we still have our ‘Star’ Schema.

Relational Diagram courtesy SQL Developer Data Modeler.

And because the schemas themselves haven’t changed, your existing demo/learning/sample SQL’s should also work just fine.

Sweet!

It was a lot of fun writing this blog post, but I’m blessed with the opportunity to show off the hard work of our database and documentation teams. Kudos!

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment