The DELETE_TABLE_STATS procedure is used to drop 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 |
Explanation |
|---|---|
| ownname | 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 (or subpartition) where the table for which statistics are to be retrieved is located. If the table is partitioned and partname is NULL, statistics are retrieved from the global table level. |
| cascade_parts | Whether to delete partition-level and subpartition-level statistics simultaneously. Default: TRUE. |
| cascade_columns | Whether to delete column-level statistics as well. Default value: TRUE. |
| no_invalidate | Whether to flush the plan cache when statistics are collected. Valid values: TRUE Indicates that the plan cache is not flushed during statistics collection. |
| force | Whether to forcibly collect the data, ignoring the lock status. Default is FALSE. If set to TRUE, statistics collection will continue even if the statistics are locked. |
Exceptions
Error Code |
Description |
|---|---|
| HY000 |
|
Usage instructions
To call this procedure, you must be the owner of the table. For objects owned by SYS, you must be the table owner or have the SYSDBA privilege to call this procedure.
Examples
Drops all statistics for table tbl1 of user testUser01.
obclient> CALL DBMS_STATS.DELETE_TABLE_STATS('testUser01', 'tbl1');
Query OK, 0 rows affected