Oracle各种版本下“示例数据库的创建”的创建
1、插入示例方案 PDB ：使用 DBCA
在 12.1.0.2 中，可以使用 DBCA 图形化界面来创建示例 PDB 。
您可以使用 DBCA 插入采用示例方案的新 PDB 。
1. 在 DBCA 中，单击“ Manage Pluggable Databases （管理可插入数据库）”。
2. 然后选择“ Create a Pluggable Database （创建可插入数据库）”。
3. 选择您打算在其中创建新 PDB 的 CDB 。
4. 单击“ Create Pluggable Database by using PDB File Set （使用 PDB 文件集创建可插入数据库）”。浏览找到两个文件：
- 可插入数据库元数据文件：
$ORACLE_HOME/assistants/dbca/templates/sampleschema.xml
- 可插入数据库数据文件备份：
$ORACLE_HOME/assistants/dbca/templates/sampleschema.dfb
5. 定义新 PDB 的名称和数据文件的位置。您还可以定义 PDB 用户，为 PDB 创建新管理员。
6. 单击“ Next （下一步）”和“ Finish （完成）”。
注意， sampleschema.xml 和 sampleschema.df b 只在 12.1.0.2 中提供，高于该版本的数据库不再提供这 2 个文件。
2、静默创建示例 PDB
示例数据库中包括了 IX 、 SH 、 BI 、 OE 、 HR 、 PM 、 SCOTT 这几个用户。当数据库版本小于 12.2.0.1 时，一般放在 example 表空间中。从 Oracle 12.2 开始不再提供该示例表空间了，即使在创建语句中加上“ sampleSchema true ”也依然没有 example 表空间，而会把相关数据放到 SYSAUX 表空间中。
若数据库版本大于等于 12.2.0.1 时，在创建非 cdb 或包含至少一个 pdb 的 cdb 数据库时，会创建示例数据库，但是相关的示例数据位于 SYSAUX 表空间。若数据库版本小于 12.2.0.1 时，则示例数据位于 EXAMPLE 表空间。 12c 版本的 CDB 数据库不再包含示例数据库和示例表空间。
12.1.0.2 版本：
18c 的版本：
静默创建示例 PDB ：
dbca -silent -createpluggabledatabase -sourceDB cdb1212 -pdbName pdbsample -createAsClone True -createPDBFrom RMANBACKUP -pdbBackUpfile '/u06/app/oracle/product/12.1.0/dbhome_1/assistants/dbca/templates/sampleschema.dfb' -pdbMetadataFile '/u06/app/oracle/product/12.1.0/dbhome_1/assistants/dbca/templates/sampleschema.xml' -pdbAdminPassword lhr
注意： sampleschema.xml 和 sampleschema.df b 只在 12.1.0.2 中提供，高于该版本的数据库不再提供这 2 个文件。但是，高版本的数据库依然可以使用这 2 个文件来创建示例 PDB 。不过需要做以下操作：
1 、删除 sampleschema.xml 文件中的行： <APEX>4.2.5.00.08:1</APEX>
2 、高版本创建完成后需要执行升级脚本： $ORACLE_HOME/bin/dbupgrade -c PDBSAMPLE
3 、删除 pdb 中的 APEX 组件，过程如下：
alter session set container=pdbsample;
SQL>@?/apex/apxremov_nocdb.sql
4 、修改 pdb 的字符集（原字符集默认为 US7ASCII ，新字符集必须是原字符集的超集），过程如下：
alter session set container=pdbsample;
select userenv('language') from dual;
alter pluggable database pdbsample close immediate;
alter pluggable database pdbsample open restricted;
alter database character set INTERNAL_CONVERT AL32UTF8 ;
alter pluggable database pdbsample close immediate;
alter pluggable database pdbsample open;
或：
alter system enable restricted session;
alter database character set INTERNAL_CONVERT ZHS16GBK ;
alter system DISABLE restricted session;
校验：
select count(1) from CDB_tables where owner='HR';
select count(1) from scott.emp;
dbca -silent -createpluggabledatabase -h
dbca -createpluggabledatabase -help
一.1.1 CDB 和 PDB 中创建 HR 用户
从 12.2 开始， $ORACLE_HOME/demo/schema/ 目录下已经没有 mksample.sql 文件，默认只有一个 HR 用户的创建脚本。因此，如果只需要安装 HR 用户，直接执行默认的脚本 hr_main.sql 即可。在 12c 中需要在 pdb 中执行脚本，因为在 CDB 中无法创建 HR 这种普通用户，脚本会执行失败，当然也可以通过修改参数 common_user_prefix 为空来在 CDB 中安装配置 HR 用户数据。
@?/demo/schema/human_resources/hr_main.sql HR SYSAUX TEMP /tmp
SYS@CDBLHR> @?/demo/schema/human_resources/hr_main.sql HR SYSAUX TEMP /tmp
specify password for HR as parameter 1:
specify default tablespeace for HR as parameter 2:
specify temporary tablespace for HR as parameter 3:
specify l og path as parameter 4:
......
SYS@CDBLHR> select count(1) from dba_tables where owner='HR';
COUNT(1)
----------
7
一.1.2 CDB 和 PDB 中重建 scott 用户
scott 用户的创建在所有版本的数据库中都没有变化。
sqlplus / as sysdba
SQL>@$ORACLE_HOME/rdbms/admin/utlsampl.sql
12c 中：
DROP USER SCOTT CASCADE;
DROP USER ADAMS CASCADE;
DROP USER JONES CASCADE;
DROP USER CLARK CASCADE;
DROP USER BLAKE CASCADE;
DROP PUBLIC SYNONYM PARTS;
CREATE USER SCOTT IDENTIFIED BY TIGER;
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT ;
--GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER CONTAINER=ALL ;
CONNECT SCOTT/tiger
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
CREATE TABLE BONUS
(
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9) ,
SAL NUMBER,
COMM NUMBER
) ;
CREATE TABLE SALGRADE
( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;
SYS@CDBLHR> GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER
*
ERROR at line 1:
ORA-65049: Creation of local user or role is not allowed in this container.
SYS@CDBLHR> show parameter common
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix string
SYS@CDBLHR> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@rhel6lhr env_oracle]$ oerr ora 65049
65049, 00000, "Creation of local user or role is not allowed in this container."
// *Cause: An attempt was made to create a local user or role in CDB$ROOT or
// an application root.
// *Action: If trying to create a common user or role, specify CONTAINER=ALL.
//
[oracle@rhel6lhr env_oracle]$ sas
SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 3 10:29:14 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SYS@CDBLHR> GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER CONTAINER=ALL ;
Grant succeeded.
3 10g 和 11g 创建示例数据
10G 可能是为了提高安全性，在 dbca 创建数据库是， custom 定制数据库后，安装示例方案的选项是灰色的，不能直接安装。
可通过以下方式完成。登陆到 sqlplus ，执行 $ORACLE_HOME/demo/schema/mkplug.sql
根据提示输入新建示例用户的密码，
元数据文件位置： $ORACLE_HOME/assistants/dbca/templates/example.dmp
备份数据文件位置： $ORACLE_HOME/assistants/dbca/templates/example01.dfb
数据文件存放位置：自己指定
指定日志输出路径，执行就 ok 了。
注意：还需要修改脚本 $ORACLE_HOME/demo/schema/mkplug.sql 才能导入，比较麻烦。
4 示例数据库通用创建方式
以上各种办法，包括使用数据泵导出导入都比较麻烦，还涉及到版本的问题。接下来介绍的这种办法可以在各种版本上执行，已在 10g 到 18c 都测试通过。
原理： 使用纯 SQL 脚本 +SQL*Loader 方式进行导入示例数据库。
介绍：
https://github.com/oracle/db-sample-schemas
下载地址： https://github.com/oracle/db-sample-schemas/releases/latest
将压缩包 db-sample-schemas-12.2.0.1.zip 放在 /soft 目录下。
1 、 unzip db-sample-schemas-12.2.0.1.zip
2 、 cd db-sample-schemas-12.2.0.1
3 、 perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat
或 #sed -i "s#__SUB__CWD__#$(pwd)#g" `grep __SUB__CWD__ -rl --include="*.sql" ./`
create pluggable database PDBSAMPLE admin user lhr identified by lhr CREATE_FILE_DEST = '/u01/app/oracle/oradata';
alter pluggable database PDBSAMPLE open;
alter session set container=PDBLHR1;
CREATE TABLESPACE example
NOLOGGING
DATAFILE '/u04/oradata/CDBLHR/PDBLHR1/example01.dbf' SIZE 100M REUSE
AUTOEXTEND ON NEXT 640k
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
-- sqlplus sys/lhr@localhost:1521/PDBSAMPLE as sysdba
-- sqlplus system/lhr@localhost:1521/PDBSAMPLE
@/soft/db-sample-schemas-12.2.0.1/mksample.sql lhr lhr HR OE PM IX SH BI EXAMPLE TEMP /tmp/ localhost:1521/PDBSAMPLE
select con_id,tablespace_name,count(1) from cdb_tables where owner in ('IX','SH','BI','OE','HR','PM') group by con_id,tablespace_name;
select con_id,owner,tablespace_name,count(1) from cdb_tables where owner in ('IX','SH','BI','OE','HR','PM') group by con_id,owner,tablespace_name;
select count(1) from dba_tables where owner in ('IX','SH','BI','OE','HR','PM');
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:
4 删除示例数据库
sqlplus system/systempw@connect_string
@/soft/db-sample-schemas-12.2.0.1/drop_sch.sql
Oracle Database Sample Schemas
Copyright (c) 2016 Oracle
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
1. Introduction
This repository contains a copy of the Oracle Database sample schemas that are installed with Oracle Database Enterprise Edition 12c. These schemas are used in Oracle documentation to show SQL language concepts. The schemas themselves are documented in Oracle Database Sample Schemas, 12c Release 1 (12.1) .
The schemas are:
-
HR: Human Resources
-
OE: Order Entry
-
PM: Product Media
-
IX: Information Exchange
-
SH: Sales History
-
BI: Business Intelligence
Due to widespread dependence on these scripts in their current form, no pull requests for changes can be accepted.
2. Installing the Samples
CAUTION : Do not install the samples if you already have user accounts named HR, OE, PM, IX, SH or BI.
The installation scripts are designed to run on a database host with Oracle Database 12.1. Privileged database access is required during installation.
The instructions below work on Linux and similar operating systems. Adjust them for other platforms.
An alternative to using this repository is to download and install the Oracle Database 12c Release 1 Examples package for your platform.
2.1. Clone this repository
Login as the Oracle Database software owner and clone the repository, for example
cd $HOMEgit clone https://github.com/oracle/db-sample-schemas.git
or download and extract the ZIP file:
unzip db-sample-schemas.zip
The schema directory should be owned by the Oracle Database software owner.
2.2. Change directory
cd $HOME/db-sample-schemas
2.3. Change all embedded paths to match your working directory
The installation scripts need your current directory embedded in various locations. Use a text editor or the following Perl script to make the changes, replacing occurrences of the token __SUB__CWD__ with your current working directory, for example /home/oracle/db-sample-schemas
perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat
2.4. Set the Oracle environment
source /usr/local/bin/oraenv
Note : Oracle's sqlldr utility needs to be in $PATH for correct loading of the Product Media (PM) and Sales History (SH) schemas.
2.5. Run the installation script
Review the README.txt for information on passwords and pre-requirements. In particular, verify your default and temporary tablespace names, and choose a password for each schema.
Start SQL*Plus and run the top level installation script as discussed in README.txt :
sqlplus system/systempw@connect_string @mksample systempw syspw hrpw oepw pmpw ixpw shpw bipw users temp /your/path/to/log/ connect_string
Note : Use an absolute path and also append a trailing slash to the log directory name.
Use your current SYSTEM and SYS passwords, and also your actual default and temporary tablespace names. The passwords for the new HR, OE, PM, IX, SH and BI users will be set to the values you specify.
Using a connect string permits connections to non-container databases and pluggable database using the same syntax.
An example of connect strings for databases with services noncdb and pdb:
localhost:1521/noncdb localhost:1521/pdb
2.6. Review the installation logs
Review output in your log directory for errors.
3. Removing the Samples
CAUTION : This will drop user accounts named HR, OE, PM, IX, SH and BI.
3.1. Set the Oracle environment
source /usr/local/bin/oraenv
3.2. Run the schema removal script
sqlplus system/systempw@connect_string @drop_sch.sql
When prompted, enter the SYSTEM password, a log file name, and connect string.
Oracle 12c 提供的样例Scheme和数据库对象创建脚本
在Oracle10g中,如果我们在创建实例时没有选择安装示例，那么在创建实例之后，还可以通过$ORACLE_HOME/demo/schema 下的脚本来创建。关于具体的创建方法，官方文档有详细的说明：
SampleSchema Scripts and Object Descriptions
http://download.oracle.com/docs/cd/B19306_01/server.102/b14198/scripts.htm#sthref77
在11gR1里也有相关的创建脚本：
http://download.oracle.com/docs/cd/B28359_01/server.111/b28328/installation.htm#COMSC00002
这些脚本会创建包括HR,OE等在内的一系列示例用户。这些用户数据可以让我们简单地进行一些功能测试，并且Oracle很多文档的示例也是使用这些用户的。
但是在11gR2中我们在$ORACLE_HOME/demo/schema/目录下已然找不到mksample.sql文件，并且每个单独的子目录中也不再有创建示例用户的SQL脚本。
实际上，Oracle将这些示例用户的安装独立到了一个安装盘中，比如对于Linuxx86-64的安装文件可以从OTN上直接下载：
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linx8664soft-100572.html
其中Oracle Database 11g Release 2Examples就是我们需要的示例用户安装文件。但是现在这份安装文件有500多M，如果你并不期望下载这么大的内容，或者说你仅仅需要HR和OE用户，那么可以直接从以下URL中下载现成的SQL脚本，解压以后运行即可。
http://st-curriculum.oracle.com/obe/jdev/obe11jdev/11/common/files/sample_schema_scripts.zip
以HR用户为例：
[oracle@dave.cndba.cn human_resources]$ ls
hr_cre.sql hr_main.sql hr_popul.sql
[oracle@dave.cndba.cn human_resources]$ sqlplus / as sysdba
SQL> @hr_main.sql
1 背景说明
Oracle 11gR2中示例用户的安装说明参考如下链接：
Oracle 11gR2 中 示例用户 安装说明
http://www.cndba.cn/dave/article/1133
Oracle 12cR2的示例用户说明可以参考官方手册：
Introduction to Sample Schemas
http://docs.oracle.com/database/122/COMSC/introduction-to-sample-schemas.htm#COMSC005
多年来，Oracle都是使用简单的数据库用户SCOTT进行文档和培训的各种示例。该用户里主要有2个表： EMP和DEPT，这些表不足以显示Oracle数据库和其他Oracle产品的基本特性。
示例数据库模式为Oracle数据库的每个版本提供了一个通用的平台。在Oracle 12cR2中，提供了如下测试用户：
Schema HR – Division Human Resources tracks information about the company employees and facilities. Schema OE – Division Order Entry tracks product inventories and sales of company products through various channels. Schema PM – Division Product Media maintains descriptions and detailed information about each product sold by the company. Schema IX – Division Information Exchange manages shipping through B2B applications. Schema SH – Division Sales tracks business statistics to facilitate business decisions.
但是从11gR2开始，$ORACLE_HOME/demo/schema/目录下已经没有mksample.sql文件，默认只有一个HR用户的创建脚本：
[oracle@dave.cndba.cn schema]$ pwd /u01/app/oracle/product/12.2.0/dbhome_1/demo/schema [oracle@dave.cndba.cn schema]$ ls drop_sch.sql human_resources log mk_dir.sql mk_dir.sql.sbs mkplug.sql sted_mkplug.sql.dbl [oracle@dave.cndba.cn schema]$ cd human_resources/ [oracle@dave.cndba.cn human_resources]$ ls hr_analz.sql hr_code.sql hr_comnt.sql hr_cre.sql hr_drop_new.sql hr_drop.sql hr_idx.sql hr_main_new.sql hr_main.sql hr_popul.sql [oracle@dave.cndba.cn human_resources]$
如果想安装完整的示例数据库，可以从github上下载：
https://github.com/oracle/db-sample-schemas/releases/latest
2 安装默认的HR用户
如果只需要安装HR用户，直接执行默认的脚本即可，示例如下：
在12c中需要在pdb中执行脚本，因为在CDB中无法创建hr这种普通用户，脚本会执行失败：
CREATE USER hr IDENTIFIED BY oracle * ERROR at line 1: ORA-65096: invalid common user or role name [oracle@dave.cndba.cn admin]$ sqlplus system/oracle@dave SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 12 20:02:55 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Mon Apr 17 2017 05:38:02 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> @?/demo/schema/human_resources/hr_main.sql specify password for HR as parameter 1: Enter value for 1: oracle 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 PL/SQL procedure successfully completed. User created. User altered. User altered. Grant succeeded. Grant succeeded. Session altered. Session altered. Session altered. ****** Creating REGIONS table .... Table created. Index created. Table altered. ****** Creating COUNTRIES table .... Table created. Table altered. ****** Creating LOCATIONS table .... Table created. Index created. Table altered. Sequence created. ****** Creating DEPARTMENTS table .... Table created. Index created. Table altered. Sequence created. ****** Creating JOBS table .... Table created. Index created. Table altered. ****** Creating EMPLOYEES table .... Table created. Index created. Table altered. Table altered. Sequence created. ****** Creating JOB_HISTORY table .... Table created. Index created. Table altered. ****** Creating EMP_DETAILS_VIEW view ... View created. Commit complete. Session altered. ****** Populating REGIONS table .... 1 row created. 1 row created. 1 row created. 1 row created. ****** Populating COUNTIRES table .... 1 row created. 1 row created. ...... 1 row created. ****** Populating LOCATIONS table .... 1 row created. ...... 1 row created. ****** Populating DEPARTMENTS table .... Table altered. 1 row created. ...... 1 row created. ****** Populating JOBS table .... 1 row created. ...... 1 row created. ****** Populating EMPLOYEES table .... 1 row created. ...... 1 row created. ****** Populating JOB_HISTORY table .... 1 row created. ...... Commit complete. PL/SQL procedure successfully completed. SQL>
3 安装完整的示例用户
从github上下载源代码：
https://github.com/oracle/db-sample-schemas/releases/latest
上传到数据库服务器：
[oracle@dave.cndba.cn demo]$ cp /home/oracle/db-sample-schemas-12.2.0.1.zip $ORACLE_HOME/demo [oracle@dave.cndba.cn demo]$ ls db-sample-schemas-12.2.0.1.zip schema [oracle@dave.cndba.cn demo]$ unzip db-sample-schemas-12.2.0.1.zip [oracle@dave.cndba.cn demo]$ mv schema schema.bak [oracle@dave.cndba.cn demo]$ mv db-sample-schemas-12.2.0.1 schema
具体说明可以参考Readme文件。
安装语法：
SQL> @?/demo/schema/mksample <SYSTEM_password> <SYS_password> <HR_password> <OE_password> <PM_password> <IX_password> <SH_password> <BI_password> EXAMPLE TEMP $ORACLE_HOME/demo/schema/log/ localhost:1521/pdb
使用命令：
SQL> @mksample oracle oracle oracle oracle oracle oracle oracle oracle dave temp '/tmp/log/' dave
如果执行执行会报如下错误，这里是__SUB__CWD__路径不对：
SP2-0310: unable to open file "__SUB__CWD__/human_resources/hr_cre.sql" SP2-0310: unable to open file "__SUB__CWD__/human_resources/hr_popul.sql" SP2-0310: unable to open file "__SUB__CWD__/human_resources/hr_idx.sql" SP2-0310: unable to open file "__SUB__CWD__/human_resources/hr_code.sql" SP2-0310: unable to open file "__SUB__CWD__/human_resources/hr_comnt.sql" SP2-0310: unable to open file "__SUB__CWD__/human_resources/hr_analz.sql" not spooling currently
上面的错误显示不能打开__SUB__CWD__/目录下的相关脚本文件，查看sql脚本文件：
@__SUB__CWD__/order_entry/oe_main.sql &&password_oe &&default_ts &&temp_ts &&password_hr &&password_sys __SUB__CWD__/order_entry/ &&logfile_dir &vrs &&connect_string
这里我们需要将__SUB__CWD__/替换成脚本的绝对路径即可。
[oracle@dave.cndba.cn schema]$ sed -i "s#__SUB__CWD__#$(pwd)#g" `grep __SUB__CWD__ -rl --include="*.sql" ./` [oracle@dave.cndba.cn schema]$ 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 [oracle@dave.cndba.cn schema]$ cat mksample.sql … @/u01/app/oracle/product/12.1.0/db_1/demo/schema/order_entry/oe_main.sql &&password_oe &&default_ts &&temp_ts &&password_hr &&password_sys /u01/app/oracle/product/12.1.0/db_1/demo/schema/order_entry/ &&logfile_dir &vrs &&connect_string ..
路劲已经替换成绝对路径了。 然后在执行：
[oracle@dave.cndba.cn schema]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 13 13:37:06 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> @?/demo/schema/mksample oracle oracle oracle oracle oracle oracle oracle oracle dave temp '/tmp/log/' dave 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 ... mkdir: cannot create directory ‘/tmp/log/’: File exists …… Table cardinality relational and object tables OWNER TABLE_NAME NUM_ROWS ------ ------------------------------ ---------- HR COUNTRIES 25 HR DEPARTMENTS 27 HR EMPLOYEES 107 HR JOBS 19 HR JOB_HISTORY 10 HR LOCATIONS 23 HR REGIONS 4 IX AQ$_ORDERS_QUEUETABLE_G 0 IX AQ$_ORDERS_QUEUETABLE_H 2 IX AQ$_ORDERS_QUEUETABLE_I 2 IX AQ$_ORDERS_QUEUETABLE_L 2 IX AQ$_ORDERS_QUEUETABLE_S 4 IX AQ$_ORDERS_QUEUETABLE_T 0 IX AQ$_STREAMS_QUEUE_TABLE_C 0 IX AQ$_STREAMS_QUEUE_TABLE_G 0 IX AQ$_STREAMS_QUEUE_TABLE_H 0 IX AQ$_STREAMS_QUEUE_TABLE_I 0 IX AQ$_STREAMS_QUEUE_TABLE_L 0 IX AQ$_STREAMS_QUEUE_TABLE_S 1 IX AQ$_STREAMS_QUEUE_TABLE_T 0 IX ORDERS_QUEUETABLE IX STREAMS_QUEUE_TABLE IX SYS_IOT_OVER_75148 0 IX SYS_IOT_OVER_75177 0 OE ACTION_TABLE 132 OE CATEGORIES_TAB 22 OE CUSTOMERS 319 OE INVENTORIES 1112 OE LINEITEM_TABLE 2232 OE ORDERS 105 OE ORDER_ITEMS 665 OE PRODUCT_DESCRIPTIONS 8640 OE PRODUCT_INFORMATION 288 OE PRODUCT_REF_LIST_NESTEDTAB 288 OE PROMOTIONS 2 OE PURCHASEORDER 132 OE SUBCATEGORY_REF_LIST_NESTEDTAB 21 OE WAREHOUSES 9 PM ONLINE_MEDIA 9 PM PRINT_MEDIA 4 PM TEXTDOCS_NESTEDTAB 12 SH CAL_MONTH_SALES_MV 48 SH CHANNELS 5 SH COSTS 82112 SH COUNTRIES 23 SH CUSTOMERS 55500 SH DR$SUP_TEXT_IDX$I SH DR$SUP_TEXT_IDX$K SH DR$SUP_TEXT_IDX$N SH DR$SUP_TEXT_IDX$R SH DR$SUP_TEXT_IDX$U SH FWEEK_PSCAT_SALES_MV 11266 SH PRODUCTS 72 SH PROMOTIONS 503 SH SALES 918843 SH SALES_TRANSACTIONS_EXT 916039 SH SUPPLEMENTARY_DEMOGRAPHICS 4500 SH TIMES 1826 58 rows selected.
十几年以来，从Oracle Database 9i到10g，11g，再到12c，每一次伴随着Oracle版本的升级更新，在了解和学习Oracle Database的新特性时，都少不了使用的Oracle Database本身提供的SCOTT用户及其数据库对象；HR用户及其数据库对象。在Oracle 12c中，安装完Oracle 12c 数据库后，需要另外独立安装Oracle提供的样例数据库对象。Oracle官方也提供了专门的文档，可以参考链接：https://docs.oracle.com/en/database/oracle/oracle-database/12.2/comsc/introduction-to-sample-schemas.html#GUID-844E92D8-A4C8-4522-8AF5-761D4BE99200
从Github上获取安装文件：Oracle Database 12.2.0.1 Sample Schemas
https://github.com/oracle/db-sample-schemas/releases/tag/v12.2.0.1
Oracle 12c 官方文档这样描述：
示例数据库架构为Oracle数据库的每个版本中的示例提供了一个通用的平台。样本模式是一组相互关联的数据库模式。这个集合提供了复杂性的方法：
HR：此模式的扩展支持Oracle Internet目录演示。
OE：对于处理中间复杂度问题是有用的。在该模式中可以使用许多数据类型，包括非标量数据类型。
OC：是在模式OE内构建的对象关系数据库对象的集合。
PM：专用于多媒体数据类型。
IX：Information Exchange(IX)下收集的一组模式可用于演示Oracle高级队列功能。
SH：销售历史（SH）被设计为允许大量数据的演示。对该模式的扩展为高级分析处理提供支持。
在学习安装Oracle 12c 的Sample Scheme时需要注意的问题
-
首先下载压缩文件：db-sample-schemas-12.2.0.1.zip
-
解压后获得目录：db-sample-schemas-12.2.0.1
-
阅读Readme文件
-
修改一些文件内容中的目录
-
执行脚本文件
SQL> @?/demo/schema/mksample <SYSTEM_password> <SYS_password>
<HR_password> <OE_password> <PM_password> <IX_password>
<SH_password> <BI_password> EXAMPLE TEMP
$ORACLE_HOME/demo/schema/log/ localhost:1521/pdb
这里需要提供11个参数的值，包括：
SYSTEM用户的密码，SYS用户的密码，HR用户密码，OE用户密码，PM用户密码，IX用户密码，SH用户密码，BI用户密码，需要使用的表空间的名称，临时表空间的名称，日志文件目录，以及连接字符串（格式：localhost:1521/pdb）
其中，连接字符串中pdb是你的Pluggable 数据库的服务名，例如：
@mksample lhr lhr HR OE PM IX SH BI EXAMPLE TEMP $ORACLE_HOME/demo/schema/log/ localhost:1521/PDBLHR1
About Me
| ........................................................................................................................ ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub（ http://blog.itpub.net/26736162 ）、博客园（ http://www.cnblogs.com/lhrbest ）和个人weixin公众号（ xiaomaimiaolhr ）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文博客园地址： http://www.cnblogs.com/lhrbest ● 本文pdf版、个人简介及小麦苗云盘地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群号： 230161599 （满） 、618766405 ● weixin群：可加我weixin，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友 （ 646634621 ） ，注明添加缘由 ● 于 2018-12-01 06:00 ~ 2018-12-31 24:00 在魔都完成 ● 最新修改时间：2018-12-01 06:00 ~ 2018-12-31 24:00 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ........................................................................................................................ ● 小麦苗的微店 ： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麦苗OCP、OCM、高可用网络班 ： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麦苗腾讯课堂主页 ： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 weixin客户端 扫描下面的二维码来关注小麦苗的weixin公众号（ xiaomaimiaolhr ）及QQ群（DBA宝典）、添加小麦苗weixin， 学习最实用的数据库技术。 ........................................................................................................................ |
 | |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2222411/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26736162/viewspace-2222411/
906
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
