Oracle各种版本下“示例数据库的创建”的创建
Oracle各種版本下“示例數(shù)據(jù)庫的創(chuàng)建”的創(chuàng)建
1、插入示例方案 PDB ：使用 DBCA
在 12.1.0.2 中，可以使用 DBCA 圖形化界面來創(chuàng)建示例 PDB 。
?
您可以使用 DBCA 插入采用示例方案的新 PDB 。
1. 在 DBCA 中，單擊“ Manage Pluggable Databases （管理可插入數(shù)據(jù)庫）”。
2. 然后選擇“ Create a Pluggable Database （創(chuàng)建可插入數(shù)據(jù)庫）”。
3. 選擇您打算在其中創(chuàng)建新 PDB 的 CDB 。
4. 單擊“ Create Pluggable Database by using PDB File Set （使用 PDB 文件集創(chuàng)建可插入數(shù)據(jù)庫）”。瀏覽找到兩個文件：
- 可插入數(shù)據(jù)庫元數(shù)據(jù)文件：
$ORACLE_HOME/assistants/dbca/templates/sampleschema.xml
- 可插入數(shù)據(jù)庫數(shù)據(jù)文件備份：
$ORACLE_HOME/assistants/dbca/templates/sampleschema.dfb
5. 定義新 PDB 的名稱和數(shù)據(jù)文件的位置。您還可以定義 PDB 用戶，為 PDB 創(chuàng)建新管理員。
6. 單擊“ Next （下一步）”和“ Finish （完成）”。
?
注意， sampleschema.xml 和 sampleschema.df b 只在 12.1.0.2 中提供，高于該版本的數(shù)據(jù)庫不再提供這 2 個文件。
2、靜默創(chuàng)建示例 PDB
示例數(shù)據(jù)庫中包括了 IX 、 SH 、 BI 、 OE 、 HR 、 PM 、 SCOTT 這幾個用戶。當數(shù)據(jù)庫版本小于 12.2.0.1 時，一般放在 example 表空間中。從 Oracle 12.2 開始不再提供該示例表空間了，即使在創(chuàng)建語句中加上“ sampleSchema true ”也依然沒有 example 表空間，而會把相關數(shù)據(jù)放到 SYSAUX 表空間中。
若數(shù)據(jù)庫版本大于等于 12.2.0.1 時，在創(chuàng)建非 cdb 或包含至少一個 pdb 的 cdb 數(shù)據(jù)庫時，會創(chuàng)建示例數(shù)據(jù)庫，但是相關的示例數(shù)據(jù)位于 SYSAUX 表空間。若數(shù)據(jù)庫版本小于 12.2.0.1 時，則示例數(shù)據(jù)位于 EXAMPLE 表空間。 12c 版本的 CDB 數(shù)據(jù)庫不再包含示例數(shù)據(jù)庫和示例表空間。
12.1.0.2 版本：
?
18c 的版本：
靜默創(chuàng)建示例 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 中提供，高于該版本的數(shù)據(jù)庫不再提供這 2 個文件。但是，高版本的數(shù)據(jù)庫依然可以使用這 2 個文件來創(chuàng)建示例 PDB 。不過需要做以下操作：
1 、刪除 sampleschema.xml 文件中的行： <APEX>4.2.5.00.08:1</APEX>
2 、高版本創(chuàng)建完成后需要執(zhí)行升級腳本： $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;
?
?
官網(wǎng)： https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/creating-and-configuring-an-oracle-database.html#GUID-6EDDC43D-9BD6-4096-8192-7E548B826360
?
?
dbca -silent -createpluggabledatabase -h
dbca -createpluggabledatabase -help
一.1.1 ? CDB 和 PDB 中創(chuàng)建 HR 用戶
從 12.2 開始， $ORACLE_HOME/demo/schema/ 目錄下已經(jīng)沒有 mksample.sql 文件，默認只有一個 HR 用戶的創(chuàng)建腳本。因此，如果只需要安裝 HR 用戶，直接執(zhí)行默認的腳本 hr_main.sql 即可。在 12c 中需要在 pdb 中執(zhí)行腳本，因為在 CDB 中無法創(chuàng)建 HR 這種普通用戶，腳本會執(zhí)行失敗，當然也可以通過修改參數(shù) common_user_prefix 為空來在 CDB 中安裝配置 HR 用戶數(shù)據(jù)。
?
@?/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 用戶的創(chuàng)建在所有版本的數(shù)據(jù)庫中都沒有變化。
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 創(chuàng)建示例數(shù)據(jù)
10G 可能是為了提高安全性，在 dbca 創(chuàng)建數(shù)據(jù)庫是， custom 定制數(shù)據(jù)庫后，安裝示例方案的選項是灰色的，不能直接安裝。
可通過以下方式完成。登陸到 sqlplus ，執(zhí)行 $ORACLE_HOME/demo/schema/mkplug.sql
根據(jù)提示輸入新建示例用戶的密碼，
元數(shù)據(jù)文件位置： $ORACLE_HOME/assistants/dbca/templates/example.dmp
備份數(shù)據(jù)文件位置： $ORACLE_HOME/assistants/dbca/templates/example01.dfb
數(shù)據(jù)文件存放位置：自己指定
指定日志輸出路徑，執(zhí)行就 ok 了。
?
注意：還需要修改腳本 $ORACLE_HOME/demo/schema/mkplug.sql 才能導入，比較麻煩。
?
4 ? 示例數(shù)據(jù)庫通用創(chuàng)建方式
以上各種辦法，包括使用數(shù)據(jù)泵導出導入都比較麻煩，還涉及到版本的問題。接下來介紹的這種辦法可以在各種版本上執(zhí)行，已在 10g 到 18c 都測試通過。
原理： 使用純 SQL 腳本 +SQL*Loader 方式進行導入示例數(shù)據(jù)庫。
?
介紹：
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?? 刪除示例數(shù)據(jù)庫
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.gitor 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? 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_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/noncdblocalhost: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.
Oracle 12c 提供的樣例Scheme和數(shù)據(jù)庫對象創(chuàng)建腳本
? ? ? ?在Oracle10g中,如果我們在創(chuàng)建實例時沒有選擇安裝示例，那么在創(chuàng)建實例之后，還可以通過$ORACLE_HOME/demo/schema 下的腳本來創(chuàng)建。關于具體的創(chuàng)建方法，官方文檔有詳細的說明：
?????? SampleSchema Scripts and Object Descriptions
??????? http://download.oracle.com/docs/cd/B19306_01/server.102/b14198/scripts.htm#sthref77
?
在11gR1里也有相關的創(chuàng)建腳本：
??????? http://download.oracle.com/docs/cd/B28359_01/server.111/b28328/installation.htm#COMSC00002
?
?????? 這些腳本會創(chuàng)建包括HR,OE等在內的一系列示例用戶。這些用戶數(shù)據(jù)可以讓我們簡單地進行一些功能測試，并且Oracle很多文檔的示例也是使用這些用戶的。
?
?????? 但是在11gR2中我們在$ORACLE_HOME/demo/schema/目錄下已然找不到mksample.sql文件，并且每個單獨的子目錄中也不再有創(chuàng)建示例用戶的SQL腳本。
?????? 實際上，Oracle將這些示例用戶的安裝獨立到了一個安裝盤中，比如對于Linuxx86-64的安裝文件可以從OTN上直接下載：
??????? http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linx8664soft-100572.html
?
?????? 其中Oracle Database 11g Release 2Examples就是我們需要的示例用戶安裝文件。但是現(xiàn)在這份安裝文件有500多M，如果你并不期望下載這么大的內容，或者說你僅僅需要HR和OE用戶，那么可以直接從以下URL中下載現(xiàn)成的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都是使用簡單的數(shù)據(jù)庫用戶SCOTT進行文檔和培訓的各種示例。該用戶里主要有2個表： EMP和DEPT，這些表不足以顯示Oracle數(shù)據(jù)庫和其他Oracle產(chǎn)品的基本特性。
示例數(shù)據(jù)庫模式為Oracle數(shù)據(jù)庫的每個版本提供了一個通用的平臺。在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/目錄下已經(jīng)沒有mksample.sql文件，默認只有一個HR用戶的創(chuàng)建腳本：
[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]$如果想安裝完整的示例數(shù)據(jù)庫，可以從github上下載：
https://github.com/oracle/db-sample-schemas/releases/latest
2 安裝默認的HR用戶
如果只需要安裝HR用戶，直接執(zhí)行默認的腳本即可，示例如下：
在12c中需要在pdb中執(zhí)行腳本，因為在CDB中無法創(chuàng)建hr這種普通用戶，腳本會執(zhí)行失敗：?
3 安裝完整的示例用戶
從github上下載源代碼：
https://github.com/oracle/db-sample-schemas/releases/latest
上傳到數(shù)據(jù)庫服務器：
[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如果執(zhí)行執(zhí)行會報如下錯誤，這里是__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 ..
路勁已經(jīng)替換成絕對路徑了。 然后在執(zhí)行：
[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用戶及其數(shù)據(jù)庫對象；HR用戶及其數(shù)據(jù)庫對象。在Oracle 12c中，安裝完Oracle 12c 數(shù)據(jù)庫后，需要另外獨立安裝Oracle提供的樣例數(shù)據(jù)庫對象。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 官方文檔這樣描述：
示例數(shù)據(jù)庫架構為Oracle數(shù)據(jù)庫的每個版本中的示例提供了一個通用的平臺。樣本模式是一組相互關聯(lián)的數(shù)據(jù)庫模式。這個集合提供了復雜性的方法：
HR：此模式的擴展支持Oracle Internet目錄演示。?
OE：對于處理中間復雜度問題是有用的。在該模式中可以使用許多數(shù)據(jù)類型，包括非標量數(shù)據(jù)類型。?
OC：是在模式OE內構建的對象關系數(shù)據(jù)庫對象的集合。?
PM：專用于多媒體數(shù)據(jù)類型。?
IX：Information Exchange(IX)下收集的一組模式可用于演示Oracle高級隊列功能。?
SH：銷售歷史（SH）被設計為允許大量數(shù)據(jù)的演示。對該模式的擴展為高級分析處理提供支持。
在學習安裝Oracle 12c 的Sample Scheme時需要注意的問題
首先下載壓縮文件：db-sample-schemas-12.2.0.1.zip
解壓后獲得目錄：db-sample-schemas-12.2.0.1
閱讀Readme文件
修改一些文件內容中的目錄
執(zhí)行腳本文件
? ? 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個參數(shù)的值，包括：
SYSTEM用戶的密碼，SYS用戶的密碼，HR用戶密碼，OE用戶密碼，PM用戶密碼，IX用戶密碼，SH用戶密碼，BI用戶密碼，需要使用的表空間的名稱，臨時表空間的名稱，日志文件目錄，以及連接字符串（格式：localhost:1521/pdb）
其中，連接字符串中pdb是你的Pluggable 數(shù)據(jù)庫的服務名，例如：
@mksample?lhr?lhr?HR?OE?PM?IX?SH?BI?EXAMPLE?TEMP?$ORACLE_HOME/demo/schema/log/?localhost:1521/PDBLHR1
About Me
| ........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網(wǎng)絡，若有侵權請聯(lián)系小麥苗刪除 ● 本文在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/ ● 數(shù)據(jù)庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群號： 230161599 （滿） 、618766405 ● weixin群：可加我weixin，我拉大家進群，非誠勿擾 ● 聯(lián)系我請加QQ好友 （ 646634621 ） ，注明添加緣由 ● 于 2018-12-01 06:00 ~ 2018-12-31 24:00 在魔都完成 ● 最新修改時間：2018-12-01 06:00 ~ 2018-12-31 24:00 ● 文章內容來源于小麥苗的學習筆記，部分整理自網(wǎng)絡，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店 ： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麥苗出版的數(shù)據(jù)庫類叢書 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網(wǎng)絡班 ： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁 ： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 weixin客戶端 掃描下面的二維碼來關注小麥苗的weixin公眾號（ xiaomaimiaolhr ）及QQ群（DBA寶典）、添加小麥苗weixin， 學習最實用的數(shù)據(jù)庫技術。
........................................................................................................................ |
?
?
來自 “ ITPUB博客 ” ，鏈接：http://blog.itpub.net/26736162/viewspace-2222411/，如需轉載，請注明出處，否則將追究法律責任。
總結
以上是生活随笔為你收集整理的Oracle各种版本下“示例数据库的创建”的创建的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： speedoffice文档里面如何设置页
- 下一篇： Python将AVI视频抽帧成图片保存