重建scott用户及数据
---------- 重建scott用户
sqlplus / as sysdba
SQL>@?/rdbms/admin/utlsampl.sql
重建hr用户及数据
--- 重建hr用户
@?/demo/schema/human_resources/hr_main.sql hr SYSAUX TEMP /tmp
插入示例方案 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 个文件。
静默创建示例 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 文件中的行： 4.2.5.00.08:1
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
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
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.
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 才能导入，比较麻烦。
示例数据库通用创建方式
以上各种办法，包括使用数据泵导出导入都比较麻烦，还涉及到版本的问题。接下来介绍的这种办法可以在各种版本上执行，已在 10g 到 18c 都测试通过。
原理： 使用纯 SQL 脚本 +SQL*Loader 方式进行导入示例数据库。
介绍：
下载地址：
将压缩包 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:
删除示例数据库
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 .
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 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 or download and extract the ZIP file:
unzip db-sample-schemas.zipThe schema directory should be owned by the Oracle Database software owner.
2.2. Change directory
cd $HOME/db-sample-schemas2.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 */*.dat2.4. Set the Oracle environment
source /usr/local/bin/oraenvNote : 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 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 :
sqlplus system/systempw@connect_string
@mksample systempw syspw hrpw oepw pmpw ixpw shpw bipw users temp /your/path/to/log/ connect_stringNote : 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/pdb2.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/oraenv3.2. Run the schema removal script
sqlplus system/systempw@connect_string
@drop_sch.sqlWhen prompted, enter the SYSTEM password, a log file name, and connect string.
