在oracle上安装hr schema
19c examples 安装完成,在$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://docs.oracle.com/en/database/oracle/oracle-database/19/comsc/toc.htm
解压缩
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