The RESTORE_TABLE_STATS procedure restores table-level historical statistics as of a specified timestamp.
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 name of the table. |
| as_of_timestamp | The timestamp to which statistics are restored. |
| force | Specifies whether to restore statistics by force and ignore the lock. Default value: 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 |
|---|---|
| HY000 |
|
Considerations
To call this procedure, you must be the owner of the table. If the object is in the sys tenant, you must be the owner of the table or have the SYSDBA privilege.
Examples
Restore the statistics on the tbl1 table of the testUser01 user to a specified timestamp.
obclient> CALL DBMS_STATS.RESTORE_TABLE_STATS ('testUser01', 'tbl1','2022-12-06 22:00:01.007146');
Query OK, 0 rows affected