DELETE_TABLE_STATS

The DELETE_TABLE_STATS procedure is used to delete table-level statistics.

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 login username is used by default.
tabname	The name of the table to which this column belongs.
partname	The name of the partition (subpartition) from which to retrieve the statistics. If the table is partitioned and partname is NULL, statistics are retrieved at the global table level.
cascade_parts	Whether to also delete partition-level and subpartition-level statistics. The default value is TRUE.
cascade_columns	Whether to also delete column-level statistics. The default value is TRUE.
no_invalidate	Whether to invalidate the Plan Cache when collecting statistics. Setting it to TRUE means the Plan Cache is not invalidated when collecting statistics.
force	Whether to forcibly collect statistics and ignore the lock status. The default value is FALSE. If set to TRUE, statistics are collected even if they are locked.

Exceptions

Error code	Description
HY000	The table does not exist or the user does not have sufficient privileges. The user statistics table is damaged and may need to be upgraded. The statistics for this object are locked.

Usage notes

You must be the owner of the table to call this procedure.

Examples

Delete all statistics for the table tbl1 of the user testUser01.

CALL DBMS_STATS.DELETE_TABLE_STATS('testUser01', 'tbl1');
Query OK, 0 rows affected