Overview

OceanBase Database's system views are divided into dictionary views and performance views. This topic explains how to query these views.

A dictionary view is a virtual table in the database that displays the metadata information and system status of the database. It covers tables, columns, indexes, users, roles, and privileges. Generally, dictionary views are stored in the system tablespace and are read-only. You cannot modify or drop them.

A performance view is a virtual table built based on the memory structure of the database. It displays the performance statistics and monitoring information of the database, and is closely related to the performance. Therefore, it is also known as a dynamic performance view. Performance views provide data about internal disk and memory structures, but they are not designed for this purpose. Generally, performance views are stored in the system tablespace and are read-only. You cannot modify or drop them.

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

Overview

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 information, such as tablespaces, users, roles, and privileges. The sys tenant also contains virtual tables that display the database structure and status. This topic provides an overview of the views in the sys tenant.

Dictionary views

The dictionary views in the sys tenant are virtual tables that display metadata information and the 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 include the oceanbase.CDB_* views and the oceanbase.DBA_* views.

• The oceanbase.CDB_* views: These views are used to query the database objects in all tenants accessed from a sys tenant. Generally, they correspond to tenant-level data dictionary views named DBA_OB_*.

• The oceanbase.DBA_* views: These views are used to query all database objects in the current tenant. To access the views of other tenants, you must use the administrator privilege.

  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 views

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 and monitoring information of the database. The performance views' names start with GV$* or V$*. A query on the GV$ view returns the information of all views whose names start with V$.

• GV$*: Displays the running status and performance metrics of the entire cluster. These views are accessible in the entire cluster and provide global status information. They are classified based on the access privileges as follows:
  • When accessed from a sys tenant, these views display information of all tenants.
  • When accessed from a normal tenant, these views display information about the current tenant.
• V$*: Displays the running status and performance metrics of the node that the client is connected to. These views cannot be used to query the status of other nodes. If you want to query the status of other nodes, use the GV$* views.

Query 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 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 of MySQL tenants, such as the name, data type, and access privileges of databases or tables. 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. Some views are compatible with the mysql.xxx-prefixed views in MySQL. For example, the mysql.help_xxx view contains some 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 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, such as 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

The 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 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 database, the information of the database 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;
  obclient> SHOW TABLES;
  
  

## 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 application usage. In addition, the tenant has system tables and views that display metadata and information about 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 a DBA_ view, you must 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 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. They have the following characteristics:

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

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

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

In an Oracle tenant, 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. In the SYS user mode, you can access all DBA_, ALL_, and USER_ views to access 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_ views of the user and the ALL_ views of all users.

In an Oracle tenant, the DBA_, ALL_, and USER_ views are used to access database metadata. The SYS user can access all these views, and normal users can access only their own views and the 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. The views provide global status information. The views are distinguished based on the user privileges as follows:
  • When you access these views from the SYS tenant, information about all tenants is displayed.
  • When you access these views from a normal tenant, information only about the current tenant is displayed.