Do you need sample data so that you can try out the functions and features of Oracle Database? Then you can install Oracle database sample schemas from github. The latest version Oracle Database Sample Schemas 23c is super easy to install and of course it is for free. You only need to download the zip file and follow the instructions. Many thanks to Gerald Venzl for making this possible. Oracle Database Sample Schemas 23c are compatible with Oracle Database 19c and upwards, including Oracle Database Cloud Services and contain several improvements.

The Oracle Database sample schemas are based on a fictitious sample company that sells goods through various channels. The company operates worldwide to fill orders for products. It has several divisions, each of which is represented by a sample database schema. The following user schemas with the according data can be installed in a couple of minutes with latest version of Oracle Database Sample Schemas:

  • Human Resources (HR): Division Human Resources tracks information about the company employees and facilities.
  • Customer Orders (CO): Division Customer Orders models a simple retail application consisting of customer, product, store and order data.
  • Sales History (SH): Division Sales tracks business statistics to facilitate business decisions. This s designed to allow for demos with larger amounts of data. 

Note: Archived schemas such as OE (Order Entry) and PM (Product Media) are provided for reference for examples in the documentation but are no longer actively maintained. 

The following steps are required:

  1. Go to the latest (or appropriate) release of the sample schemas source .zip file.
  2. Extract the downloaded .zip file.
  3. Navigate to the sample schema directory that you want to install and run the <schema_name>_install.sql script.

To review the installation verify the generated installation log file at the end of the installation.

For the schema HR the following steps are:  

  • cd into the human_resources folder.
  • Connect to the target database with a privileged user.
  • Run the hr_install.sql script and provide inputs for the prompts.

And you are ready to start! 
Keep in mind that all scripts need to be run as a privileged user with rights to create and drop another user such as SYSTEM, ADMIN, etc.

If you want to install the schema CO, repeat the steps above with the according co_ scripts.

Regarding the schema SH it’s a little bit different. You need to run the scripts sh_install.sql from SQLcl.Because the table data is provided in CSV format, the SQLcl utility is required. You may download SQLcl from here. Please note SQLcl requires a minimum JDK of 11.0 to run.

If you review the script sh_populate.sql, you will recognize the power of SQLcl. Only two commands are needed to load e.g. the table COSTS. You do not need to involve SQL*Loader. Here is an extract from the script sh_populate.sql that demonstrates how CSV data will be loaded with SQLcl.

rem *************************** set loading parameters
SET LOAD BATCH_ROWS 10000 BATCHES_PER_COMMIT 1 DATE_FORMAT YYYY-MM-DD
rem *************************** insert data into the COSTS table
Prompt ******  Populating COSTS table ....
LOAD costs costs.csv

I tried the scripts in several Oracle Database environments, in 19c Oracle Database, in Oracle Database 23ai Free and in the latest 23ai Oracle Base Database Service. 
In addition in my test lab environment I disabled the password complexity to keep very simple and easy passwords. But this is not recommended for production enviornments. 

All the required objects and data were quickly installed and as a result the populated objects such as tables, table data, partitions, materialized views, indexes etc are available – with data and with statistics – as you can see here.

-- Created user with profile, account and read only status
SQL> col profile format a25
SQL> col username format a25 
SQL> select username, profile, account_status, read_only from dba_users order by created;
USERNAME                  PROFILE                   ACCOUNT_STATUS                   REA
------------------------- ------------------------- -------------------------------- ---
...
HR                        DEFAULT                   OPEN                             NO
CO                        DEFAULT                   OPEN                             NO
SH                        DEFAULT                   OPEN                             NO

Note: In 23ai you can change the user access to read only. The default is of course read write.

Here is the list of tables with number of rows and information about the last analyzed date. 

SQL> select table_name, num_rows, last_analyzed
     from dba_tables where owner='SH' order by num_rows desc;

TABLE_NAME                       NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
...
SALES                              918843 06-OCT-23
COSTS                               82112 06-OCT-23
CUSTOMERS                           55500 06-OCT-23
SUPPLEMENTARY_DEMOGRAPHICS           4500 06-OCT-23
TIMES                                1826 06-OCT-23
PROMOTIONS                            503 06-OCT-23
PRODUCTS                              144 06-OCT-23
COUNTRIES                              70 06-OCT-23
CHANNELS                               10 06-OCT-23
CAL_MONTH_SALES_MV                      0 06-OCT-23
FWEEK_PSCAT_SALES_MV                    0 06-OCT-23
18 rows selected.

Conclusion
If you need sample data, install the latest version of Oracle Database sample schemas (here 23ai). You only need to download the zip file and execute the scripts accordingly.
Keep in mind the scripts can be also used for other Oracle database versions such as 19c and of course on on-premises installations and in Oracle Database Cloud Services.

There are following advantages when using the latest release of sample schemas:

  • All data sets listed above have been refreshed.
  • Schemas can be installed independently from each other.
  • SYS/SYSTEM user account access is no longer required but a privileged user.
  • SQL*Loader is no longer required instead SQLcl will be used. 
     

For more information please refer to the following links: