Overview

OceanBase Database provides dictionary views and performance views. This topic explains the concepts and usage of these 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. Generally, dictionary views are 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 this data is not their primary purpose. Generally, performance views are stored in the system tablespace and can be queried but not modified or dropped.

Dictionary views and performance views are essential for obtaining the metadata information and system status of the database. This information helps us understand the structure, status, and performance of the database for performance tuning and optimization.

Overview of views in the sys tenant

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, also known as views, for displaying the database structure and status information. This topic will provide an overview of the views in the sys tenant of OceanBase Database.

Dictionary views

Dictionary views in the sys tenant are virtual tables that display the metadata information and system status of the database. You can query the dictionary views for 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 need to 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. A query on the GV$ view returns the information of all views named with the V$* prefix that meet the criteria.

• GV$*: Displays the operational status and performance metrics of the entire cluster. You can access these views throughout 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 about 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:

  obclient> USE oceanbase;
  obclient> SHOW TABLES;
  

Overview of views in a MySQL tenant

In OceanBase Database, a view in a MySQL tenant is a virtual table composed 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 (such as the name, data type, or access privileges of a database or table) 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 for various purposes. They are compatible with some views whose names start with the mysql.xxx prefix. 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, including the table name, table type, and database to which the table belongs.

• information_schema.columns: displays the information about 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 user information in the database, including usernames, passwords, 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. There are the following distinctions based on access privileges:
  • When you access the information of a tenant from the sys tenant, information about 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;
  obclient> SHOW TABLES;
  

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 has system tables and views that can be used to display metadata and information about objects.

Dictionary views

The dictionary views in an Oracle tenant are comprised of the following tables.

• Views with the DBA_ prefix

  These views display metadata of all objects 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 schemas that belong to the user, these views 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. These views have the following characteristics:

  • They reference the private environment of the user in the database and include metadata about the user-created mode objects and the user-granted privileges.

  • They display only rows related to the user, which is a subset of the information displayed in the ALL_ views.

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

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

• 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_ views to access 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_ 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 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 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 by the following criteria:
  • When accessed from the SYS tenant, these views display the information of all tenants.
  • When accessed from a normal tenant, these views display the information of the current tenant.

Query views