UNLOCK_SCHEMA_STATS

The UNLOCK_SCHEMA_STATS procedure unlocks statistics for all tables in a schema.

Syntax

DBMS_STATS.UNLOCK_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 the statistics of a table are locked, the statistics of all dependent objects, including table-level statistics, column-level statistics, histogram statistics, and statistics of all dependent indexes, are also locked.

• If the statistics of an object are locked, an error is returned when you execute the SET_*, DELETE_*, IMPORT_*, or GATHER_* procedure to modify the statistics of a single table, index, or column.

• The UNLOCK_SCHEMA_STATS procedure and UNLOCK_TABLE_STATS procedure cannot unlock statistics of 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. In addition, if the table statistics are locked, the dependent objects are also locked, so you do not need to explicitly call the LOCK_PARTITION_STATS procedure to lock the partition statistics.

Examples

Unlock the statistics of all tables in the hr schema.

USE hr;
Database changed
CALL DBMS_STATS.UNLOCK_SCHEMA_STATS ('hr', stattype=>'ALL');
Query OK, 0 rows affected