The LOCK_SCHEMA_STATS procedure is used to lock the statistics of all tables for a specified user.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
Syntax
DBMS_STATS.LOCK_SCHEMA_STATS(
ownname VARCHAR2,
stattype VARCHAR2 DEFAULT 'ALL');
Parameters
| Parameter | Description |
|---|---|
| ownname | The username. If the username is set to NULL, the current login username will be used by default. |
| stattype | The type of lock. The following three lock types are generally supported:
'ALL' lock type is supported in Oracle mode. |
Considerations
To call this procedure, you must be the owner of the table. For objects owned by
SYS, you must be the owner or have theSYSDBAprivilege to call this procedure.When the statistics of a table are locked, the statistics of all dependent objects, including table-level statistics, column-level statistics, histogram statistics, and statistics dependent on indexes, are also locked.
If the statistics of an object are locked, an error will be returned when you execute the
SET_*,DELETE_*,IMPORT_*, orGATHER_*procedure to modify the statistics of a single table, index, or column.When you use the
EXPORT_*_STATSprocedure, the locked or unlocked status is not exported along with the table-level statistics.The
UNLOCK_SCHEMA_STATSandUNLOCK_TABLE_STATSprocedures cannot unlock the statistics of corresponding partitions. When you call theLOCK_TABLE_STATSprocedure, it sets the lock at the table level. At this point, you cannot collect statistics for dependent objects such as partitions and indexes. Additionally, if the table-level statistics are locked, their dependent items are also locked, so you do not need to explicitly call theLOCK_PARTITION_STATSprocedure to lock the partition statistics.
Examples
Lock the statistics of all tables in the hr schema.
obclient> CALL DBMS_STATS.LOCK_SCHEMA_STATS(ownname=>'hr', stattype=>'ALL');
Query OK, 0 rows affected