The DELETE_TABLE_STATS procedure is used to delete statistics at the table level.
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 NULL, the current login username is used. |
| tabname | The name of the table to which this column belongs. |
| partname | The name of the partition (subpartition) from which to retrieve statistics. 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, defaulting to TRUE. |
| cascade_columns | Whether to also delete statistics at the column level, defaulting to TRUE. |
| no_invalidate | Whether to invalidate the Plan Cache when collecting statistics. Set to TRUE to prevent invalidation. |
| force | Whether to forcibly collect statistics, ignoring lock status. Default is FALSE. If set to TRUE, statistics will be collected even if locked. |
Exceptions
| Error Code | Description |
|---|---|
| HY000 |
|
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.
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