Oracle-compatible views

This topic describes views supported in the Oracle-compatible mode of OceanBase Database.

Storage location

Oracle-compatible views are stored in the SYS schema.

View types

In the Oracle-compatible mode of OceanBase Database, the data dictionary is composed of system tables and dictionary views, which are essential components of the database.

Data dictionary views

A data dictionary views encompass a range of information, including but not limited to the following:

• Definitions of each schema object in the database, including details about column default values and integrity constraints.

• Allocation and usage of storage space for the assigned schema objects.

• User details such as names, granted privileges and roles, along with relevant audit information.

The name of a data dictionary view can contain one of the following prefixes:

• SYS.ALL_*: Views prefixed with SYS.ALL_* provide an overview of the database. In addition to the schema objects owned by users, these views return the schema objects on which users are publicly or explicitly granted privileges.

• SYS.DBA_*:Views prefixed with SYS.DBA_ display all relevant information of the entire database. Only database administrators can access views prefixed with SYS.DBA_.

• SYS.USER_*:

  • Views prefixed with SYS.USER_* 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 that are relevant to the user and return a subset of the information in SYS.ALL_* views.
  • These views contain the same columns as other views but do not include the OWNER column (it is implicit).

Dynamic performance views

Dynamic performance views can be prefixed with SYS.V$ or SYS.GV$:

• SYS.V$*: Views of this type display the running status and performance metrics of only the node to which the client connects. To obtain the 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. These views can be accessed throughout the cluster and provide global status information. They are differentiated based on access privileges as follows:

  • 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.

References

• Standard views
• OceanBase-specific views