The DELETE_SCHEMA_STATS procedure is used to drop statistics for all tables in a specified schema.
Syntax
DBMS_STATS.DELETE_SCHEMA_STATS (
ownname VARCHAR2,
no_invalidate BOOLEAN DEFAULT FALSE,
force BOOLEAN DEFAULT FALSE);
Parameters
Parameter |
Explanation |
|---|---|
| ownname | The name of the schema. |
| no_invalidate | Whether to flush the plan cache during statistics collection. Valid values: TRUE Indicates that the plan cache is not flushed during statistics collection. |
| force | Whether to forcibly delete the file regardless of the lock status. Default is FALSE. If set to TRUE, statistics will still be deleted even if they are locked. |
Exceptions
Error code HY000 indicates that the object does not exist or you do not have sufficient privileges.
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 the statistics of all tables in the hr schema.
obclient> CALL DBMS_STATS.DELETE_SCHEMA_STATS('hr');
Query OK, 0 rows affected