The system views in OceanBase AI Database are divided into dictionary views and performance views. This topic describes the relevant content and viewing methods for these types of views.
Dictionary views are virtual tables in a database that display metadata and system status information, including details about tables, columns, indexes, users, roles, and privileges. These views are typically stored in the system tablespace and are read-only, meaning they cannot be modified or deleted.
Performance views are virtual tables built on the database's memory structure, used to display performance statistics and monitoring information. As they are primarily performance-related, they are also called dynamic performance views. While they can provide data about internal disk and memory structures, that is not their main purpose. Performance views are typically stored in the system tablespace and are read-only, meaning they cannot be modified or deleted.
Dictionary views and performance views are crucial sources of metadata and system status information in a database. They help you understand the database's structure, status, and performance, enabling you to perform performance tuning and optimization.
Overview of views in the sys tenant
When you need to query metadata and system status information in OceanBase AI Database, you can query views under the sys tenant. The sys tenant is a special tenant in OceanBase AI Database that contains all system-level objects and metadata, such as tablespaces, users, roles, and privileges. Under the sys tenant, there are many virtual tables, also known as views, that display database structure and status information. The following section provides an overview of the views under the sys tenant in OceanBase AI Database.
Dictionary views
Dictionary views in the sys tenant are virtual tables that display metadata and system status information in the database. You can query these views to obtain the structure and status of the database. The dictionary views under the sys tenant include oceanbase.CDB_* and oceanbase.DBA_* views.
oceanbase.CDB_*views: These views are used to retrieve information about database objects accessed by all tenants under the system tenant. Generally, the corresponding tenant-level data dictionary views areDBA_OB_*.oceanbase.DBA_*views: These views are used to retrieve information about all database objects under the current tenant. They are visible to all tenants, and access to these views requires administrator privileges.The following views are visible only in the
systenant:- 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
Performance views in the sys tenant are virtual tables that display database performance statistics and monitoring information. You can query these views to obtain the performance status and monitoring data of the database. Performance views start with GV$ or V$. Querying a GV$ view returns information for all matching V$ views.
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 access permissions are classified as follows:- When accessed under the
systenant, it displays information for all tenants. - When accessed under a regular tenant, it displays information for that tenant.
- When accessed under the
V$*: Displays the runtime status and performance metrics of the node connected by the client. This type of view can only provide status information for the node connected by the client. To access the status information of other nodes, use theGV$*views.
Query views
You can log in to the sys tenant and use the SHOW statement to view views of the corresponding type.
The following examples show how to query the
oceanbase.CDB_*,oceanbase.DBA_*, and performance views:obclient> USE oceanbase; obclient> SHOW TABLES;
Overview of views in a user tenant
In OceanBase AI Database, a view is a virtual table that consists of rows and columns from one or more base tables. It can simplify queries, improve data access efficiency, and enhance security by masking the complexity of the underlying base tables.
Dictionary views
Dictionary views in a user tenant include information_schema.xxx views and mysql.xxx views.
information_schema.xxxviews: These views provide access to database metadata within a user tenant, including the name of databases or tables, column data types, and access privileges. They are also known as data dictionaries or system catalogs.mysql.xxxviews: These views include data dictionary tables that store metadata for database objects and system tables used for other operational purposes. They are also compatible with somemysql.xxxprefixed views. For example, themysql.help_xxxview contains some help information on the server side;mysql.time_zone_xxxrecords time zone-related information; themysql.userview and themysql.dbview record user privilege-related information.
The following are some commonly used dictionary views in a user tenant:
information_schema.tables: Displays table information in the database, including the table name, table type, and the database to which the table belongs.
information_schema.columns: Displays column information in the database, including the column name, data type, and whether it is a primary key.
information_schema.indexes: Displays index information in the database, including the index name, index type, and table to which the index belongs.
information_schema.views: Displays view information in the database, including the view name and view definition.
mysql.user: Displays user information in the database, including the username, password, and privileges.
Performance views
Performance views in a user tenant start with GV$* or V$*. Almost every 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 access permissions are classified as follows:- When accessed under the
systenant, it displays information for all tenants. - When accessed under a regular tenant, it displays information for that tenant.
- When accessed under the
V$*: Displays the runtime status and performance metrics of the node connected by the client. This type of view can only provide status information for the node connected by the client. To access the status information of other nodes, use theGV$*views.
Query views
You can log in to the user tenant and use the SHOW statement to view views of the corresponding type.
The following example shows how to query the
information_schema.*views:obclient> USE information_schema; obclient> SHOW TABLES;The following example shows how to query the
mysql.*view:obclient> USE mysql; obclient> SHOW TABLES;