本文共 8746 字，大约阅读时间需要 29 分钟。
1背景说明
Oracle 11gR2中示例用户的安装说明参考如下链接：
Oracle 11gR2 中 示例用户 安装说明
Oracle 12cR2的示例用户说明可以参考官方手册：
Introduction to Sample Schemas
多年来，Oracle都是使用简单的数据库用户SCOTT进行文档和培训的各种示例。该用户里主要有2个表： EMP和DEPT，这些表不足以显示Oracle数据库和其他Oracle产品的基本特性。
示例数据库模式为Oracle数据库的每个版本提供了一个通用的平台。在Oracle 12cR2中，提供了如下测试用户：
Schema HR – Division Human Resources tracks information about the company employees and facilities.
Schema OE – Division Order Entry tracks product inventories and sales of company products through various channels.
Schema PM – Division Product Media maintains descriptions and detailed information about each product sold by the company.
Schema IX – Division Information Exchange manages shipping through B2B applications.
Schema SH – Division Sales tracks business statistics to facilitate business decisions.
但是从11gR2开始，$ORACLE_HOME/demo/schema/目录下已经没有mksample.sql文件，默认只有一个HR用户的创建脚本：
[oracle@dave.cndba.cn schema]$ pwd
/u01/app/oracle/product/12.2.0/dbhome_1/demo/schema
[oracle@dave.cndba.cn schema]$ ls
drop_sch.sql human_resources log mk_dir.sql mk_dir.sql.sbs mkplug.sql sted_mkplug.sql.dbl
[oracle@dave.cndba.cn schema]$ cd human_resources/
[oracle@dave.cndba.cn human_resources]$ ls
hr_analz.sql hr_code.sql hr_comnt.sql hr_cre.sql hr_drop_new.sql hr_drop.sql hr_idx.sql hr_main_new.sql hr_main.sql hr_popul.sql
[oracle@dave.cndba.cn human_resources]$
如果想安装完整的示例数据库，可以从github上下载：
2安装默认的HR用户
如果只需要安装HR用户，直接执行默认的脚本即可，示例如下：
在12c中需要在pdb中执行脚本，因为在CDB中无法创建hr这种普通用户，脚本会执行失败：
CREATE USER hr IDENTIFIED BY oracle
*
ERROR at line 1:
ORA-65096: invalid common user or role name
[oracle@dave.cndba.cn admin]$ sqlplus system/oracle@dave
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 12 20:02:55 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Mon Apr 17 2017 05:38:02 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @?/demo/schema/human_resources/hr_main.sql
specify password for HR as parameter 1:
Enter value for 1: oracle
specify default tablespeace for HR as parameter 2:
Enter value for 2: users
specify temporary tablespace for HR as parameter 3:
Enter value for 3: temp
specify log path as parameter 4:
Enter value for 4: /tmp
PL/SQL procedure successfully completed.
User created.
User altered.
User altered.
Grant succeeded.
Grant succeeded.
Session altered.
Session altered.
Session altered.
****** Creating REGIONS table ....
Table created.
Index created.
Table altered.
****** Creating COUNTRIES table ....
Table created.
Table altered.
****** Creating LOCATIONS table ....
Table created.
Index created.
Table altered.
Sequence created.
****** Creating DEPARTMENTS table ....
Table created.
Index created.
Table altered.
Sequence created.
****** Creating JOBS table ....
Table created.
Index created.
Table altered.
****** Creating EMPLOYEES table ....
Table created.
Index created.
Table altered.
Table altered.
Sequence created.
****** Creating JOB_HISTORY table ....
Table created.
Index created.
Table altered.
****** Creating EMP_DETAILS_VIEW view ...
View created.
Commit complete.
Session altered.
****** Populating REGIONS table ....
1 row created.
1 row created.
1 row created.
1 row created.
****** Populating COUNTIRES table ....
1 row created.
1 row created.
......
1 row created.
****** Populating LOCATIONS table ....
1 row created.
......
1 row created.
****** Populating DEPARTMENTS table ....
Table altered.
1 row created.
......
1 row created.
****** Populating JOBS table ....
1 row created.
......
1 row created.
****** Populating EMPLOYEES table ....
1 row created.
......
1 row created.
****** Populating JOB_HISTORY table ....
1 row created.
......
Commit complete.
PL/SQL procedure successfully completed.
SQL>
3安装完整的示例用户
从github上下载源代码：
上传到数据库服务器：
[oracle@dave.cndba.cn demo]$ cp /home/oracle/db-sample-schemas-12.2.0.1.zip $ORACLE_HOME/demo
[oracle@dave.cndba.cn demo]$ ls
db-sample-schemas-12.2.0.1.zip schema
[oracle@dave.cndba.cn demo]$ unzip db-sample-schemas-12.2.0.1.zip
[oracle@dave.cndba.cn demo]$ mv schema schema.bak
[oracle@dave.cndba.cn demo]$ mv db-sample-schemas-12.2.0.1 schema
具体说明可以参考Readme文件。
安装语法：
SQL> @?/demo/schema/mksample
EXAMPLE TEMP
$ORACLE_HOME/demo/schema/log/ localhost:1521/pdb
使用命令：
SQL> @mksample oracle oracle oracle oracle oracle oracle oracle oracle dave temp '/tmp/log/' dave
如果执行执行会报如下错误，这里是__SUB__CWD__路径不对：
SP2-0310: unable to open file "__SUB__CWD__/human_resources/hr_cre.sql"
SP2-0310: unable to open file "__SUB__CWD__/human_resources/hr_popul.sql"
SP2-0310: unable to open file "__SUB__CWD__/human_resources/hr_idx.sql"
SP2-0310: unable to open file "__SUB__CWD__/human_resources/hr_code.sql"
SP2-0310: unable to open file "__SUB__CWD__/human_resources/hr_comnt.sql"
SP2-0310: unable to open file "__SUB__CWD__/human_resources/hr_analz.sql"
not spooling currently
上面的错误显示不能打开__SUB__CWD__/目录下的相关脚本文件，查看sql脚本文件：
@__SUB__CWD__/order_entry/oe_main.sql &&password_oe &&default_ts &&temp_ts &&password_hr &&password_sys __SUB__CWD__/order_entry/ &&logfile_dir &vrs &&connect_string
这里我们需要将__SUB__CWD__/替换成脚本的绝对路径即可。
[oracle@dave.cndba.cn schema]$ sed -i "s#__SUB__CWD__#$(pwd)#g" `grep __SUB__CWD__ -rl --include="*.sql" ./`
[oracle@dave.cndba.cn schema]$ ls
bus_intelligence drop_sch.sql.bak LICENSE.md mkplug.sql mksample.sql.bak mkverify.sql product_media sales_history
CONTRIBUTING.md human_resources mk_dir.sql mkplug.sql.bak mkunplug.sql mkverify.sql.bak README.md shipping
drop_sch.sql info_exchange mk_dir.sql.bak mksample.sql mkunplug.sql.bak order_entry README.txt
[oracle@dave.cndba.cn schema]$ cat mksample.sql
…
@/u01/app/oracle/product/12.1.0/db_1/demo/schema/order_entry/oe_main.sql &&password_oe &&default_ts &&temp_ts &&password_hr &&password_sys /u01/app/oracle/product/12.1.0/db_1/demo/schema/order_entry/ &&logfile_dir &vrs &&connect_string
..
路劲已经替换成绝对路径了。 然后在执行：
[oracle@dave.cndba.cn schema]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 13 13:37:06 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @?/demo/schema/mksample oracle oracle oracle oracle oracle oracle oracle oracle dave temp '/tmp/log/' dave
specify password for SYSTEM as parameter 1:
specify password for SYS as parameter 2:
specify password for HR as parameter 3:
specify password for OE as parameter 4:
specify password for PM as parameter 5:
specify password for IX as parameter 6:
specify password for SH as parameter 7:
specify password for BI as parameter 8:
specify default tablespace as parameter 9:
specify temporary tablespace as parameter 10:
specify log file directory (including trailing delimiter) as parameter 11:
specify connect string as parameter 12:
Sample Schemas are being created ...
mkdir: cannot create directory ‘/tmp/log/’: File exists
……
Table cardinality relational and object tables
OWNER TABLE_NAME NUM_ROWS
------ ------------------------------ ----------
HR COUNTRIES 25
HR DEPARTMENTS 27
HR EMPLOYEES 107
HR JOBS 19
HR JOB_HISTORY 10
HR LOCATIONS 23
HR REGIONS 4
IX AQ$_ORDERS_QUEUETABLE_G 0
IX AQ$_ORDERS_QUEUETABLE_H 2
IX AQ$_ORDERS_QUEUETABLE_I 2
IX AQ$_ORDERS_QUEUETABLE_L 2
IX AQ$_ORDERS_QUEUETABLE_S 4
IX AQ$_ORDERS_QUEUETABLE_T 0
IX AQ$_STREAMS_QUEUE_TABLE_C 0
IX AQ$_STREAMS_QUEUE_TABLE_G 0
IX AQ$_STREAMS_QUEUE_TABLE_H 0
IX AQ$_STREAMS_QUEUE_TABLE_I 0
IX AQ$_STREAMS_QUEUE_TABLE_L 0
IX AQ$_STREAMS_QUEUE_TABLE_S 1
IX AQ$_STREAMS_QUEUE_TABLE_T 0
IX ORDERS_QUEUETABLE
IX STREAMS_QUEUE_TABLE
IX SYS_IOT_OVER_75148 0
IX SYS_IOT_OVER_75177 0
OE ACTION_TABLE 132
OE CATEGORIES_TAB 22
OE CUSTOMERS 319
OE INVENTORIES 1112
OE LINEITEM_TABLE 2232
OE ORDERS 105
OE ORDER_ITEMS 665
OE PRODUCT_DESCRIPTIONS 8640
OE PRODUCT_INFORMATION 288
OE PRODUCT_REF_LIST_NESTEDTAB 288
OE PROMOTIONS 2
OE PURCHASEORDER 132
OE SUBCATEGORY_REF_LIST_NESTEDTAB 21
OE WAREHOUSES 9
PM ONLINE_MEDIA 9
PM PRINT_MEDIA 4
PM TEXTDOCS_NESTEDTAB 12
SH CAL_MONTH_SALES_MV 48
SH CHANNELS 5
SH COSTS 82112
SH COUNTRIES 23
SH CUSTOMERS 55500
SH DR$SUP_TEXT_IDX$I
SH DR$SUP_TEXT_IDX$K
SH DR$SUP_TEXT_IDX$N
SH DR$SUP_TEXT_IDX$R
SH DR$SUP_TEXT_IDX$U
SH FWEEK_PSCAT_SALES_MV 11266
SH PRODUCTS 72
SH PROMOTIONS 503
SH SALES 918843
SH SALES_TRANSACTIONS_EXT 916039
SH SUPPLEMENTARY_DEMOGRAPHICS 4500
SH TIMES 1826
58 rows selected.
https://www.cndba.cn/dave/article/1985
https://www.cndba.cn/dave/article/1985
https://www.cndba.cn/dave/article/1985
https://www.cndba.cn/dave/article/1985
https://www.cndba.cn/dave/article/1985
https://www.cndba.cn/dave/article/1985
https://www.cndba.cn/dave/article/1985
https://www.cndba.cn/dave/article/1985
https://www.cndba.cn/dave/article/1985
https://www.cndba.cn/dave/article/1985
版权声明：本文为博主原创文章，未经博主允许不得转载。
Oracle 12cR2 安装示例用户(Sample Schemas)