Dictionary views

In the Oracle mode of OceanBase Database, the data dictionary is a set of read-only tables that store management metadata of the databases.

Overview

In Oracle mode, the data dictionary consists of system tables and dictionary views and is an important part of the database.

Information about the database is stored in system tables. Only OceanBase Database can write data to and read data from these tables. You rarely directly access the system tables because they are internal implementation-oriented and not easy to read. In addition, external compatibility is not ensured between different versions.

Dictionary views decode system table data into useful information to simplify the system table data. For example, a dictionary view decodes a username or table name as the filter condition for a join key and a WHERE clause. The views contain the names and descriptions of all objects in the data dictionary. Some views are accessible to all database users, whereas others are available only to the database administrator.

A data dictionary contains at least the following information:

• The definitions of schema objects in the database, including default values of columns and integrity constraints

• The amount of storage space allocated to and used by the schema objects

• The database usernames, user privileges and roles, and user-related audit information

Composition

The following table describes the content of different types of dictionary views.

Views prefixed with ALL_

Views prefixed with ALL_ provide an overview of the database for users. In addition to the schema objects owned by users, these views return the schema objects on which users are publicly or explicitly granted privileges.

Views prefixed with DBA_

Views prefixed with DBA_ display all relevant information in the entire database. Only database administrators can access views prefixed with DBA_.

Views prefixed with USER_

Views that are most frequently used by regular database users are prefixed with USER_.

These views have the following features:

• Reference the private environment of a user in the database, including metadata about schema objects created by the user and privileges granted by the user.

• Display only rows related to a user and return a subset of the information in views prefixed with ALL_.

• Contain the same columns as other views, except that the OWNER column is implicitly contained.

• Can have abbreviated PUBLIC synonyms for convenience.

Working mechanism

In Oracle mode, the SYS user owns all base tables and user-accessible views of the data dictionary. An SYS user is automatically created when each Oracle tenant is created.

During database operations, OceanBase Database reads the data dictionary to determine whether schema objects exist and whether users have required privileges to access them. In Oracle mode, OceanBase Database continuously updates the data dictionary to reflect changes in database structures, auditing, privileges, and data.

For example, assume that the hr user creates a table named interns. Then OceanBase Database adds new rows to the data dictionary. These rows reflect the new table, columns, segments, data blocks, and the privileges that the hr user has on the table. The new information is visible the next time the dictionary views are queried.

Data in the base tables of the data dictionary is required for the normal operation of the Oracle mode of OceanBase Database. Only OceanBase Database writes or modifies data dictionary information. In Oracle mode, users must not modify rows or schema objects contained in the SYS schema because such operations may compromise data integrity. The security administrator must strictly prevent such activities.

Storage

In OceanBase Database, the data dictionary that stores the metadata for each tenant as a whole is stored only in the dictionary information of each tenant.

The data dictionary that stores the metadata for a specific tenant is stored in the dictionary table dedicated to the tenant. Therefore, each set of data dictionaries is stored in the dictionary table dedicated to its tenant.