oracle 19c创建sample schema-HR,OE,SH等等

本文有两个目的：
1.数据库里面只安装HR用户, 19C也提供安装脚本；
2.一键安装HR、OE、SH等所有其他的用户，19C不在提供安装脚本，需要从GITHUB上下载，我已经下载完了，可以直接从本文下载或者从附上的连接直接下载，备注：下载的ZIP包 包含了所有相关联的脚本：

前言：

• 在创建之前查看临时表空间为执行脚本的输入做准备：

select tablespace_name from dba_temp_files;

• 查看表空间，打算把HR放到哪个表空间里面，查看表空间还有很大空间的，这里面我就放到USERS表空间了(假设回收站没开，表空间都是自动拓展的)：

select TABLESPACE_NAME,ROUND(SUM(BYTES/1024/1024),2)
CUR_M,ROUND(SUM(MAXBYTES/1024/1024),2) MAX_M from dba_data_files
where autoextensible=‘YES’ GROUP BY TABLESPACE_NAME ORDER BY 2

1. 只创建HR 用户

以SYSDBA登录.

sqlplus connect sys as sysdba
Enter password: password

• 跑hr_main.sql脚本，创建HR用户以及用户下的表：

SQL> @?/demo/schema/human_resources/hr_main.sql

-输入 HR的密码

specify password for HR as parameter 1:
Enter value for 1:

• 输入HR的默认表空间

Enter value for 2:
Enter temp as the temporary tablespace for HR

• 输入HR的临时表空间的名字:

Enter value for 3:
Enter your SYS password

• 输入 SYS的密码:

specify password for SYS as parameter 4:
Enter value for 4:

• Enter the directory path, for example, $ORACLE_HOME/demo/schema/log/,
  for your log directory

specify log path as parameter 5:
Enter value for 5:

• After script hr_main.sql runs successfully and schema HR is
  installed, you are connected as user HR. To verify that the schema
  was created, use the following command:

SQL> SELECT table_name FROM user_tables;

2. 一键安装 HR、OE、SH等所有其他的用户

下载链接：https://github.com/oracle/db-sample-schemas/releases/tag/v19c

包内容如下截图：

• 把这个安装包上传到服务器上，并放到$ORACLE_HOME/demo/sample的目录下，解压：

unzip db-sample-schemas-19c.zip
查看文件：
total 96K
drwxr-xr-x 2 oracle oinstall 230 Apr 6 2018 shipping
drwxr-xr-x 2 oracle oinstall 4.0K Apr 6 2018 sales_history
-rw-r–r-- 1 oracle oinstall 5.2K Apr 6 2018 README.txt
-rw-r–r-- 1 oracle oinstall 4.9K Apr 6 2018 README.md
drwxr-xr-x 2 oracle oinstall 4.0K Apr 6 2018 product_media
drwxr-xr-x 3 oracle oinstall 4.0K Apr 6 2018 order_entry
-rw-r–r-- 1 oracle oinstall 6.0K Apr 6 2018 mkverify.sql
-rw-r–r-- 1 oracle oinstall 6.5K Apr 6 2018 mkunplug.sql
-rw-r–r-- 1 oracle oinstall 7.0K Apr 6 2018 mksample.sql
-rw-r–r-- 1 oracle oinstall 28K Apr 6 2018 mkplug.sql
-rw-r–r-- 1 oracle oinstall 2.7K Apr 6 2018 mk_dir.sql
-rw-r–r-- 1 oracle oinstall 1.1K Apr 6 2018 LICENSE.md
drwxr-xr-x 2 oracle oinstall 79 Apr 6 2018 info_exchange
drwxr-xr-x 2 oracle oinstall 197 Apr 6 2018 human_resources
-rw-r–r-- 1 oracle oinstall 3.6K Apr 6 2018 drop_sch.sql
-rw-r–r-- 1 oracle oinstall 117 Apr 6 2018 CONTRIBUTING.md
drwxr-xr-x 2 oracle oinstall 85 Apr 6 2018 bus_intelligence

• 执行脚本，pw后缀的都是密码，可以手动输入，也可以使用以下方法作为参数输入

@?/demo/schema/mksample systempw syspw hrpw oepw pmpw ixpw shpw bipw
users temp /u01/dump/log dbtest19c

输出示例：

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 5 18:00:38 2019
Version 19.3.0.0.0

Copyright © 1982, 2019, Oracle. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production Version 19.3.0.0.0

SYS@hd11g>@?/demo/schema/mksample systempw syspw hrpw oepw pmpw ixpw
shpw bipw users temp /u01/dump/log dbtest19c

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 …

ERROR: ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE. SP2-0640: Not
connected SP2-0640: Not connected SP2-0640: Not connected SP2-0640:
Not connected SP2-0640: Not connected SP2-0640: Not connected ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0310: unable to open file
“SUB__CWD/human_resources/hr_main.sql” ERROR: ORA-01017: invalid
username/password; logon denied

SP2-0310: unable to open file “SUB__CWD/order_entry/oe_main.sql”
ERROR: ORA-01017: invalid username/password; logon denied

SP2-0310: unable to open file “SUB__CWD/product_media/pm_main.sql”
ERROR: ORA-01017: invalid username/password; logon denied

SP2-0310: unable to open file “SUB__CWD/info_exchange/ix_main.sql”
ERROR: ORA-01017: invalid username/password; logon denied

SP2-0310: unable to open file “SUB__CWD/sales_history/sh_main.sql”
ERROR: ORA-01017: invalid username/password; logon denied

SP2-0310: unable to open file
“SUB__CWD/bus_intelligence/bi_main.sql” ERROR: ORA-01017: invalid
username/password; logon denied

not spooling currently SP2-0310: unable to open file
“SUB__CWD/mkverify.sql” @>

• 查看验证这几个用户创建的OBJECTS

SELECT OWNER,OBJECT_TYPE, COUNT(1) FROM DBA_OBJECTS where owner in (‘HR’,‘OE’,‘SH’,‘IX’,‘BI’,‘PM’,‘SCOTT’) GROUP BY OWNER,OBJECT_TYPE
ORDER BY 1；

示例输出：
HR INDEX 19
HR PROCEDURE 2
HR SEQUENCE 3
HR TABLE 7
HR TRIGGER 2
HR VIEW 1
SCOTT INDEX 2
SCOTT TABLE 4
SH TABLE 2

参考连接：
https://docs.oracle.com/en/database/oracle/oracle-database/19/comsc/installing-sample-schemas.html#GUID-1E645D09-F91F-4BA6-A286-57C5EC66321D