RESTORE_TABLE_STATS

The RESTORE_TABLE_STATS procedure is used to restore table-level historical statistics at a specified time.

Syntax

DBMS_STATS.RESTORE_TABLE_STATS (
  ownname                   VARCHAR2,
  tabname                   VARCHAR2,
  as_of_timestamp           TIMESTAMP WITH TIME ZONE,
  force                     BOOLEAN DEFAULT FALSE,
  no_invalidate             BOOLEAN DEFAULT FALSE);

Parameters

Parameter	Description
ownname	The username.
tabname	The table name.
as_of_timestamp	The timestamp for restoring the statistics.
force	Whether to forcibly restore the statistics and ignore locks. Default value: FALSE.
no_invalidate	Whether to clear the plan cache when collecting statistics. Set to TRUE to not clear the plan cache when collecting statistics.

Exceptions

Error Code	Description
HY000	The object does not exist or the user does not have sufficient privileges. Invalid or inconsistent value. The statistics cannot be restored because the statistics history is unavailable.

Usage

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

Examples

Restore the statistics of the tbl1 table under the testUser01 user at a specified time.

CALL DBMS_STATS.RESTORE_TABLE_STATS ('testUser01', 'tbl1','2022-12-06 22:00:01.007146');
Query OK, 0 rows affected