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 to restore to. |
| force | Whether to forcibly restore and ignore locks. Default is FALSE. |
| no_invalidate | Whether to invalidate the plan cache when collecting statistics. Set to TRUE to prevent invalidation of the plan cache when collecting statistics. |
Exceptions
| Error Code | Description |
|---|---|
| HY000 |
|
Considerations
To call this procedure, you must be the owner of the schema. For objects owned by SYS, you must be the owner or have the SYSDBA privilege.
Examples
Restore statistics for the hr schema at a specific point in time.
obclient [hr]> CALL DBMS_STATS.RESTORE_SCHEMA_STATS ('hr', '2021-09-26 19:02:12.675729');
Query OK, 0 rows affected