ORACLE12C Data Dictionary schema in the CDB database

A data dictionary is a collection of metadata, such as created tables, columns, constraints, triggers, and so on, which are metadata that need to be saved in the database. In addition, some of Oracle's own database objects, such as directories, PL/SQL code, and so on, are metadata and need to be stored in a data dictionary. With the popularity of 12c container data, what has changed in Oracle data dictionaries, as described below.

I. Data dictionary and its formation 1, data dictionary

A data dictionary is a collection of metadata that logically and physically describes a database and its contents, stored in a number of segments within the system and Sysaux table spaces.

SYS user owns all data dictionary tables, and the data dictionary is basically a knot end , such as col , tab$, and so on, these data dictionaries are stored in the system table space.

2, the formation of data dictionary

Created during the database creation phase, maintained and updated during the use phase

Can not be modified through DML operation, only through the relevant command to modify the system to achieve indirect modification of the data dictionary.

3, the data dictionary includes the content

Definition Information for database objects

User-defined information

PL/SQL code

Other objects created by the user, etc.

For a detailed description of the data dictionary, refer to: Oracle data dictionary and data dictionary view

Ii. Data dictionary schema in CDB database 1. Change of data dictionary storage mode

In the database versions prior to Oracle 12c, the system data dictionary and the user Data dictionary were processed in a mixed-storage manner.

Separate storage is taken in the Oracle 12c CDB database, and each PDB data dictionary is stored independently.

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

2. Non-CDB mixed data dictionary case

In a newly created non-CDB that does not contain user data, the data dictionary contains only system metadata.

For example, the tab$ table contains rows that describe only the tables provided by Oracle, such as trigger$ and service$.

As user database objects are added to the database, the user-added object information is mixed into the system data dictionary (the Oracle-provided data object dictionary)

As depicted in two basic data dictionary tables

The black bar in the left illustration indicates the line describing the system data dictionary, and the green bar represents the rows generated by the user object into the data dictionary table

3. CDB Separate data dictionary case

In CDB, data dictionary metadata is split between the root and the PDB.

In, the table EMP and dept are located in the PDB. The data dictionary for this user data also resides in the PDB. Therefore, the tables in tab$pdb have the rows of the EMP table and the rows of the table dept.

How does Oracle access the system objects (System-level data dictionaries) provided by Oracle, which is implemented in the form of internal pointers.

The data dictionary in the display PDB contains a pointer to the data dictionary in the root. Internally, Oracle-provided objects, such as data dictionary table definitions and PL/SQL packages, are represented only in the root.

This architecture achieves the two main objectives within the CDB:

???? Reduce duplication

???????? For example, DBMS_ADVISORCDB does not store the PL/SQL package's source code in each PDB, but instead stores it in Cdb$root, saving disk space.

???? Easy Database Upgrade

???????? If the definition of a data dictionary table exists in each PDB, and if the definition changes in the new version, each PDB will need to be upgraded separately to capture the changes. storing table definitions in the root only once eliminates this problem.

4. Meta data and Data link

CDB uses an internal linkage mechanism to separate data dictionary information.

Specifically, the Oracle database uses the following automatically managed pointers:

• Meta Data Link

?? The Oracle database stores metadata about the Dictionary object only in the CDB root directory. For example, the column definition for the obj$ dictionary table under the data dictionary view dba_objects only exists in the root directory. As in the previous illustration, obj$ uses a metadata link called an internal authority in each PDB table to point to the obj$ stored in the root definition.

?? The data corresponds to the metadata link residing in its PDB, not at the root. For example, if you add rows to a table mytable on hrpdb, then store the rows in the PDB data file. The data dictionary view in the PDB and root contains different rows. A new row in the description table MyTable is added to the hrpdb obj$, but does not exist in the obj$ of the CDB root directory. Therefore, the query dba_objects shows different results in the CDB root and dba_objects in HRDPB.

• Data link

?? Attention:
?? Data links in Oracle database in version 1 (12.1.0.2) are called object links.

?? In some cases, the Oracle database stores the data of an object (not just metadata) in the application root directory. Application PDB uses an internal mechanism called a data link to reference an object in the application's root directory. The application PDB that creates the data link also stores the data link description. Data link inherits the data type of the object it is involved in.

• Extended Data Link

?? An extended data link is a mixture of data links and metadata links. Like data links, extended data Links reference objects in the application's root directory. However, the Extended Data link also refers to the corresponding object in the PDB. Like metadata links, objects in the application PDB inherit metadata from corresponding objects in the application's root directory.

?? When querying in the application root directory, the Extended Data link object only fetches rows from the application root directory. However, when querying in the application PDB, the Extended Data link object fetches rows from the application root and application PDB.

Oracle database automatically creates and manages metadata and data link cdb$root. Users cannot add, modify, or delete these links.

Iii. demonstration of non-CDB and CDB data dictionary 1, based on non-CDB database data dictionary changes

Sql>SELECT name,2 DECODE (CDB,3' YES ',' Multitenant Option enabled ',4' Regular12c Database: ')5"Multitenant Option",6 Open_mode,7 con_id8 from V$database;-as shown in the query results, for non-CDB database name Multitenant Option open_mode con_id-----------------------------------------------------------------nocdb Regular12c Database:read WRITE0--Checks whether the current database data dictionary exists T1 object sql>Select Name,owner#,ctime from obj$ where name=' T1 '; no rows selected--Current database total number of objects sql>Select COUNT (*) from obj$; COUNT (*)----------21703---because the current CDB is created manually, create a new user sql> creates the user Leshami identified by pass2 default tablespace users;  --authorizing sql> grant Dba,resource,connect to Leshami; Sql> conn leshami/passsql> CREATE TABLE t1 (ID number,ename varchar2 (20)); sql> conn/as sysdba--can query to the object has been added to the data dictionary sql> select Name,owner#,ctime from obj$ where name= 'T1 '; name OWN er# CTIME-----------------------------T1  01-jun---The corresponding total number also increased by 1 sql> Select Count (*) from obj$; COUNT (*)---------- 21704              

2, based on CDB database data dictionary change demo

sql> SELECT Name,2 DECODE (CDB,3 ' YES ', ' multitenant Option enabled ',4 ' Regular 12c Database: ')5 "Multitenant Option",6 Open_mode,7 con_id8 from V$database; -This query results in the following, that is, connected to the CDB database name Multitenant Option OPEN_mode con_id-----------------------------------------------------------------CDB1 multitenant Option enabled READ WRITE 0-- Container under current CDB database sql> Select Name,con_id,open_mode from V$containers;name CON_id Open_mode---------------------------------------cdb$root 1 Read writepdb$seed 2 read ONLYCDB1PDB1 3 mounted--See if Table T1 exists in the CDB container data dictionary, the following results are shown as sql> select Name,owner#,ctime from obj$ where name=' T1 '; no rows selected--statistics The total number of obj$ in the current CDB container data dictionary sql> select COUNT (*) from obj$;COUNT (*)----------72635--Open the PDB database cdb1pdb1sql> ALTER pluggable DB CDB1PDB1 open; Pluggable database altered.--switch to the PDB container DB cdb1pdb1sql> alter session set container=cdb1pdb1;--see if the table exists in the PDB t1sql> Select Name,owner#,ctime from obj$ where name=' T1 '; no rows selected--statistics The total number of obj$ in the PDB container data dictionary sql> select COUNT (*) from obj$;COUNT (*)----------72648--open another session, use the HR account to login $ sqlplus hr/[email protected]--CREATE Table object sql> created tables T1 (ID number,ename varchar2 (20) );--Switch to CDB database sql> conn/as sysdbasql> Select' Leshami ' Author, Http://blog.csdn.net/leshami ' Blog, 2 ' 645746311 ' QQ from dual; AUTHOR BLOG qq--------------------------------------------Leshami http://blog.csdn.net/ Leshami 645746311sql> Select Name,owner#,ctime from obj$ where Name= ' T1 '; no rows Selectedsql> Select count (*) from obj$;--database objects do not exist in CDB and the total is not increased  COUNT (*)---------- 72635--switch to PDB database sql> alter session SET CONTAINER=CDB1PDB1; Sql> Select Name,owner#,ctime from obj$ where Name= ' T1 ';--a created object exists in the data dictionary name owner# Ctime----------------------------------T1 107 01-jun-17sql> Select COUNT (*) from obj$; --Total number of data dictionaries increased by 1  COUNT (*)---------- 72649    

Four, Data dictionary view

After CDB is enabled, there are some changes in the data dictionary-related views, mainly by adding CDB-related views. As shown in the following:

Ext.: http://blog.csdn.net/leshami/article/details/72833564

ORACLE12C Data Dictionary schema in the CDB database