如何使用github安装Oracle 数据库Sample Schema （示例Schema）

Sample Schema可以在创建数据库时安装就是使用DBCA GUI或命令行命令行中指定-sampleSchema true。以下介绍的利用Github中的项目安装。首先是一个容器数据库的例子这时pdb只能建立1个。文档https://docs.oracle.com/en/database/oracle/oracle-database/12.2/comsc/sample-schemas.pdfGitHub最方便了。可以在线检出或下载zip文件~30M后续安装。在Oracle用户下cd ~ git clone https://github.com/oracle/db-sample-schemas.git目前Sample Schema最新的版本是23c如果要下载19c的命令如下gitclone--depth1--branchv19c https://github.com/oracle-samples/db-sample-schemas.git查看目录$ cd db-sample-schemas/ $ 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修改cd $HOME/db-sample-schemas perl -p -i.bak -e s#__SUB__CWD__#$(pwd)#g *.sql */*.sql */*.dat source /usr/local/bin/oraenv一次性安装所有示例Schema执行命令格式如下其中users和temp无需改变/your/path/to/log/指定为/tmp/即可尾部一定要有/例如/tmp是不行的实际上官方指定的目录为$ORACLE_HOME/demo/schema/log/。余下的参数就是系统用户sys和system的口令以及各shcema用户的口令sqlplus system/systempwconnect_string mksample systempw syspw hrpw oepw pmpw ixpw shpw bipw users temp /your/path/to/log/ connect_string实际命令如下假设SYSTEM用户口令为Welcome1将所有schem安装在orclpdb1中cd $HOME/db-sample-schemas sqlplus system/Welcome1orclpdb1 -- 假设各schema用户的口令是oracle mksample Welcome1 Welcome1 oracle oracle oracle oracle oracle oracle users temp $ORACLE_HOME/demo/schema/log/ orclpdb1 -- 假设各schema用户的口令是Welcome1 mksample Welcome1 Welcome1 Welcome1 Welcome1 Welcome1 Welcome1 Welcome1 Welcome1 users temp $ORACLE_HOME/demo/schema/log/ orclpdb1如果是OCI口令需要复杂些可以用下面这个mksample ABcd123_# ABcd123_# ABcd123_# ABcd123_# ABcd123_# ABcd123_# ABcd123_# ABcd123_# users temp $ORACLE_HOME/demo/schema/log/ orclpdb1一切顺利没有报错。所有的执行日志会放到$ORACLE_HOME/demo/schema/log/目录下。验证$ sqlplus hr/oracleorclpdb1 SQL*Plus: Release 12.2.0.1.0 Production on Fri May 31 08:52:27 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Fri May 31 2019 08:47:36 08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production安装SH Schema有2点注意一个是版本输入v3而非3另一个是connect string只输入服务名不要有用户名和口令。$cdsales_history $ rlwrap sqlplus sys/Welcome1orclpdb1 as sysdba SQL*Plus: Release19.0.0.0.0 - Production on Tue Feb2509:27:582020Version19.3.0.0.0 Copyright(c)1982,2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release19.0.0.0.0 - Production Version19.3.0.0.0 SQLsh_main specify passwordforSH as parameter1: Enter valuefor1: oracle specify default tablespaceforSH as parameter2: Enter valuefor2:usersspecify temporary tablespaceforSH as parameter3: Enter valuefor3: temp specify passwordforSYS as parameter4: Enter valuefor4: Welcome1 specify directory pathforthe data files as parameter5: Enter valuefor5: /home/oracle/db-sample-schemas/sales_history/ writeable directory pathforthe log files as parameter6: Enter valuefor6: /opt/oracle/product/19c/dbhome_1/demo/schema/log/ specify version as parameter7: Enter valuefor7: v3 specify connect string as parameter8: Enter valuefor8: orclpdb1安装HR Schema$ sqlplus sys/Welcome1orclpdb1 as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Mon May 27 16:13:00 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Mon May 27 2019 16:11:37 08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL hr_main specify password for HR as parameter 1: Enter value for 1: Welcome1 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 password for SYS as parameter 4: Enter value for 4: Welcome1 specify log path as parameter 5: Enter value for 5:输入回车 SP2-0137: DEFINE requires a value following equal sign specify connect string as parameter 6: Enter value for 6: orclpdb1 Enter value for log_path:输入回车 ... Comment created. Comment created. Comment created. Commit complete. PL/SQL procedure successfully completed. not spooling currently登入并检查[oracleoracle-12201-vagrant human_resources]$ sqlplus hr/oracleorclpdb1 SQL*Plus: Release 12.2.0.1.0 Production on Mon May 27 16:14:18 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Mon May 27 2019 16:13:41 08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL select * from regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa就有一点要说明输入connect string时说的是后面的部分。例如orclcdb或orclpdb1。安装OE SchemaOE Schema中的一些表会外键指向HR Schema中的表因此过程中需要输入hr用户的口令。两点需要注意要登入需安装schema的PDB而非CDB输入路径时需要以/结尾$cdorder_entry $ sqlplus sysorclpdb1 as sysdba SQLoe_main specify passwordforOE as parameter1: Enter valuefor1: Welcome1 specify default tablespeaceforOE as parameter2: Enter valuefor2:usersspecify temporary tablespaceforOE as parameter3: Enter valuefor3: temp specify passwordforHR as parameter4: Enter valuefor4: Welcome1 specify passwordforSYS as parameter5: Enter valuefor5: Welcome1 specify directory pathforthe data files as parameter6: Enter valuefor6: /home/oracle/db-sample-schemas/order_entry/ writeable directory pathforthe log files as parameter7: Enter valuefor7: /opt/oracle/product/19c/dbhome_1/demo/schema/log/ specify version as parameter8: Enter valuefor8: v3 specify connect string as parameter9: Enter valuefor9: orclpdb1安装SH Schema 注意第5和第6个参数输入目录时最后一定要以/结束。例如要输入/tmp/而非/tmp。数据目录就输.dat文件所在的绝对路径。$ cd ~/db-sample-schemas/sales_history/ $ sqlplus sys/Welcome1orclpdb1 as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed May 29 20:31:01 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Wed May 29 2019 20:28:32 08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL sh_main specify password for SH as parameter 1: Enter value for 1: oracle specify default tablespace for SH as parameter 2: Enter value for 2: users specify temporary tablespace for SH as parameter 3: Enter value for 3: temp specify password for SYS as parameter 4: Enter value for 4: Welcome1 specify directory path for the data files as parameter 5: Enter value for 5: /home/oracle/db-sample-schemas/sales_history/ writeable directory path for the log files as parameter 6: Enter value for 6:/tmp/ specify version as parameter 7: Enter value for 7: v3 specify connect string as parameter 8: Enter value for 8: orclpdb1删除所有Schema$cd$HOME/db-sample-schemas $ sqlplus systemorclpdb1 SQLdrop_sch.sql会提示你输入口令连接串和一个spool file。在我的环境中因此输入Welcome1, orclpdb1和/tmp/1.sql即可。参考https://oracle-base.com/articles/misc/install-sample-schemas