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’
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.
As the log scrolls down, you can see where the tables are both created AND populated.
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.
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 🙂
And some more timeless types of products. You can tell this is American data as it’s Soccer vs Futbol 🙂
And the tables themselves haven’t really changed, we still have our ‘Star’ Schema.
And because the schemas themselves haven’t changed, your existing demo/learning/sample SQL’s should also work just fine.
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!
