在Oracle和MySQL上安装hr schema

oracleace 2022-04-01

745

—

在Oracle上安装hr schema

在$ORACLE_HOME/demo/schema/human_resources 目录下执行hr_main.sql 文件创建 hr用户：

SYS@orcl(CDB$ROOT)> alter session set container=pdb1;




Session altered.




SYS@orcl(CDB$ROOT)> create tablespace users datafile '/u01/app/oracle/oradata/ORCL/pdb1/user01.dbf' size 100m autoextend on;




Tablespace created.




SYSTEM@oracleace/pdb1.yaoyuan.com(PDB1)> @?/demo/schema/human_resources/hr_main.sql
specify password for HR as parameter 1:
Enter value for 1: hr
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

安装完成后进行检查：

$ sqlplus hr/hr@pdb1
...
SQL> select table_name from user_tables;




TABLE_NAME
--------------------------------------------------------------------------------
COUNTRIES
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY




7 rows selected.




SQL> select count(*) from employees;




  COUNT(*)
----------
       107

—

在MySQL上安装hr schema

在MySQL上也可以安装和Oracle同样的hr schema，参见：https://github.com/nomemory/hr-schema-mysql。安装方法如下：

wget https://github.com/nomemory/hr-schema-mysql/archive/refs/heads/master.zip
 unzip master.zip 
 [oracle@dell hr-schema-mysql-master]$ ll
total 36
-rw-r--r--. 1 oracle oinstall 31342 Feb 15  2021 hr-schema-mysql.sql
-rw-r--r--. 1 oracle oinstall   231 Feb 15  2021




[oracle@dell hr-schema-mysql-master]$ mysql < hr-schema-mysql.sql

安装完成后检查：





mysql> use hr;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A




Database changed
mysql> show tables;
+------------------+
| Tables_in_hr     |
+------------------+
| countries        |
| departments      |
| emp_details_view |
| employees        |
| job_history      |
| jobs             |
| locations        |
| regions          |
+------------------+
8 rows in set (0.00 sec)
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|      107 |
+----------+
1 row in set (0.01 sec)

—

安装examples

在Oracle官方文档中有一个安装文档《Database Examples Installation Guide》，这个文档只有一章！

官网下载 LINUX.X64_193000_examples.zip 压缩包，并上传服务器，解压目录结构如下

$ cd home/oracle/examples/
[oracle@wl examples]$ ls
install response runInstaller stage welcome.html

运行runInstaller进行图像界面的安装

字符静默安装

$ directory_path/runInstaller [-silent] -responseFile responsefilename

[oracle@oracleace examples]$ pwd
/media/sf_oracle/19c/examples
[oracle@oracleace examples]$ ./runInstaller -silent -force -ignorePrereq -ignoreSysPrereqs -responseFile media/sf_oracle/19c/examples/response/demos_install.rsp 
Starting Oracle Universal Installer...




Checking Temp space: must be greater than 415 MB.   Actual 28381 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 7935 MB    Passed
Preparing to launch Oracle Universal Installer from tmp/OraInstall2022-03-28_06-45-48PM. Please wait ...[oracle@oracleace examples]$ 
[oracle@oracleace examples]$ The response file for this session can be found at:
 u01/app/oracle/product/19.3.0/db_1/install/response/examples_2022-03-28_06-45-48PM.rsp




You can find the log of this install session at:
 u01/app/oraInventory/logs/installActions2022-03-28_06-45-48PM.log
Successfully Setup Software.
The installation of Oracle Database 19c Examples was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2022-03-28_06-45-48PM.log' for more details.

安装时并不直接修改数据库，而是安装脚本。

# 安装 HR、OE、PM、IX、SH、BI 用户

从 12.2 开始，Oracle 自带的 examples 只是包含了 HR 这个 schema，其他schema，需要自行从 github 下载和安装，19c 地址：

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

参考https://github.com/oracle-samples/db-sample-schemas文档：

解压缩

unzip db-sample-schemas-19c.zip

查看其中的readme.txt和readme.md文档

移动到对应的目录

[oracle@oracleace ~]$ cp -r /media/sf_oracle/19c/db-sample-schemas-19c $ORACLE_HOME/demo/schema


mv db-sample-schemas-19c  $ORACLE_HOME/demo/schema/

把脚本中的_SUB__CWD__替换为当前路径：

[oracle@wl db-sample-schemas-19c] pwd
/u01/app/oracle/product/19.3.0/db_1/demo/schema/db-sample-schemas-19c
[oracle@wl db-sample-schemas-19c]$ perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat

根据readme.md文档中的提示安装方法进行安装

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

[oracle@oracleace db-sample-schemas-19c]$ sqlplus system/oracle@oracleace/pdb1.yaoyuan.com
...
SYSTEM@oracleace/pdb1.yaoyuan.com(PDB1)> @?/demo/schema/db-sample-schemas-19c/mksample  oracle oracle hr oe pm ix sh bi users temp $ORACLE_HOME/demo/schema/log/ oracleace:1521/pdb1.yaoyuan.com

—

安装 scott schema

修改utlsampl.sql文件，把 CONNECT SCOTT/tiger 改为

CONNECT SCOTT/tiger@localhost/pdb1.yaoyuan.com

[oracle@oracleace ~]$ sqlplus system/oracle@localhost/pdb1.yaoyuan.com




SCOTT@localhost/pdb1.yaoyuan.com(PDB1)> @/u01/app/oracle/product/19.3.0/db_1/rdbms/admin/utlsampl.sql
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

查询安装好的表：

SCOTT@localhost/pdb1.yaoyuan.com(PDB1)> select table_name from user_tables;




TABLE_NAME
--------------------------------------------------------------------------------
DEPT
EMP
BONUS
SALGRADE

oracle mysql

文章转载自oracleace，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

在Oracle和MySQL上安装hr schema

评论