Oracle 12c CDB数据库中数据字典架构

0    86    1

Tags:

👉 本文共约8391个字,系统预计阅读时间或需32分钟。

Oracle 12c CDB数据库中数据字典架构

在 CDB 中,数据字典元数据在根和 PDB 之间 是分离的,那对于 Oracle 提供的系统对象 ( 系统级别数据字典 ) 要如何访问呢, Oracle 通过内部指针的方式来实现

CDB_xxx 视图的结果是来自根以及当前所有打开的 PDB 上的相应 DBA_xxx 视图的结果的集合。从 PDB 查询 CDB_xxx 视图时,其仅显示它在相应 DBA_xxx 视图中显示的信息。 如果连接到根并查询 CDB_USERS ,将得到每个容器的公用和本地用户的列表。如果查询 DBA_USERS ,将得到公用用户的列表(在根中仅存在公用用户)。现在如果连接到 PDB 并查询 CDB_USERS 或 DBA_USERS ,将得到 PDB 的相同公用和本地用户的列表。

img

数据字典就是元数据的集合,比如创建的表,列,约束,触发器等等这些都是元数据,需要保存到数据库中。除此之外,Oracle自身的一些数据库对象,如目录,PL/SQL代码等等这些都是元数据,都需要存放在数据字典中。随着12c 容器数据的普及,Oracle数据字典发生了哪些变化呢,下文即是具体描述。

一、数据字典及其形成

1、数据字典

数据字典是元数据的集合,从逻辑上和物理上描述了数据库及内容,存储于SYSTEM与SYSAUX表空间内的若干段。

SYS用户拥有所有的数据字典表,数据字典基本一般以结尾,如col结尾,如col,tab$等,这些数据字典存放在system表空间中。

2、数据字典的形成

在数据库创建阶段创建,在使用阶段维护和更新

无法通过DML操作来修改,只能通过相关的命令修改系统,来达到间接修改数据字典。

3、数据字典包括的内容

数据库对象的定义信息

用户定义信息

PL/SQL代码

用户创建的其他对象等

二、CDB数据库中数据字典架构

1、数据字典存放方式的变更

在Oracle 12c之前的数据库版本,系统数据字典和用户数据字典采取了混合存放的处理方式。

在Oracle 12c CDB数据库中采取分离存放的方式,及各个PDB数据字典独立存放。

从用户和应用程序的角度来看,CDB中每个容器中的数据字典是分开的,因为它将在非CDB中。

2、非CDB混用数据字典情形示意图

在新创建的不包含用户数据的非CDB中,数据字典仅包含系统元数据。

例如,TAB$表包含仅描述Oracle提供的表的行,例如TRIGGER$和SERVICE$。

随着用户数据库对象添加到数据库,则用户添加的这些对象信息被混合存放到了系统数据字典(即Oracle提供的数据对象字典)

如下图描绘了二个基础数据字典表

左边图示中黑色条表示描述系统数据字典的行,绿色条的表示用户对象产生的行相间到数据字典表

这里写图片描述

3、CDB分离数据字典情形示意图

在CDB中,数据字典元数据在根和PDB之间分割。

在下图中,表emp和dept位于PDB中。该用户数据的数据字典也驻留在PDB中。因此,TAB$PDB中的表具有emp表的行和表dept的行。

这里写图片描述

那对于Oracle提供的系统对象(系统级别数据字典)要如何访问呢,Oracle通过内部指针的方式来实现。

如下图显示PDB中的数据字典包含指向根中数据字典的指针。在内部,Oracle提供的对象(如数据字典表定义和PL / SQL包)仅在根中表示。

这里写图片描述

这种架构实现了CDB内的两个主要目标:

    减少重复

        例如,DBMS_ADVISORCDB不是将PL / SQL包的源代码存储在每个PDB中,而是将其存储在CDB$ROOT其中,从而节省了磁盘空间。

    易于数据库升级

        如果数据字典表的定义存在于每个PDB中,并且如果定义在新版本中更改,则每个PDB将需要单独升级以捕获更改。在根中存储表定义只有一次消除了这个问题。

4、元数据和数据链接

CDB使用内部链接机制来分离数据字典信息。

具体来说,Oracle数据库使用以下自动管理的指针:

元数据链接

  Oracle数据库仅在CDB根目录中存储有关字典对象的元数据。例如,数据字典视图下的OBJ$字典表的列定义DBA_OBJECTS仅存在于根目录中。如前一图示,OBJ$在每个PDB表使用称为内部机构的元数据链接指向存储在根定义的OBJ$。

  该数据对应于元数据链路驻留在其PDB,而不是在根。例如,如果您在hrpdb上的表mytable中添加行,然后将行存储在PDB数据文件。PDB和根中的数据字典视图包含不同的行。描述表mytable的一个新行被添加到hrpdb的OBJ$,但是不存在于CDB根目录中的OBJ$。因此,查询DBA_OBJECTS在CDB根和DBA_OBJECTS在hrdpb显示出不同的结果。

数据链接

  注意:

  Oracle数据库12 c版本1(12.1.0.2)中的数据链接称为对象链接。

  在某些情况下,Oracle数据库将一个对象的数据(不仅仅是元数据)存储在应用程序根目录中。应用程序PDB使用称为数据链接的内部机制来引用应用程序根目录中的对象。创建数据链接的应用程序PDB还存储数据链接描述。数据链接继承其所涉及的对象的数据类型。

扩展数据链接

  扩展数据链路是数据链路和元数据链路的混合。像数据链接一样,扩展数据链接引用应用程序根目录中的对象。然而,扩展数据链路也引用PDB中的相应对象。像元数据链接一样,应用程序PDB中的对象从应用程序根目录中的相应对象继承元数据。

  在应用程序根目录中查询时,扩展的数据链接对象仅从应用程序根目录中获取行。然而,当在应用程序PDB中查询时,扩展的数据链接对象从应用程序根和应用程序PDB中获取行。

Oracle数据库自动创建和管理元数据和数据链接CDB$ROOT。用户无法添加,修改或删除这些链接。

三、演示非CDB及CDB数据字典

1、基于非CDB数据库数据字典变化演示

SQL> SELECT name,

2 DECODE (cdb,

3 'YES', 'Multitenant Option enabled',

4 'Regular 12c Database: ')

5 "Multitenant Option",

6 open_mode,

7 con_id

8 FROM v$database; --如查询结果所示,为非CDB数据库

NAME Multitenant Option OPEN_MODE CON_ID


NOCDB Regular 12c Database: READ WRITE 0

--检查当前数据库数据字典是否存在T1对象

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

SQL> select name,owner#,ctime from obj$ where name='T1';

no rows selected

--当前数据库总对象数

SQL> select count(*) from obj$;

COUNT(*)

----------

21703

--由于当前的cdb为手工创建,因此,创建一个新用户

SQL> create user leshami identified by pass

2 default tablespace users;

--授权

SQL> grant dba,resource,connect to leshami;

SQL> conn leshami/pass

SQL> create table t1(id number,ename varchar2(20));

SQL> conn / as sysdba

--可以查询到对象已经添加到数据字典

SQL> select name,owner#,ctime from obj$ where name='T1';

NAME OWNER# CTIME


T1 72 01-JUN-17

--相应的总数目也增加了1个

SQL> select count(*) from obj$;

COUNT(*)

----------

21704

2、基于CDB数据库数据字典变化演示

SQL> SELECT name,

2 DECODE (cdb,

3 'YES', 'Multitenant Option enabled',

4 'Regular 12c Database: ')

5 "Multitenant Option",

6 open_mode,

7 con_id

8 FROM v$database; --此查询结果如下,即连接到了CDB数据库

NAME Multitenant Option OPEN_MODE CON_ID


CDB1 Multitenant Option enabled READ WRITE 0

--当前CDB数据库下的容器

SQL> select name,con_id,open_mode from v$containers;

NAME CON_ID OPEN_MODE


CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

CDB1PDB1 3 MOUNTED

--在CDB容器数据字典中查看是否存在表T1,如下结果显示为无

SQL> select name,owner#,ctime from obj$ where name='T1';

no rows selected

--统计当前CDB容器数据字典中obj$的总数目

SQL> select count(*) from obj$;

COUNT(*)

----------

72635

--打开pdb数据库cdb1pdb1

SQL> alter pluggable database cdb1pdb1 open;

Pluggable database altered.

--切换到pdb容器数据库cdb1pdb1

SQL> alter session set container=CDB1PDB1;

--在PDB中查看是否存在表T1

SQL> select name,owner#,ctime from obj$ where name='T1';

no rows selected

--统计PDB容器数据字典中obj$的总数目

SQL> select count(*) from obj$;

COUNT(*)

----------

72648

--开启另外一个session,使用hr账号登陆

$ sqlplus hr/hr@cdb1pdb1

--创建表对象

SQL> create table t1(id number,ename varchar2(20));

--切换到cdb数据库

SQL> conn / as sysdba

SQL> select 'Leshami' Author,'http://blog.csdn.net/leshami' Blog,

2 '645746311' QQ from dual;

AUTHOR BLOG QQ


Leshami http://blog.csdn.net/leshami 645746311

SQL> select name,owner#,ctime from obj$ where name='T1';

no rows selected

SQL> select count(*) from obj$; --数据库对象在cdb中不存在,总数也没有增加

COUNT(*)

----------

72635

--切换到pdb数据库

SQL> alter session set container=CDB1PDB1;

SQL> select name,owner#,ctime from obj$ where name='T1'; --数据字典中存在已创建的对象

NAME OWNER# CTIME


T1 107 01-JUN-17

SQL> select count(*) from obj$; --数据字典总数目增加1条

COUNT(*)

----------

72649

四、数据字典视图

在启用CDB之后,数据字典相关视图也有一些变化,主要是增加了CDB相关的视图。如下图所示:

这里写图片描述

Data Dictionary Architecture in a CDB

From the user and application perspective, the data dictionary in each container in a CDB is separate, as it would be in a non-CDB.

For example, the DBA_OBJECTS view in each PDB can show a different number of rows. This dictionary separation enables Oracle Database to manage the PDBs separately from each other and from the root.

Purpose of Data Dictionary Separation

In a newly created non-CDB that does not yet contain user data, the data dictionary contains only system metadata. For example, the TAB$ table contains rows that describe only Oracle-supplied tables, for example, TRIGGER$ and SERVICE$ .

The following graphic depicts three underlying data dictionary tables, with the red bars indicating rows describing the system.

Figure 19-2 Unmixed Data Dictionary Metadata in a Non-CDB

Description of Figure 19-2 follows
Description of "Figure 19-2 Unmixed Data Dictionary Metadata in a Non-CDB"

If users create their own schemas and tables in this non-CDB, then the data dictionary now contains some rows that describe Oracle-supplied entities, and other rows that describe user-created entities. For example, the TAB$ dictionary table now has a row describing employees and a row describing departments .

Figure 19-3 Mixed Data Dictionary Metadata in a Non-CDB

Description of Figure 19-3 follows
Description of "Figure 19-3 Mixed Data Dictionary Metadata in a Non-CDB"

In a CDB, the data dictionary metadata is split between the root and the PDBs. In the following figure, the employees and departments tables reside in a PDB. The data dictionary for this user data also resides in the PDB. Thus, the TAB$ table in the PDB has a row for the employees table and a row for the departments table.

Figure 19-4 Data Dictionary Architecture in a CDB

Description of Figure 19-4 follows
Description of "Figure 19-4 Data Dictionary Architecture in a CDB"

The preceding graphic shows that the data dictionary in the PDB contains pointers to the data dictionary in the root. Internally, Oracle-supplied objects such as data dictionary table definitions and PL/SQL packages are represented only in the root. This architecture achieves two main goals within the CDB:

  • Reduction of duplication

    For example, instead of storing the source code for the DBMS_ADVISOR PL/SQL package in every PDB, the CDB stores it only in CDB$ROOT , which saves disk space.

  • Ease of database upgrade

    If the definition of a data dictionary table existed in every PDB, and if the definition were to change in a new release, then each PDB would need to be upgraded separately to capture the change. Storing the table definition only once in the root eliminates this problem.

Metadata and Data Links

The CDB uses an internal linking mechanism to separate data dictionary information.

Specifically, Oracle Database uses the following automatically managed pointers:

  • Metadata links

    Oracle Database stores metadata about dictionary objects only in the CDB root. For example, the column definitions for the OBJ$ dictionary table, which underlies the DBA_OBJECTS data dictionary view, exist only in the root. As depicted in Figure 19-4 , the OBJ$ table in each PDB uses an internal mechanism called a metadata link to point to the definition of OBJ$ stored in the root.

    The data corresponding to a metadata link resides in its PDB, not in the root. For example, if you create table mytable in hrpdb and add rows to it, then the rows are stored in the PDB data files. The data dictionary views in the PDB and in the root contain different rows. For example, a new row describing mytable exists in the OBJ$ table in hrpdb , but not in the OBJ$ table in the CDB root. Thus, a query of DBA_OBJECTS in the CDB root and DBA_OBJECTS in hrdpb shows different results.

  • Data links

    Note:

    Data links were called object links in Oracle Database 12 c Release 1 (12.1.0.2).

    In some cases, Oracle Database stores the data (not only metadata) for an object only once in the application root. An application PDB uses an internal mechanism called a data link to refer to the objects in the application root. The application PDB in which the data link was created also stores the data link description. A data link inherits the data type of the object to which it refers.

  • Extended data link

    An extended data link is a hybrid of a data link and a metadata link. Like a data link, an extended data link refers to an object in an application root. However, the extended data link also refers to a corresponding object in the application PDB. Like a metadata link, the object in the application PDB inherits metadata from the corresponding object in the application root.

    When queried in the application root, an extended data-linked object fetches rows only from the application root. However, when queried in an application PDB, an extended data-linked object fetches rows from both the application root and application PDB.

Oracle Database automatically creates and manages metadata and data links to CDB$ROOT . Users cannot add, modify, or remove these links.

See Also:

Container Data Objects in a CDB

A container data object is a table or view containing data pertaining to multiple containers or the whole CDB.

Container data privileges support a general requirement in which multiple PDBs reside in a single CDB, but with different local administration requirements. For example, if application DBAs do not want to administer locally, then they can grant container data privileges on appropriate views to the common users. In this case, the CDB administrator can access the data for these PDBs. In contrast, PDB administrators who do not want the CDB administrator accessing their data do not grant container data privileges.

Examples of container data objects are Oracle-supplied views whose names begin with V$ and CDB_ . All container data objects have a CON_ID column. The following table shows the meaning of the values for this column.

Table 19-1 Container ID Values

Container IDRows pertain to
``Whole CDB, or non-CDB
1CDB$ROOT
2PDB$SEED
All Other IDsUser-created PDBs, application roots, or application seeds

In a CDB, for every DBA_ view, a corresponding CDB_ view exists. The owner of a CDB_ view is the owner of the corresponding DBA_ view. The following graphic shows the relationship among the different categories of dictionary views:

Figure 19-5 Dictionary Views in a CDB

Description of Figure 19-5 follows
Description of "Figure 19-5 Dictionary Views in a CDB"

When the current container is a PDB, a user can view data dictionary information for the current PDB only. To an application connected to a PDB, the data dictionary appears as it would for a non-CDB. When the current container is the root, however, a common user can query CDB_ views to see metadata for the root and for PDBs for which this user is privileged.

Note:

When queried from the root container, CDB_ and V$ views implicitly convert data to the AL32UTF8 character set. If a character set needs more bytes to represent a character when converted to AL32UTF8, and if the view column width cannot accommodate data from a specific PDB, then data truncation is possible.

The following table shows a scenario involving queries of CDB_ views. Each row describes an action that occurs after the action in the preceding row.

Table 19-2 Querying CDB_ Views

OperationDescription
SQL> CONNECT SYSTEM Enter password: ******** Connected. The SYSTEM user, which is common to all containers in the CDB, connects to the root (see " Common Users in a CDB " ).
SQL> SELECT COUNT(*) FROM CDB_USERS WHERE CON_ID=1; COUNT(*) -------- 41 SYSTEM queries CDB_USERS to obtain the number of common users in the CDB. The output indicates that 41 common users exist.
SQL> SELECT COUNT(DISTINCT(CON_ID)) FROM CDB_USERS; COUNT(DISTINCT(CON_ID)) ----------------------- 4 SYSTEM queries CDB_USERS to determine the number of distinct containers in the CDB.
SQL> CONNECT SYSTEM@hrdb Enter password: ******** Connected. The SYSTEM user now connects to the PDB named hrpdb .
SQL> SELECT COUNT(*) FROM CDB_USERS; COUNT(*) ---------- 45 SYSTEM queries CDB_USERS . The output indicates that 45 users exist. Because SYSTEM is not connected to the root, the CDB_USERS view shows the same output as DBA_USERS . Because DBA_USERS only shows the users in the current container, it shows 45.

See Also:

Oracle Database Administrator’s Guide to learn more about container data objects

Data Dictionary Storage in a CDB

The data dictionary that stores the metadata for the CDB as a whole is stored only in the system tablespaces.

The data dictionary that stores the metadata for a specific PDB is stored in the self-contained tablespaces dedicated to this PDB. The PDB tablespaces contain both the data and metadata for an application back end. Thus, each set of data dictionary tables is stored in its own dedicated set of tablespaces.

See Also:

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

16 − 15 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部