Oracle 12cR2 安装示例用户(Sample Schemas) -- cnDBA.cn

1 背景说明

Oracle 11gR2中示例用户的安装说明参考如下链接：

Oracle 11gR2 中示例用户安装说明

http://www.cndba.cn/dave/article/1133

Oracle 12cR2的示例用户说明可以参考官方手册：

Introduction to Sample Schemas

http://docs.oracle.com/database/122/COMSC/introduction-to-sample-schemas.htm#COMSC005

多年来，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上下载：

https://github.com/oracle/db-sample-schemas/releases/latest

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上下载源代码：

https://github.com/oracle/db-sample-schemas/releases/latest

上传到数据库服务器：

[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 <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

使用命令：

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.

Oracle 12cR2 安装示例用户(Sample Schemas)

签到成功

CNDBA社区

Oracle 12cR2 安装示例用户(Sample Schemas)

dave

QQ交流群

注册联系QQ