Oracleサンプル・スキーマのインストール

概要

前回、Oracle Databaseをインストールしましたが、デフォルトでは下記の通りHuman Resources(HR)スキーマを作成するスクリプトが配置されています。
しかしながら、その他(SHやOE)のスクリプトは入っていませんでした。

[oracle@aaeca05e3663 schema]$ pwd
/opt/oracle/product/12.2.0.1/dbhome_1/demo/schema
[oracle@aaeca05e3663 schema]$ ll
total 60
-rw-r--r-- 1 oracle oinstall 2322 Apr 3 2009 drop_sch.sql
drwxr-xr-x 2 oracle oinstall 205 May 4 06:55 human_resources
drwxr-xr-x 2 oracle oinstall 6 May 4 06:58 log
-rw-r--r-- 1 oracle oinstall 1836 May 4 06:59 mk_dir.sql
-rw-r--r-- 1 oracle oinstall 1685 Nov 6 2015 mk_dir.sql.sbs
-rw-r--r-- 1 oracle oinstall 27570 Jul 1 2014 mkplug.sql
-rw-r--r-- 1 oracle oinstall 16894 Jul 1 2014 sted_mkplug.sql.dbl

今後、色々試行錯誤するのにその他のサンプルスキーマも必要と考えられたためインストールしてみました。

環境情報

ホストOS:Windows 10
VirtualBox:Ver 5.2.8
ゲストOS:CentOS 7 64bit
コンテナOS:Oracle Linux 7.5
Oracle:Oracle Database EE 12.2.0.1

スクリプトの場所

https://github.com/oracle/db-sample-schemas/releases/tag/v12.2.0.1

事前に上記よりOracle Database 12.2.0.1 Sample Schemasを作成するスクリプトをダウンロードしています。

ダウンロードファイル:db-sample-schemas-12.2.0.1.zip

コンテナ内のスクリプト配置場所

/home/oracle

■ダウンロードファイルの解凍

[oracle@aaeca05e3663 ~]$ pwd
/home/oracle
[oracle@aaeca05e3663 ~]$ ll
total 36516
-rw-rw-r-- 1 oracle oinstall 37389564 May 6 05:41 db-sample-schemas-12.2.0.1.zip
lrwxrwxrwx 1 oracle oinstall 26 May 4 07:00 setPassword.sh -> /opt/oracle/setPassword.sh
[oracle@aaeca05e3663 ~]$ unzip db-sample-schemas-12.2.0.1.zip
Archive: db-sample-schemas-12.2.0.1.zip
99049719d53c2e0810b7a8462965636b98161131
creating: db-sample-schemas-12.2.0.1/
inflating: db-sample-schemas-12.2.0.1/CONTRIBUTING.md
inflating: db-sample-schemas-12.2.0.1/LICENSE.md
inflating: db-sample-schemas-12.2.0.1/README.md
inflating: db-sample-schemas-12.2.0.1/README.txt
creating: db-sample-schemas-12.2.0.1/bus_intelligence/
inflating: db-sample-schemas-12.2.0.1/bus_intelligence/bi_main.sql
inflating: db-sample-schemas-12.2.0.1/bus_intelligence/bi_oe_pr.sql
inflating: db-sample-schemas-12.2.0.1/bus_intelligence/bi_sh_pr.sql
inflating: db-sample-schemas-12.2.0.1/bus_intelligence/bi_views.sql

～略～

inflating: db-sample-schemas-12.2.0.1/shipping/qs_drop.sql
inflating: db-sample-schemas-12.2.0.1/shipping/qs_es.sql
inflating: db-sample-schemas-12.2.0.1/shipping/qs_main.sql
inflating: db-sample-schemas-12.2.0.1/shipping/qs_os.sql
inflating: db-sample-schemas-12.2.0.1/shipping/qs_run.sql
inflating: db-sample-schemas-12.2.0.1/shipping/qs_ws.sql

[oracle@aaeca05e3663 ~]$ ll
total 36520
drwxr-xr-x 9 oracle oinstall 4096 May 18 2016 db-sample-schemas-12.2.0.1
-rw-rw-r-- 1 oracle oinstall 37389564 May 6 05:41 db-sample-schemas-12.2.0.1.zip
lrwxrwxrwx 1 oracle oinstall 26 May 4 07:00 setPassword.sh -> /opt/oracle/setPassword.sh
[oracle@aaeca05e3663 ~]$ cd db-sample-schemas-12.2.0.1
[oracle@aaeca05e3663 db-sample-schemas-12.2.0.1]$ ls
CONTRIBUTING.md README.md bus_intelligence human_resources mk_dir.sql mksample.sql mkverify.sql product_media shipping
LICENSE.md README.txt drop_sch.sql info_exchange mkplug.sql mkunplug.sql order_entry sales_history

■READMEファイルの確認

[oracle@aaeca05e3663 db-sample-schemas-12.2.0.1]$ cat README.txt
README.txt

Created 25-NOV-2003

Updated 19-Mar-2015

Permission is hereby granted, free of charge, to any person obtaining
a copy of this software and associated documentation files (the
"Software"), to deal in the Software without restriction, including
without limitation the rights to use, copy, modify, merge, publish,
distribute, sublicense, and/or sell copies of the Software, and to
permit persons to whom the Software is furnished to do so, subject to
the following conditions:

～略～

2. Instructions to create the schemas:

2.1 Decide on passwords for the Sample Schemas. Here, we are using
placeholder names inside "<" and ">" brackets:

SYSTEM: <SYSTEM_password>
SYS: <SYS_password>
HR: <HR_password>
OE: <OE_password>
PM: <PM_password>
IX: <IX_password>
SH: <SH_password>
BI: <BI_password>

2.2 Verify the value for the default tablespace, the temporary
tablespace, and the log file directory. For illustration purposes,
the values are:

default tablespace: EXAMPLE
temporary tablespace: TEMP
log file directory: $ORACLE_HOME/demo/schema/log

NOTE: Use your own passwords.

2.3 Verify the connect string for the database. For illustration
purposes, the value of <connect_string> for database pdb is:

connect_string: localhost:1521/pdb

The connect_string may also be a valid tnsnames.ora entry.

NOTE: Due to referencing external tables under the sample
schema directory, the sample schema files must be
extracted on and mksample executed on the host
containing the database.

2.4 Invoke the Sample Schema creation script. Using the values listed
in Sections 2.1, 2.2, and 2.3 the command would look like this:

SQL> @?/demo/schema/mksample <SYSTEM_password> <SYS_password>
<HR_password> <OE_password> <PM_password> <IX_password>
<SH_password> <BI_password> EXAMPLE TEMP
$ORACLE_HOME/demo/schema/log/ localhost:1521/pdb

The mksample script lists all the objects created in the data dictionary.

～略～

[oracle@aaeca05e3663 db-sample-schemas-12.2.0.1]$ cat README.md
# Oracle Database Sample Schemas

～略～

### 2.1. Clone this repository

```shell
cd $HOME
git clone https://github.com/oracle/db-sample-schemas.git

```

or download and extract the ZIP file:

```shell
unzip db-sample-schemas.zip
```

The schema directory should be owned by the Oracle Database software owner.

### 2.2. Change directory

```shell
cd $HOME/db-sample-schemas
```

### 2.3. Change all embedded paths to match your working directory

The installation scripts need your current directory embedded in
various locations. Use a text editor or the following Perl script to
make the changes, replacing occurrences of the token `__SUB__CWD__`
with your current working directory, for example
`/home/oracle/db-sample-schemas`

```shell
perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat
```

### 2.4. Set the Oracle environment

```shell
source /usr/local/bin/oraenv
```

*Note*: Oracle's `sqlldr` utility needs to be in `$PATH` for correct
loading of the Product Media (PM) and Sales History (SH) schemas.

### 2.5. Run the installation script

Review the [README.txt](#README.txt) for information on passwords and
pre-requirements. In particular, verify your default and temporary
tablespace names, and choose a password for each schema.

Start SQL*Plus and run the top level installation script as
discussed in [README.txt](#README.txt):

```shell
sqlplus system/systempw@connect_string
@mksample systempw syspw hrpw oepw pmpw ixpw shpw bipw users temp /your/path/to/log/ connect_string
```

*Note*: Use an absolute path and also append a trailing slash to the log directory name.

～略～

■README内の手順実行

[oracle@aaeca05e3663 db-sample-schemas-12.2.0.1]$ /opt/oracle/product/12.2.0.1/dbhome_1/perl/bin/perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat

■EXAMPLESスキーマ用表領域作成

[oracle@aaeca05e3663 db-sample-schemas-12.2.0.1]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sun May 6 06:51:08 2018

SQL> conn /as sysdba
Connected.
SQL> alter session set container=pdb1;

Session altered.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL> set line 300
SQL> set pages 10000
SQL> col TABLESPACE_NAME for a20
SQL> col FILE_NAME for a60
SQL> select f.file_id,f.tablespace_name,f.file_name,f.bytes,f.blocks,f.autoextensible
2 from dba_tablespaces t,dba_data_files f
3 where t.tablespace_name = f.tablespace_name
4 order by f.file_id;

FILE_ID TABLESPACE_NAME FILE_NAME BYTES BLOCKS AUT
---------- -------------------- ------------------------------------------------------------ ---------- ---------- ---
9 SYSTEM /opt/oracle/oradata/orcl/pdb1/system01.dbf 272629760 33280 YES
10 SYSAUX /opt/oracle/oradata/orcl/pdb1/sysaux01.dbf 377487360 46080 YES
11 UNDOTBS1 /opt/oracle/oradata/orcl/pdb1/undotbs01.dbf 104857600 12800 YES
12 USERS /opt/oracle/oradata/orcl/pdb1/users01.dbf 5242880 640 YES
SQL> create tablespace examples
2 datafile '/opt/oracle/oradata/orcl/pdb1/examples.dbf'
3 size 1G
4 autoextend on maxsize unlimited;

Tablespace created.

■スキーマ作成

SQL> !ls -la

total 176
drwxr-xr-x 9 oracle oinstall 4096 May 6 06:49 .
drwx------ 1 oracle oinstall 124 May 6 06:45 ..
-rw-r--r-- 1 oracle oinstall 117 May 18 2016 CONTRIBUTING.md
-rw-r--r-- 1 oracle oinstall 1050 May 18 2016 LICENSE.md
-rw-r--r-- 1 oracle oinstall 4931 May 18 2016 README.md
-rw-r--r-- 1 oracle oinstall 5263 May 18 2016 README.txt
drwxr-xr-x 2 oracle oinstall 180 May 6 06:49 bus_intelligence
-rw-r--r-- 1 oracle oinstall 3633 May 6 06:49 drop_sch.sql
-rw-r--r-- 1 oracle oinstall 3633 May 18 2016 drop_sch.sql.bak
drwxr-xr-x 2 oracle oinstall 4096 May 6 06:49 human_resources
drwxr-xr-x 2 oracle oinstall 168 May 6 06:49 info_exchange
-rw-r--r-- 1 oracle oinstall 2821 May 6 06:49 mk_dir.sql
-rw-r--r-- 1 oracle oinstall 2740 May 18 2016 mk_dir.sql.bak
-rw-r--r-- 1 oracle oinstall 27972 May 6 06:49 mkplug.sql
-rw-r--r-- 1 oracle oinstall 27756 May 18 2016 mkplug.sql.bak
-rw-r--r-- 1 oracle oinstall 7463 May 6 06:49 mksample.sql
-rw-r--r-- 1 oracle oinstall 7166 May 18 2016 mksample.sql.bak
-rw-r--r-- 1 oracle oinstall 6592 May 6 06:49 mkunplug.sql
-rw-r--r-- 1 oracle oinstall 6592 May 18 2016 mkunplug.sql.bak
-rw-r--r-- 1 oracle oinstall 6123 May 6 06:49 mkverify.sql
-rw-r--r-- 1 oracle oinstall 6123 May 18 2016 mkverify.sql.bak
drwxr-xr-x 3 oracle oinstall 8192 May 6 06:49 order_entry
drwxr-xr-x 2 oracle oinstall 4096 May 6 06:49 product_media
drwxr-xr-x 2 oracle oinstall 4096 May 6 06:49 sales_history
drwxr-xr-x 2 oracle oinstall 4096 May 6 06:49 shipping

--スクリプト実行(XXXXXXXXの部分にはパスワード入力)

@mksample.sql XXXXXXXX XXXXXXXX XXXXXXXX XXXXXXXX XXXXXXXX XXXXXXXX XXXXXXXX XXXXXXXX EXAMPLES TEMP $ORACLE_HOME/demo/schema/log/ localhost:1521/pdb1

※ターミナルログ上、トランザクションの実行/COMMITは実行されているものの色々エラーが出力されているように見えます。

■事後確認

SQL> select username from dba_users;

USERNAME
------------------------------------------------------------------------------------------
SYS
SYSTEM
XS$NULL
LBACSYS
OUTLN
DBSNMP

～略～

SYSDG
SPATIAL_CSW_ADMIN_USR
HR
BI
OE
PM
IX
SH
SQL> conn hr/XXXXXXXX@//localhost:1521/pdb1
SQL> set line 1000
SQL> set pages 2000
SQL> col TNAME for a30
SQL> select * from tab

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE

8 rows selected.

SQL> conn sh/XXXXXXX@//localhost:1521/pdb1
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
CAL_MONTH_SALES_MV TABLE
CHANNELS TABLE
COSTS TABLE
COUNTRIES TABLE
CUSTOMERS TABLE
DR$SUP_TEXT_IDX$I TABLE
DR$SUP_TEXT_IDX$K TABLE
DR$SUP_TEXT_IDX$N TABLE
DR$SUP_TEXT_IDX$R TABLE
DR$SUP_TEXT_IDX$U TABLE
FWEEK_PSCAT_SALES_MV TABLE
PRODUCTS TABLE
PROFITS VIEW
PROMOTIONS TABLE
SALES TABLE
SALES_TRANSACTIONS_EXT TABLE
SUPPLEMENTARY_DEMOGRAPHICS TABLE
TIMES TABLE

18 rows selected.

参考資料