LOCK_SCHEMA_STATS

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: 'ALL' 'DATA' 'CACHE'

Usage notes

• You must be the owner of the table to call this procedure.

• When statistics of a table are locked, all dependent statistics, 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_*、GATHER_* procedure to modify statistics of a single table, index, or column.

• When you use the EXPORT_*_STATS procedure, the lock or unlock status will not be exported with table-level statistics.

• The UNLOCK_SCHEMA_STATS and UNLOCK_TABLE_STATS procedures cannot unlock statistics of corresponding partitions. When you call the LOCK_TABLE_STATS procedure, 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 dependencies are also locked, so you do not need to explicitly call the LOCK_PARTITION_STATS procedure to lock partition statistics.

Examples

Lock statistics for all tables in the hr schema.

CALL DBMS_STATS.LOCK_SCHEMA_STATS(ownname=>'hr', stattype=>'ALL');
Query OK, 0 rows affected