oracle19c配置scott创建,oracle 19c创建sample schema-HROESH等等

oracle 19c创建sample schema-HROESH等等

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

以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

[email protected]>@?/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；

示例输出：

HRINDEX19

HRPROCEDURE2

HRSEQUENCE3

HRTABLE7

HRTRIGGER2

HRVIEW1

SCOTTINDEX2

SCOTTTABLE4

SHTABLE2

参考连接：

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

oracle 19c创建sample schema-HROESH等等相关教程