UNLOCK_SCHEMA_STATS

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

Syntax

DBMS_STATS.UNLOCK_SCHEMA_STATS (
  ownname        VARCHAR2,
  stattype       VARCHAR2 DEFAULT 'ALL');

Parameters

Considerations

• You must be the owner of the table to call this procedure. For objects owned by SYS, you must be the owner or have the SYSDBA privilege.

• When statistics for a table are locked, statistics for all dependent objects, including table-level statistics, column-level statistics, histogram statistics, and statistics for all dependent indexes, are also locked.

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

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

Examples

Unlock statistics for all tables in the hr schema.

obclient [SYS]> CREATE USER hr IDENTIFIED BY 123456;
Query OK, 0 rows affected

obclient [SYS]> CALL DBMS_STATS.UNLOCK_SCHEMA_STATS ('hr', stattype=>'ALL');
Query OK, 0 rows affected