最近、GitHubからダウンロードできるオラクル・データベースのサンプル・スキーマが更新されました。以前よりもインストールが容易になった、とのことなので確認してみます。
１年以上前にサンプル・スキーマをAutonomous Databaseにインストールしようとした記事があります。その時点のサンプル・スキーマと比較すると、bus_intelligence、info_exchange、shippingのサンプルが無くなっています。また、order_entry、product_mediaは含まれていますが、これらは更新されていないようです。
customer_orders、human_resources、sales_historyの３つサンプルは更新され、それぞれ個別にインストールできるようになりました。
Autonomous Databaseでは、sales_history（スキーマSH）はバンドルされているため、customer_orders、human_resourcesがインストールできるサンプルになります。
サンプル・スキーマのGitHubリポジトリは以下になります。
https://github.com/oracle-samples/db-sample-schemas
今回はOracle Database 23c Freeにサンプル・スキーマをインストールしてみます。PDBとしてFREEPDB1が作成済みで、このPDBにインストールします。
サンプル・スキーマのバージョン23.1のアーカイブをダウンロードします。
curl -OL https://github.com/oracle-samples/db-sample-schemas/archive/refs/tags/v23.1.zip[oracle@apex ~]$ curl -OL https://github.com/oracle-samples/db-sample-schemas/archive/refs/tags/v23.1.zip
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0
100 11.6M 0 11.6M 0 0 4335k 0 --:--:-- 0:00:02 --:--:-- 4712k
[oracle@apex ~]$
ダウンロードされたv23.1.zipを展開します。ディレクトリdb-sample-schemas-23.1が作成されます。
unzip v23.1.zip
[oracle@apex ~]$ unzip v23.1.zip
Archive: v23.1.zip
453bfac571ff74121093133f795fe908bedc01b6
creating: db-sample-schemas-23.1/
inflating: db-sample-schemas-23.1/CONTRIBUTING.md
inflating: db-sample-schemas-23.1/LICENSE.txt
inflating: db-sample-schemas-23.1/README.md
inflating: db-sample-schemas-23.1/README.txt
inflating: db-sample-schemas-23.1/SECURITY.md
creating: db-sample-schemas-23.1/customer_orders/
[中略]
inflating: db-sample-schemas-23.1/sales_history/sh_install.sql
inflating: db-sample-schemas-23.1/sales_history/sh_populate.sql
inflating: db-sample-schemas-23.1/sales_history/sh_uninstall.sql
inflating: db-sample-schemas-23.1/sales_history/supplementary_demographics.csv
inflating: db-sample-schemas-23.1/sales_history/times.csv
[oracle@apex ~]$
ディレクトリdb-sample-schemas-23.1に移動し、内容を確認します。
[oracle@apex db-sample-schemas-23.1]$ ls
CONTRIBUTING.md human_resources order_entry README.md sales_history
customer_orders LICENSE.txt product_media README.txt SECURITY.md
[oracle@apex db-sample-schemas-23.1]$
最初にcustomer_ordersをインストールします。ディレクトリcustomer_ordersに移動し、sqlplusでデータベースに接続してco_install.sqlを実行します。SYSDBAは不要になったので、system（Autonomous Databaseの場合はadmin）で作業ができるようになりました。
[oracle@apex customer_orders]$ sqlplus system/*******@localhost/freepdb1
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Fri Jun 2 14:32:13 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Tue May 23 2023 10:20:08 +09:00
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> @co_install
Thank you for installing the Oracle Customer Orders Sample Schema.
This installation script will automatically exit your database session
at the end of the installation or if any error is encountered.
The entire installation will be logged into the 'co_install.log' log file.
Enter a password for the user CO: ********
Enter a tablespace for CO [USERS]:
Do you want to overwrite the schema, if it already exists? [YES|no]: YES
****** Creating CUSTOMERS table ....
Table created.
****** Creating STORES table ....
Table created.
****** Creating PRODUCTS table ....
[中略]
Installation
-------------
Verification:
Table provided actual
----------- ---------- ----------
customers 392 392
stores 23 23
products 46 46
orders 1950 1950
shipments 1892 1892
order_items 3914 3914
inventory 566 566
Thank you!
--------------------------------------------------------
The installation of the sample schema is now finished.
Please check the installation verification output above.
You will now be disconnected from the database.
Thank you for using Oracle Database!
Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
[oracle@apex customer_orders]$
[oracle@apex human_resources]$ sqlplus system/********@localhost/freepdb1
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Fri Jun 2 14:42:30 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Fri Jun 02 2023 14:32:13 +09:00
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> @hr_install
Thank you for installing the Oracle Human Resources Sample Schema.
This installation script will automatically exit your database session
at the end of the installation or if any error is encountered.
The entire installation will be logged into the 'hr_install.log' log file.
Enter a password for the user HR: ********
Enter a tablespace for HR [USERS]:
Do you want to overwrite the schema, if it already exists? [YES|no]: YES
****** Creating REGIONS table ....
Table created.
Index created.
Table altered.
****** Creating COUNTRIES table ....
[中略]
Installation
-------------
Verification:
Table provided actual
----------- ---------- ----------
regions 5 5
countries 25 25
departments 27 27
locations 23 23
employees 107 107
jobs 19 19
job_history 10 10
Thank you!
--------------------------------------------------------
The installation of the sample schema is now finished.
Please check the installation verification output above.
You will now be disconnected from the database.
Thank you for using Oracle Database!
Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
[oracle@apex human_resources]$
[oracle@apex sales_history]$ sqlplus system/******@localhost/freepdb1
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Fri Jun 2 14:45:50 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Fri Jun 02 2023 14:42:30 +09:00
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> @sh_install
Thank you for installing the Oracle Sales History Sample Schema.
This installation script will automatically exit your database session
at the end of the installation or if any error is encountered.
The entire installation will be logged into the 'sh_install.log' log file.
Enter a password for the user SH:
Enter a tablespace for SH [USERS]:
Do you want to overwrite the schema, if it already exists? [YES|no]: YES
Start time: 02-JUN-23 02.46.05.407967 PM +09:00
****** Creating COUNTRIES table ....
Table created.
[中略]
Installation
-------------
Verification:
Table provided actual
-------------------------- ---------- ----------
channels 5 5
costs 82112 0
countries 35 35
customers 55500 0
products 72 72
promotions 503 0
sales 918843 0
times 1826 0
supplementary_demographics 4500 0
Thank you!
--------------------------------------------------------
The installation of the sample schema is now finished.
Please check the installation verification output above.
You will now be disconnected from the database.
Thank you for using Oracle Database!
Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
[oracle@apex sales_history]$
- Run
perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.datin this folder - Connect as privileged user with rights to create another user (
SYSTEM,ADMIN, etc.) - Run the
oe_main.sqlscript passing on the following parameters:OEschema name password- The tablespace name where to install the schema into
- The temporary tablespace name for the
OEuser - The password of the
HRuser - The
SYSpassword - The full path of this parent directory
- The full path of a directory where to write the install log file to
- The version number
3 - The connect string for the database you connected to
[oracle@apex ~]$ cd db-sample-schemas-23.1
[oracle@apex db-sample-schemas-23.1]$ perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat
Can't open *.sql: No such file or directory.
[oracle@apex db-sample-schemas-23.1]$
ディレクトリorder_entryに移動し、インストール・スクリプトのoe_main.sqlを実行します。READMEに記載されているパラメータの説明には正しくないものがあります。
[oracle@apex order_entry]$ sqlplus sys/*******@localhost/freepdb1 as sysdba
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Fri Jun 2 15:00:49 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> @oe_main
specify password for OE as parameter 1:
Enter value for 1: ********
specify default tablespeace for OE as parameter 2:
Enter value for 2: USERS
specify temporary tablespace for OE as parameter 3:
Enter value for 3: TEMP
specify password for HR as parameter 4:
Enter value for 4: ********
specify password for SYS as parameter 5:
Enter value for 5: ********
specify directory path for the data files as parameter 6:
Enter value for 6: /home/oracle/db-sample-schemas-23.1/order_entry/
writeable directory path for the log files as parameter 7:
Enter value for 7: /home/oracle/
specify version as parameter 8:
Enter value for 8: v3
specify connect string as parameter 9:
Enter value for 9: localhost/freepdb1
DROP USER oe CASCADE
*
ERROR at line 1:
ORA-01918: user 'OE' does not exist
old 1: CREATE USER oe IDENTIFIED BY &pass
new 1: CREATE USER oe IDENTIFIED BY oracle
User created.
old 1: ALTER USER oe DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs
new 1: ALTER USER oe DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS
User altered.
[中略]
Synonym created.
Synonym created.
Synonym created.
SQL>
[oracle@apex product_media]$ sqlplus sys/*****@localhost/freepdb1 as sysdba
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Fri Jun 2 15:09:08 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> @pm_main
specify password for PM as parameter 1:
Enter value for 1: ********
specify default tablespeace for PM as parameter 2:
Enter value for 2: USERS
specify temporary tablespace for PM as parameter 3:
Enter value for 3: TEMP
specify password for OE as parameter 4:
Enter value for 4: *******
specify password for SYS as parameter 5:
Enter value for 5: *******
specify directory path for the PM data files as parameter 6:
Enter value for 6: /home/oracle/db-sample-schemas-23.1/product_media/
specify directory path for the PM load log files as parameter 7:
Enter value for 7: /home/oracle/
specify work directory path as parameter 8:
Enter value for 8: /home/oracle/db-sample-schemas-23.1/product_media/
specify connect string as parameter 9:
Enter value for 9: localhost/freepdb1
DROP USER pm CASCADE
*
ERROR at line 1:
ORA-01918: user 'PM' does not exist
old 1: CREATE USER pm IDENTIFIED BY &pass
new 1: CREATE USER pm IDENTIFIED BY oracle
User created.
old 1: ALTER USER pm DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs
new 1: ALTER USER pm DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS
User altered.
old 1: ALTER USER pm TEMPORARY TABLESPACE &ttbs
new 1: ALTER USER pm TEMPORARY TABLESPACE TEMP
[中略]
Table PRINT_MEDIA:
4 Rows successfully loaded.
Table TEXTDOCS_NESTEDTAB:
12 Rows successfully loaded.
Check the log file:
/home/oracle/pm_p_lob.log
for more information about the load.
PL/SQL procedure successfully completed.
SQL>
