The RESTORE_SCHEMA_STATS procedure restores user-level historical statistics as of a specified timestamp.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
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 username. If the parameter is set to NULL, the current login username is used by default. |
| as_of_timestamp | The timestamp to which statistics are restored. |
| force | Specifies whether to forcibly restore statistics and ignore the lock. The default value is FALSE. |
| no_invalidate | Specifies whether to refresh the plan cache during statistics collection. If you set the value to TRUE, the plan cache will not be refreshed during statistics collection. |
Exceptions
| Error code | Description |
|---|---|
| OBE-20000 | The object does not exist, or you do not have the required privileges. |
| OBE-20001 | Values are invalid or inconsistent. |
| OBE-20006 | The historical statistics are unavailable and cannot be restored. |
Considerations
To call this procedure, you must be the owner of the table. To call this procedure on a table object owned by the SYS user, you must be the table owner or have the SYSDBA privilege.
Examples
Restore the statistics of the hr user to a specified timestamp.
obclient> CALL DBMS_STATS.RESTORE_SCHEMA_STATS ('hr', TO_TIMESTAMP('2021-09-26 19:02:12.675729', 'YYYY-MM-DD HH24:MI:SS.FF'));
Query OK, 0 rows affected