首先,Oracle12c的多租户(multitenant)结构使得启用HR Schema不如之前的版本那么简单,下面是其中HR Schema的步骤。
1 在sqlplus
中登录sys
sqlplus sys/password as sysdba;
2 HR Schema在Oracle12c的可插拔(Pluggable)数据库中存放,为了启用HR Schema,我们需要知道可插拔数据库的名字,在Oracle 12c中,如果我们没有特别设定服务,容器或者可插拔数据库,Oracle会连接到默认的根容器数据库,它的名字固定为CBD$ROOT
,我们可以通过下面的命令查看连接名参数(con_name)
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
想要知道是否有可用的可插拔数据库,我们可以如下操作
SQL> select name, con_id from v$pdbs;
NAME CON_ID
------------------------------------------ --------------
PDB$SEED 2
ORCLPDB 3
这两个数据库的解释如下:
(1) PDB$SEED: First is PDB$SEED which has container id 2. This is our seed PDB which is a default pluggable database (PDB) used as a template for user-created PDBs by the system. The seed is always named PDB$SEED.
(2) Second is PDBORCL which has container id 3. This pluggable database is the one that was created by me during the installation of oracle database 12c which has our sample HR schema.
为了找到服务的名字,我们使用视图v$active_services
,大多数情况下,服务名和数据库名的名字相同。
SQL> select name from v$active_services where con_id = 3;
NAME
----------------------------------------------------------------
orclpdb
查询到的数据库名和服务名将要用来更新tnsname.ora
文件,并设置一些网络配置。
3 更新tnsname.ora
文件。
在文件C:\dbhome_1\network\admin\tnsname.ora
中加入下面的配置(注意:需要使用TAB键来进行缩进)
ORCLPDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclpdb)
)
)
4 从CBD$ROOT
数据库切换到ORCLPDB
数据库。
SQL> alter session set container = orclpdb;
Session altered.
可以使用下面的操作来确认是否切换到ORCLPDB
数据库。
SQL> show con_name;
CON_NAME
------------------------------
ORCLPDB
5 开启数据库ORCLPDB
, 如果OPEN_MODE
是mounted
状态,需要去开启它,操作如下
SQL> alter pluggable database open;
Pluggable database altered.
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
------------------------------------------------------------------ ------------
ORCLPDB READ WRITE
6 解开HR用户,操作如下
SQL> alter user hr identified by hr account unlock;
User altered.
7 登录HR用户,操作如下
SQL> conn hr/hr @orclpdb;
Connected.
8 enjoy it !