It’s always a good idea to be wary of assuming too much.
Looking at the current Conservative Party Leadership contest, you might assume that a fantasy Brexit policy and a history of class A drug use were pre-requisites for the job of Prime Minister.
You may further assume that one is a result of the other.
That last assumption is unlikely however, unless the smoking, snorting and otherwise ingesting of illicit substances is widespread across all of the other major political parties. Then again…
For my part, I’ve had to correct some of my assumptions about the process for installing the OE sample schema into Oracle 18cXE running on CentOS 7.
What follows is a quick guide on how to accomplish this…without all the head-scratching over apparently spurious errors.
Specifically, I will be covering :
- getting the OE schema installation scripts
- checking the pre-requisites for the OE schema installation have been met
- preparing the scripts for execution
- performing the installation
Before we go too much further though, it’s probably wise to state some assumptions…
These steps assume that you’re running 18cXE on CentOS or some other Red Hat compatible distro ( e.g. Oracle Linux, Fedora).
We’re only installing the OE schema here. I already have the HR schema installed on the database and I do not want to drop and re-create it.
If you want to install all of the demo schemas then you’ll need to check the instructions in the README.md file once you’ve downloaded the installation scripts.
Speaking of which…
Finding the demo scripts
Although we’re only interested in the OE schema at the moment, the source code is provided in a single archive file.
Download the zip for the appropriate database release ( 18c in my case) and we should now have a file looking something like :
-rw-rw-r-- 1 mike mike 28882177 Jun 9 17:03 db-sample-schemas-18c.zip
Pre-requisites for installing the OE schema
Before I run off and start playing with my new zip file, I should really check that I’ve got everything I need to ensure that the setup will go smoothly.
The pre-requisites are :
- the HR schema must already be installed and unlocked in the PDB that you are installing into
- Oracle Spatial must be enabled
- the installation scripts need to run on the database server (or a filesystem visible to it)
To check that HR is already available in the PDB (xepdb1 in my case) :
alter session set container = xepdb1; select username, account_status from cdb_users where username = 'HR' and con_id = sys_context('userenv', 'con_id'); USERNAME ACCOUNT_STATUS ---------- --------------- HR OPEN
If the query does not return any rows then you will need to install the HR schema. This can be done following the instructions in the aforementioned documentation.
NOTE – before you do this it’s a good idea to double check to make sure that you are in the correct container database :
select sys_context('userenv', 'con_name') from dual;
If the ACCOUNT_STATUS is LOCKED then you need to unlock the HR user as the OE creation script will attempt to connect to the database as HR. To do this, connect to the target PDB as a user with the ALTER USER privilege (e.g. SYSTEM) and run :
alter user hr account unlock; User HR altered
As I’m on 18cXE, Oracle Spatial should be enabled. Thanks to Norman, I know that I can confirm this by running :
select parameter, value from v$option where regexp_like(parameter, 'spatial', 'i') / PARAMETER VALUE -------------------- ---------- Spatial TRUE
Next, we need to upload the zip file to the Database Server (which I’ve done using sftp).
Preparing the Install scripts
Now it’s on the correct machine, I need to change the ownership to oracle…
sudo chown oracle:oinstall db-sample-schemas-18c.zip ls -l db-sample-schemas-18c.zip -rw-rw-r--. 1 oracle oinstall 28882177 Jun 10 12:34 db-sample-schemas-18c.zip
…because I want to move it to the appropriate directory in ORACLE_HOME…
sudo mv db-sample-schemas-18c.zip $ORACLE_HOME/demo/schema cd $ORACLE_HOME/demo/schema
…and extract it as oracle…
sudo su oracle unzip db-sample-schemas-18c.zip cd db-sample-schemas-18c
… and create a directory to hold the log file that’s output when we run the installation…
Now we’ve extracted the files, you may be tempted to have a quick peek at the code.
Initially you may be somewhat surprised. For example, in order_entry/oe_main.sql you can see :
DEFINE vscript = __SUB__CWD__/order_entry/coe_&vrs
Is __SUB__CWD__ some SQL*Plus magic that has eluded you all these years ?
Well, no. Looking that the README.md file, we can confirm that it’s simply a placeholder that we need to replace with a valid absolute path to the scripts.
Fortunately, the README.md file also contains the code required to achieve this.
Simply ensure that we’re in the db-sample-schemas-18c directory and run :
perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat
After running this, we can see that the line in oe_main.sql now reads :
DEFINE vscript = /opt/oracle/product/18c/dbhomeXE/demo/schema/db-sample-schemas-18c/order_entry/coe_&vrs
Performing the installation
The main order entry script is in the order_entry sub-directory and is called oe_main.sql.
This script accepts 9 positional parameters :
- the password for the OE user that will be created as part of this installation
- the default tablespeace for OE
- the temporary tablespace for OE
- the password for the pre-existing HR user
- the SYS password
- the directory path for the data files (these are in the current directory as they are included in the zip file)
- the absolute path for the log directory we just created ( including a trailing slash)
- the version (it’s v3 in this case)
- the SQL*Plus connect string for the target database
The “version” the script asks for is appended to the basename of some of the scripts that will be run as part of the install.
To obtain this, simply run :
ls -1 *_v?.sql ccus_v3.sql cidx_v3.sql cmnt_v3.sql coe_v3.sql cord_v3.sql cwhs_v3.sql doe_v3.sql loe_v3.sql pcus_v3.sql poe_v3.sql pord_v3.sql pwhs_v3.sql
From this, I can see that the value of version that the script needs is “v3”.
Now, connect to the database via SQL*Plus as SYSTEM. Note that, as I’ve not setup an Oracle client on the server, I need to specify the server name, tns port and pdb name in the connect string :
Finally, we can run the install script :
@oe_main.sql oe_user_password users temp hr_user_password sys_password . /opt/oracle/product/18c/dbhomeXE/demo/schema/db-sample-schemas-18c/order_entry/logs/ v3 frea.virtualbox:1522/xepdb1
The log file generated during the installation will contain everything that now whizzes up the screen.
You can find it after the run in the logs directory we created earlier :
ls -l logs total 8 -rw-r--r--. 1 oracle oinstall 6244 Jun 10 13:50 oe_oc_v3.log
Even if there aren’t any particularly alarming errors in the logfile, it would be nice to verify that all has gone as intended.
First we can check that the OE schema now contains the expected number of objects of each type :
select object_type, count(object_name) as "Number of objects" from dba_objects where owner = 'OE' group by object_type order by 2 desc; OBJECT_TYPE Number of objects -------------------- ----------------- INDEX 48 TYPE 37 LOB 15 TABLE 14 VIEW 13 SYNONYM 6 TRIGGER 4 TYPE BODY 3 SEQUENCE 1 FUNCTION 1 10 rows selected.
We can also confirm that data has been loaded into the tables :
set serverout on size unlimited declare l_count pls_integer; begin for r_tab in ( select dbms_assert.sql_object_name(owner||'.'||table_name) oe_table from dba_tables where owner = 'OE' and nested = 'NO' order by table_name) loop execute immediate 'select count(*) from '||r_tab.oe_table into l_count; dbms_output.put_line(upper(r_tab.oe_table)||' contains '||l_count||' records.'); end loop; end; / OE.CUSTOMERS contains 319 records. OE.INVENTORIES contains 1112 records. OE.ORDERS contains 105 records. OE.ORDER_ITEMS contains 665 records. OE.PRODUCT_DESCRIPTIONS contains 8640 records. OE.PRODUCT_INFORMATION contains 288 records. OE.PROMOTIONS contains 2 records. OE.WAREHOUSES contains 9 records. PL/SQL procedure successfully completed.
The Country may be descending into chaos but at least we’ve got a working OE schema to play around with.