The DELETE_TABLE_STATS procedure is used to delete statistics at the table level.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
Syntax
DBMS_STATS.DELETE_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
cascade_parts BOOLEAN DEFAULT TRUE,
cascade_columns BOOLEAN DEFAULT TRUE,
no_invalidate BOOLEAN DEFAULT FALSE,
force BOOLEAN DEFAULT FALSE);
Parameters
| Parameter | Description |
|---|---|
| ownname | The username. If the username is set to NULL, the current logged-in username will be used by default. |
| tabname | The name of the table to which this column belongs. |
| partname | The name of the partition (subpartition) of the table from which statistics are to be retrieved. If the table is partitioned and partname is NULL, statistics are retrieved at the global table level. |
| cascade_parts | Whether to also delete statistics at the partition and subpartition levels. The default value is TRUE. |
| cascade_columns | Whether to also delete statistics at the column level. The default value is TRUE. |
| no_invalidate | Whether to invalidate the plan cache when collecting statistics. If set to TRUE, the plan cache will not be invalidated when collecting statistics. |
| force | Whether to forcibly collect statistics, ignoring the lock status. The default value is FALSE. If set to TRUE, statistics will be collected even if they are locked. |
Exceptions
| Error code | Description |
|---|---|
| OBE-20000 | The object does not exist or the user does not have sufficient privileges. |
| OBE-20002 | The user statistics table is damaged and may need to be upgraded. |
| OBE-20005 | The statistics for this object are locked. |
Considerations
To call this procedure, you must be the owner of the table. For objects owned by SYS, you must be the owner or have the SYSDBA privilege to call this procedure.
Examples
Delete all statistics for the table tbl1 owned by the user testUser01.
obclient> CALL DBMS_STATS.DELETE_TABLE_STATS('testUser01', 'tbl1');
Query OK, 0 rows affected