Overview

OceanBase Database provides system views, which are divided into dictionary views and performance views. This topic describes the dictionary views and performance views.

A dictionary view is a virtual table that displays metadata and system status information of the database, including tables, columns, indexes, users, roles, and privileges. A dictionary view is stored in the system tablespace and is read-only. You cannot modify or delete a dictionary view.

A performance view is a virtual table that displays performance statistics and monitoring information of the database. A performance view is also called a dynamic performance view. A performance view provides information about the internal disk and memory structures, but this is not its main purpose. A performance view is stored in the system tablespace and is read-only. You cannot modify or delete a performance view.

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

Overview of views in the sys tenant

You can query the views in the sys tenant to obtain the metadata and system status information of OceanBase Database. 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. In the sys tenant, there are many virtual tables for displaying the database structure and status information, which are also called views. This topic provides an overview of the views in the sys tenant of OceanBase Database.

Dictionary views

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

• oceanbase.CDB_* views: These views are used to obtain the database objects of all tenants under the sys tenant. Generally, they correspond to the tenant-level data dictionary views, which are DBA_OB_*.

• oceanbase.DBA_* views: These views are used to obtain the database objects of the current tenant. All tenants can access these views, but you need to have administrator privileges to access them.

  The following views are only visible 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 for displaying 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. The names of performance views start with GV$* and V$*. Querying a GV$ view returns the information of all corresponding V$ views.

• GV$*: These views are used to display the running status and performance metrics of the entire cluster. These views can be accessed throughout the cluster and provide global status information. The information displayed varies based on the access privileges:
  • In the sys tenant, all tenant information is displayed.
  • In a regular tenant, only the information of the current tenant is displayed.
• V$*: These views are used to display the running status and performance metrics of the node to which the client is connected. These views can only provide the status information of the node to which the client is connected. To access the status information of other nodes, use GV$* views.

Query views

You can log in to the sys tenant and use the SHOW statement to view the corresponding views.

• Examples of querying the oceanbase.CDB_* views, oceanbase.DBA_* views, and performance views are as follows:

  obclient> USE oceanbase;
  obclient> SHOW TABLES;
  

Overview of views in MySQL tenants

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 base tables, simplify query operations, and improve the efficiency and security of data access.

Dictionary views

The dictionary views in 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 the MySQL tenant, including the names of databases or tables, data types of columns, and access privileges. They are also known as data dictionaries or system catalogs.

• mysql.xxx views: These views include data dictionary tables that store metadata of database objects and system tables used for other purposes. They also include some mysql.xxx prefixed views. For example, the mysql.help_xxx views contain server-side help information; the mysql.time_zone_xxx views record timezone-related information; and the mysql.user and mysql.db views record user privilege-related information.

Some commonly used dictionary views in MySQL tenants are as follows:

• information_schema.tables: Displays table information, including table names, table types, and databases to which tables belong.

• information_schema.columns: Displays column information, including column names, data types, and whether the columns are primary keys.

• information_schema.indexes: Displays index information, including index names, index types, and tables to which indexes belong.

• information_schema.views: Displays view information, including view names and view definitions.

• mysql.user: Displays user information, including usernames, passwords, and privileges.

Performance views

The performance views in a MySQL tenant start with GV$* and V$*. Each V$ view has a corresponding GV$ view.

• GV$*: These views are used to display the running status and performance metrics of the entire cluster. These views can be accessed throughout the cluster and provide global status information. The information displayed varies based on the access privileges:
  • In the sys tenant, all tenant information is displayed.
  • In a regular tenant, only the information of the current tenant is displayed.
• V$*: These views are used to display the running status and performance metrics of the node to which the client is connected. These views can only provide the status information of the node to which the client is connected. To access the status information of other nodes, use GV$* views.

Query views

You can log in to a MySQL tenant and use the SHOW statement to view the corresponding views.

• Example of querying the information_schema.* views:

  obclient> USE information_schema;
  obclient> SHOW TABLES;
  

• Example of querying the mysql.* views:

  obclient> USE mysql;
  obclient> SHOW TABLES;
  

Overview of views in an Oracle tenant

OceanBase Database provides an Oracle tenant that includes common tables, views, and stored procedures from Oracle Database, facilitating migration and usage. In an Oracle tenant, there are also system tables and views that display database metadata and object information.

Dictionary views

The dictionary views in an Oracle tenant are described in the table below.

• Views with the DBA_ prefix

  These views display all relevant information in the database. To access DBA_ views, you need administrator privileges.

• Views with the ALL_ prefix

  These views provide an overview of the database. In addition to schema objects owned by the user, these views also return schema objects that are publicly or explicitly granted to the user.

• Views with the USER_ prefix

  These views are most commonly used by regular database users. They have the following characteristics:

  • They reference the private environment of the user and include metadata about schema objects created by the user and privileges granted to the user.

  • They only display rows related to the user and return a subset of the information from ALL_ views.

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

In an Oracle tenant, different users have different privileges for accessing dictionary views:

• SYS user As the super administrator, the SYS user has the highest privileges and can access all database objects and metadata information. In the SYS user, you can access all views with the DBA_, ALL_, and USER_ prefixes, including metadata information of all users.

• Regular users Regular users can only access their own objects and metadata information, as well as objects and metadata information granted to them. That is, they can only access views with the USER_ prefix and ALL_ prefix views of all users, but cannot access DBA_ prefix views.

In summary, in an Oracle tenant, views with the DBA_, ALL_, and USER_ prefixes are used to access database metadata information. The SYS user can access all views, while regular users can only access their own views and common views of all users.

Performance views

In an Oracle tenant, performance views include SYS.V$* and SYS.GV$*, which are both located in the SYS schema. When querying, you can omit the SYS. prefix.

• SYS.V$*: These views display the runtime status and performance metrics of the node to which the client is connected. These views can only provide status information of the node to which the client is connected. If you need to access the status information of other nodes, please use the SYS.GV$* views.
• SYS.GV$*: These views display the runtime status and performance metrics of the entire cluster. These views can be accessed across the entire cluster and provide global status information. The views are distinguished based on access privileges:
  • When accessed from the SYS tenant, they display information of all tenants.
  • When accessed from a regular tenant, they display information of the current tenant.

Query views

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

Here is a sample query:

obclient> SELECT * FROM DBA_VIEWS;
obclient> SELECT * FROM ALL_VIEWS;