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 information of the database, including tables, columns, indexes, users, roles, and privileges. Generally, dictionary views are stored in the system tablespace and cannot be 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. It is mainly used for performance-related purposes and is therefore also known as a dynamic performance view. A performance view provides metadata about internal disk and memory structures, but is not designed to provide such metadata. Generally, performance views are stored in the system tablespace and cannot be modified or dropped.

Dictionary views and performance views are essential for metadata information and system status information in the database. They help us understand the structure, status, and performance of the database, so that we can perform performance tuning and optimization.

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 store information about the system. You can query the views for system information.

When you need to query metadata information and the system status of OceanBase Database, you can query the views in the sys tenant. The sys tenant is a special tenant in OceanBase Database that contains all system-level objects and metadata, such as tablespaces, users, roles, and privileges. The sys tenant has many virtual tables, which are also called views, for displaying the database structure and status. This topic will overview the views in the sys tenant of OceanBase Database.

Dictionary view

Dictionary views in the sys tenant are virtual tables that display the metadata information and system status of the database. You can query these views to obtain the structure and status of the database. The dictionary views in the sys tenant of OceanBase Database include the oceanbase.CDB_* views and the oceanbase.DBA_* views.

• oceanbase.CDB_* views: These views are used to obtain information about database objects that a sys tenant user has access to across tenants. Generally, they correspond to the tenant-level DBA_OB_* views.

• oceanbase.DBA_* views: These views are used to obtain information about all database objects in the current tenant. To access these views, you must have administrator privileges.

The following views are available only in the sys tenant:

• oceanbase.DBA_OB_RESOURCE_POOLS
• oceanbase.DBA_OB_ROOTSERVICE_EVENT_HISTORY
• oceanbase.DBA_OB_SERVERS
• oceanbase.DBA_OB_SERVER_EVENT_HISTORY
• oceanbase.DBA_OB_SERVER_JOBS
• oceanbase.DBA_OB_TENANT_JOBS
• oceanbase.DBA_OB_UNITS
• oceanbase.DBA_OB_UNIT_CONFIGS
• oceanbase.DBA_OB_UNIT_JOBS
• oceanbase.DBA_OB_ZONES

Performance view

The performance views in the sys tenant are virtual tables that display the performance statistics and monitoring information of the database. You can query these views to obtain the performance status and monitoring information of the database. Performance views are named with the GV$* or V$* prefix. Queries against the GV$ views return information about all eligible V$ views.

• GV$*: Displays the operational status and performance metrics of the entire cluster. These views are accessible throughout the cluster and provide global status information. The following table describes the different types of users based on their access privileges:

• When you access the information of all tenants from the sys tenant, the 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 sys tenant and execute the SHOW statement to view the corresponding type of view.

• Here is an example of how to query the oceanbase.CDB_* views, oceanbase.DBA_* views, and performance views:

## Overview of views in a MySQL tenant

In OceanBase Database, a view in a MySQL tenant is a virtual table that consists of rows and columns from one or more base tables. Views can hide the complexity of the underlying 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 the names, data types, and privileges on the databases or tables) of MySQL tenants. They are also known as the data dictionary or system catalogs.

• mysql.xxx views: These views contain data dictionary tables that store metadata of database objects and other system tables used for various purposes. Some mysql.xxx prefix views are compatible. 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 a 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$*: Displays the operational status and performance metrics of the entire cluster. These views are accessible throughout the cluster and provide global status information. The following table describes the different types of users based on the access privileges they have:

• When you access the information of all tenants from the sys tenant, the 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:

  obclient> USE mysql;
  
  

## Overview

OceanBase Database provides some tables, views, and stored procedures compatible with those in Oracle Database to facilitate data migration and application porting. In addition, the Oracle tenant also provides system tables and views to display metadata and information about objects.

Dictionary views

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

• Views with the DBA_ prefix

  These views display metadata of all users in the database. To access a DBA_ view, 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 schemas that belong to the user, the ALL_ prefix views also return schemas 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. They 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_ prefix views.

  • They have the same columns as other views except that the OWNER column is implicit in these views.

In an Oracle tenant of OceanBase Database, different users have different privileges on 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 have access to all DBA_, ALL_, and USER_ prefix views, including the metadata of all users.

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

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

Performance views

The performance views in an Oracle tenant of OceanBase Database 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 SYS.GV$* views are distinguished based on the access privilege as follows:
  • When you access the SYS.GV$* views from the SYS tenant, status information of all tenants is displayed.
  • When you access the SYS.GV$* views from a normal tenant, only status information of the current tenant is displayed.

Query views

You can execute the SELECT statement to query corresponding system views.