Overview

System views of OceanBase Database include dictionary views and performance views. This topic provides an overview of these views and describes how to query them.

Dictionary views in a database are virtual tables that present metadata and system status information. They provide details about tables, columns, indexes, users, roles, and privileges. These views are typically stored in the system tablespace and are read-only, meaning you cannot modify or delete them.

Performance views, on the other hand, are virtual tables that offer performance statistical and monitoring information, utilizing the database's memory structure. Due to their relation to performance, these views are referred to as dynamic performance views. Although they may provide insights into the internal disk and memory structure, their primary purpose is performance-related. Similar to dictionary views, performance views are stored in the system tablespace and cannot be modified or deleted.

Both dictionary views and performance views serve as crucial sources of metadata and system status information in a database. They provide valuable insights into the database's structure, status, and performance, enabling optimizations to enhance overall performance.

Views in the sys tenant

OceanBase Database allows you to query views in the sys tenant for metadata and system status information. The sys tenant is a special tenant in OceanBase Database. You can query metadata and information about all system-level objects, such as tablespaces, users, roles, and privileges. In the sys tenant, many virtual tables that show the database structure and status information are also called views. The following sections describe views in the sys tenant of OceanBase Database.

Dictionary views

In the sys tenant, dictionary views are virtual tables that show the metadata and system status information of the database. You can query these views for the structure and status information of the database. The sys tenant provides two types of dictionary views: oceanbase.CDB_* and oceanbase.DBA_*.

• oceanbase.CDB_*: Views of this type allow you to query information about some database objects in the sys tenant and user tenants.

• oceanbase.DBA_*: Views of this type allow you to query all information about the database. To query a view of this type, you must have the administrator permissions. You can query the following views only from 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

In the sys tenant, performance views are virtual tables that show performance statistical information and performance monitoring information about the database. You can query these views for the database performance status and monitoring information. Names of performance views start with GV$* or V$*. When you query a GV$ view, all V$ view information meeting the query conditions is returned.

• GV$*: Views of this type display the running status and performance metrics of the entire cluster. They provide global status information and are accessible in the entire cluster. The information returned varies based on the access privileges.
  • If you query a view from the sys tenant, information about all tenants is returned.
  • If you query a view from a user tenant, information about the current tenant is returned.
• V$*: Views of this type display the running status and performance metrics of the node to which the client connects. They provide status information about only the node to which the client connects. To obtain status information about other nodes, query GV$* views.

Query views

You can log on to the sys tenant and execute the SHOW statement to query views of a specific type.

• The following example shows how to query oceanbase.CDB_*, oceanbase.DBA_*, and performance views:

  obclient> USE oceanbase;
  obclient> SHOW TABLES;
  

Views in a MySQL tenant

In OceanBase Database, views in a MySQL tenant are virtual tables, which are logical tables that consist of rows and columns of one or more basic tables. Views can shield the complexity of basic tables to simplify queries and improve data access efficiency and security.

Dictionary views

A MySQL tenant provides two types of views: information_schema.xxx and mysql.xxx.

• information_schema.xxx: Views of this type provide access to database metadata in a MySQL tenant, including the name of a database or table, data type of a column, and access permissions. A view of this type is also called a data dictionary or a system catalog.

• mysql.xxx: Views of this type contain data dictionary tables that store metadata of database objects and system tables intended for other purposes. Some views prefixed with mysql are also supported. For example, mysql.help_xxx records help information of the server side, mysql.time_zone_xxx records information related to time zones, and mysql.user and mysql.db record information related to user privileges.

The following are general dictionary views in a MySQL tenant:

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

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

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

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

• mysql.user: displays information about users in a database, including the username, password, and privileges.

Performance views

Names of performance views in a MySQL tenant start with GV$* or V$*. Generally, each V$ view has a corresponding GV$ view.

• GV$*: Views of this type display the running status and performance metrics of the entire cluster. They provide global status information and are accessible in the entire cluster. The information returned varies based on the access privileges.
  • If you query a view from the sys tenant, information about all tenants is returned.
  • If you query a view from a user tenant, information about the current tenant is returned.
• V$*: Views of this type display the running status and performance metrics of the node to which the client connects. They provide status information about only the node to which the client connects. To obtain status information about other nodes, query GV$* views.

Query views

You can log on to a MySQL tenant and execute the SHOW statement to query views of a specific type.

• Query information_schema.* views

  obclient> USE information_schema;
  obclient> SHOW TABLES;
  

• Query mysql.* views

  obclient> USE mysql;
  obclient> SHOW TABLES;
  

Views in an Oracle tenant

In OceanBase Database, an Oracle tenant provides general objects compatible with Oracle Database, such as tables, views, and stored procedures, to facilitate migration and usage. Oracle tenants also provide system tables and views that display database metadata and object information.

Dictionary views

The following table describes different types of dictionary views in an Oracle tenant.

• Views prefixed with DBA_:

  These views display all relevant information in the entire database. Only database administrators can access views prefixed with DBA_.

• Views prefixed with ALL_:

  These views provide an overview of the database for users. In addition to the schema objects owned by users, these views return the schema objects on which users are publicly or explicitly granted privileges.

• Views prefixed with USER_:

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

  • They reference the private environment of a user in the database, including metadata about schema objects created by the user and privileges granted by the user

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

  • They contain the same columns as other views, except that the OWNER column is implicitly contained.

In an Oracle tenant, the access privileges on dictionary views vary based on users.

• SYS user: As the super administrator of a database, the SYS user has the highest privileges and can access all database object information and metadata. Specifically, the SYS user can access all views prefixed with DBA_, ALL_, or USER_, including the metadata of all users.

• Normal user: A normal user can access only its own object information and metadata, as well as the object information and metadata authorized to it. Specifically, a normal user can access only its own views prefixed with USER_ and views of other users prefixed with ALL_. A normal user cannot access views prefixed with DBA_.

In an Oracle tenant, you can query views prefixed with DBA_, ALL_, or USER_ for database metadata. The SYS user can access all views whereas a normal user can access only its own views and the public views of others.

Performance views

An Oracle tenant provides two types of performance views: SYS.V$* and SYS.GV$*. You can query these views as the SYS user. When you query these views, you can omit the prefix SYS..

• SYS.V$*: Views of this type display the running status and performance metrics of the node to which the client connects. They provide status information about only the node to which the client connects. To obtain status information about other nodes, query SYS.GV$* views.
• SYS.GV$*: Views of this type display the running status and performance metrics of the entire cluster. They provide global status information and are accessible in the entire cluster. The information returned varies based on the access privileges.
  • If you query a view from the sys tenant, information about all tenants is returned.
  • If you query a view from a user tenant, information about the current tenant is returned.

Query views

You can query a system view by using the SELECT statement.

Here is an example:

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