Overview

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 database objects of all tenants from the sys tenant. In general, each such view corresponds to a tenant-level data dictionary view named DBA_OB_*.

• oceanbase.DBA_*: Views of this type allow you to query all database object information in the current tenant. These views are visible to all tenants but require 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-compatible tenant

In OceanBase Database, views in a MySQL-compatible 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-compatible 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-compatible 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-compatible 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-compatible 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-compatible 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-compatible tenant

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

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 comprehensive overview of the database. In addition to objects owned by you, these views also return objects that are shared with you, such as objects that you have been granted privileges on.

• 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 environment of users in the database and include metadata about the schema objects created by users and the 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 except that the OWNER column is implicit in these views.

In an Oracle tenant, different users have different privileges on the 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 granted 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. The user cannot access DBA_ views.

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

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

• 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 to obtain global status information. These views are distinguished as follows based on the access privileges:
  • 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.

In an Oracle-compatible 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.

Query views

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

An Oracle-compatible 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;