The LOCK_SCHEMA_STATS procedure is used to lock statistics for all tables in a schema.
Syntax
DBMS_STATS.LOCK_SCHEMA_STATS(
ownname VARCHAR2,
stattype VARCHAR2 DEFAULT 'ALL');
Parameters
| Parameter | Description |
|---|---|
| ownname | The name of the schema. |
| stattype | The type of lock. The following three lock types are generally supported:
|
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.When the statistics of a table are locked, all dependent statistics, including table-level, column-level, histogram, and index-related statistics, 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 lock or unlock status is not exported along with the table-level statistics.The
UNLOCK_SCHEMA_STATSandUNLOCK_TABLE_STATSprocedures cannot unlock statistics for 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 table-level statistics are locked, their dependent items are also locked, so you do not need to explicitly call theLOCK_PARTITION_STATSprocedure to lock partition statistics.
Examples
Lock statistics for all tables in the hr schema.
obclient> CALL DBMS_STATS.LOCK_SCHEMA_STATS(ownname=>'hr', stattype=>'ALL');
Query OK, 0 rows affected