OceanBase Database supports PL system packages to extend database functionality and provide PL access to SQL features.
You can use the provided system packages when creating applications or your own stored procedures. This manual describes the system packages supported by the current version of the OceanBase Database server.
A system package is a encapsulated collection of related program objects stored in a database. Program objects include stored procedures, functions, variables, constants, cursors, and exceptions.
After obtaining the necessary privileges, run the following statement in the oceanbase schema to query the currently supported system packages and their related information:
SELECT * FROM __all_package;
Components of the PL system package
A PL package consists of two parts: the package header (SPECIFICATION) and the package body (BODY). The package header serves as the interface for the application, containing public declarations visible to the application. These declarations define the types, variables, constants, exceptions, cursors, and subprograms that can be used. The package body contains implementation details and private declarations hidden from the application. It fully defines the cursors and subprograms that support the implementation of the package header.
Note
The current version of OceanBase Database does not support user modification of system packages.
Usage of the PL system package
When you create a database, the system packages provided by OceanBase Database are automatically installed.
Overview of PL system packages
The following table lists the PL system packages supported by the current version of OceanBase Database and provides brief descriptions.
System Package |
Description |
|---|---|
| DBMS_RESOURCE_MANAGER | Used to manage resource usage plans, resource usage groups, and resource plan directives. |
| DBMS_SPM | It is used to manage SQL plans, supporting loading, modifying, and deleting plan baseline information. |
| DBMS_STATS | View and modify the statistics collected by the optimizer for database objects. |
| DBMS_UDR | The rewrites binding feature allows you to rewrite the SQL statements received by the database based on matching rules before they are executed. |
| DBMS_XPLAN | Provides features related to logical plan management, including logical plan optimization and tracing. |
| DBMS_WORKLOAD_REPOSITORY | Used to manage the automatic workload repository (WR). |
| DBMS_MVIEW | It enables callers to understand the capabilities of materialized views and potential materialized views, including their query rewrite availability. It can also refresh materialized views that do not belong to the same refresh group and clear logs. |
| DBMS_MVIEW_STATS | Provides an interface for users to manage the collection and retention of statistics for materialized view refresh operations. |
| DBMS_MVIEW_STAT | Allows the caller to use the interface to manage the collection and retention of statistics for materialized view refresh operations. |
| DBMS_TRUSTED_CERTIFICATE_MANAGER | Allows the caller to add, delete, and modify CA root certificates trusted by the OBServer cluster for RPC security authentication. |
| DBMS_OB_LIMIT_CALCULATOR | This API is used to obtain the current resource usage of a cluster, which can be used to plan operations such as scaling, node replacement, and standby tenant creation. |
| DBMS_EXTERNAL_TABLE | Provides a programmatic interface for managing and operating external tables. |
| DBMS_PARTITION | Provides an API for managing dynamic partitions. |