Overview

OceanBase Database's system views are divided into dictionary views and performance views. This topic explains the concepts and usage of dictionary views and performance views.

A dictionary view is a virtual table in the database that displays the metadata information and system status of the database, such as tables, columns, indexes, users, roles, and privileges. Dictionary views are usually stored in the system tablespace and can be queried but not modified or dropped.

A performance view is a virtual table built based on the memory structure of the database that displays the performance statistics and monitoring information of the database, which is closely related to the performance. Performance views are also known as dynamic performance views. Performance views provide data about the internal disk structure and memory structure, but displaying such data is not their primary purpose. Performance views are usually stored in the system tablespace and can be queried but not modified or dropped.

Overview of views in the sys tenant

A view is a virtual table that contains rows generated by a query statement. Views in the sys tenant are generated based on system tables and provide access to metadata in the system tables.

Privilege requirements

To query data from a view in the sys tenant, you must have the SELECT privilege on the view.

Views by category

The views in the sys tenant are categorized based on the type of metadata they contain.

Data dictionary views

Data dictionary views provide access to the data dictionary, which contains the schema metadata of the entire system.

Data query views

Data query views provide access to data in the system tables, allowing you to query system data such as the status of tenants, databases, and users.

Performance views

Performance views provide access to performance-related metadata, such as the execution plans of SQL statements and the resource usage of sessions.

Audit views

Audit views provide access to audit metadata, such as audit events and audit rules.

Privilege views

Privilege views provide access to privilege-related metadata, such as privileges and roles.

Statistics views

Statistics views provide access to statistics-related metadata, such as the execution statistics of SQL statements and the usage statistics of indexes.

Tenant lifecycle management views

Tenant lifecycle management views provide access to metadata related to tenant lifecycle management, such as the creation and deletion of tenants.

Database lifecycle management views

Database lifecycle management views provide access to metadata related to database lifecycle management, such as the creation and deletion of databases.

User lifecycle management views

User lifecycle management views provide access to metadata related to user lifecycle management, such as the creation and deletion of users.

Log views

Overview of views in a MySQL tenant

A view is a virtual table derived from one or more base tables or views. You can query a view as if it were a real table. A MySQL tenant can have multiple views.

In OceanBase Database, a MySQL tenant view is a virtual table composed of rows and columns from one or more base tables. Views in a MySQL tenant can hide the complexity of the base tables, simplify queries, and enhance data access efficiency and security.

Dictionary views

The dictionary views of a MySQL tenant include the information_schema.xxx views and the mysql.xxx views.

• information_schema.xxx views: These views provide access to the metadata (including: database or table name, column data type, and access privileges) of MySQL tenants. They are also known as the data dictionary or system catalogs.

• mysql.xxx views: These views contain dictionary tables that store metadata of database objects and other system tables used for various purposes. They are compatible with some views prefixed with mysql.xxx. For example, the mysql.help_xxx view contains help information on the server side; the mysql.time_zone_xxx view records information about time zones; the mysql.user view and the mysql.db view record information about user privileges.

The following are some common dictionary views in a MySQL tenant:

• information_schema.tables: displays information about the tables in the database, such as the table name, table type, and database to which the table belongs.

• information_schema.columns: Displays information about the columns in the database, including the column name, data type, and whether the column is a primary key.

• information_schema.indexes: displays information about the indexes in the database, including the index name, index type, and table to which the index belongs.

• information_schema.views: displays information about the views in the database, such as the view name and definition.

• mysql.user: Displays the user information in the database, including the username, password, and privileges.

Performance view

Performance views in a MySQL tenant start with GV$* or V$*. Each V$ view has a corresponding GV$ view.

• GV$*: These views display the operational status and performance metrics of the entire cluster. You can access these views from any node in the cluster to obtain global status information. The following table describes the different types of users based on their access privileges:

• When you access the information of a tenant from the sys tenant, information of all tenants is displayed.

• When you access a tenant, the information of the tenant is displayed.

• V$*: Displays the operational status and performance metrics of the node to which the client is connected. You can use this view only to query the status of the node to which the client is connected. To query the status of other nodes, use the GV$* views.

Query views

Log in to the MySQL tenant and execute the SHOW statement to view the corresponding type of view.

• Here is an example of how to query the information_schema.* views:

  obclient> USE information_schema;
  obclient> SHOW TABLES;
  

• Here is an example of how to query the mysql.* views:

## Overview

In OceanBase Database, an Oracle tenant provides some tables, views, and stored procedures compatible with those in Oracle Database to facilitate data migration and usage. In addition, the tenant provides system tables and views to display the metadata and information of the database and the objects.

Dictionary views

The following table describes the dictionary views in an Oracle tenant.

• Views with the DBA_ prefix

  These views display metadata of all users in the database. To access the DBA_ views, you need to have the administrator privilege.

• Views with the ALL_ prefix

  These views provide a summary of the database for the user. In addition to the objects owned by the user, these views also return the schema objects that are publicly or explicitly authorized to the user.

• Views with the USER_ prefix

  These views are the most commonly used views for general database users. These views have the following characteristics:

  • They reference the private environments of users in the database and include metadata about the pattern objects created by users and the privileges granted by users.

  • They display only rows related to users and provide a subset of the information displayed in the ALL_ views.

  • They have the same columns as other views but with the implicit OWNER column hidden.

In an Oracle tenant, different users have different privileges on the dictionary views:

• The SYS user The SYS user is the super administrator of the database and has the highest privileges. The SYS user can access all database objects and metadata. When you use the SYS user, you can access all DBA_, ALL_, and USER_ prefix views to obtain the metadata of all users.

• A normal user A normal user can only access the objects and metadata of the user and those that are authorized to the user. In other words, a normal user can only access the USER_ prefix views and the ALL_ prefix views of all users. The user cannot access the DBA_ prefix views.

In summary, in an Oracle tenant, the DBA_, ALL_, and USER_ prefix views are used to access database metadata. The SYS user can access all views, whereas a normal user can access only its own views and the common views of all users.

Performance views

The performance views in an Oracle tenant are the SYS.V$* views and the SYS.GV$* views, which are stored in the SYS tenant. You can omit the SYS. prefix when querying these views.

• SYS.V$*: These views display the operational status and performance metrics of the node to which the client is connected. You can query these views only for the node to which you are connected. If you want to access the status information of other nodes, use the SYS.GV$* views.
• SYS.GV$*: These views display the operational status and performance metrics of the entire cluster. You can query these views across the entire cluster for global status information. The views provide different information based on your privileges:
  • When you query these views in the SYS tenant, information about all tenants is displayed.
  • When you query these views in a normal tenant, only information about the current tenant is displayed.