RESTORE_SCHEMA_STATS

The RESTORE_SCHEMA_STATS procedure is used to restore schema-level historical statistics at a specified point in time.

Syntax

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

Parameters

Parameter	Description
ownname	The name of the schema.
as_of_timestamp	The timestamp of the statistics to restore.
force	Specifies whether to force the restore and ignore locks. The default value is FALSE.
no_invalidate	Specifies whether to invalidate the plan cache when collecting statistics. If set to TRUE, the plan cache is not invalidated when collecting statistics.

Exceptions

Error Code	Description
HY000	The object does not exist or the user does not have sufficient privileges. The value is invalid or inconsistent. 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 statistics for the hr schema at a specified point in time.

CALL DBMS_STATS.RESTORE_SCHEMA_STATS ('hr', '2021-09-26 19:02:12.675729');
Query OK, 0 rows affected