UNLOCK_TABLE_STATS

The UNLOCK_TABLE_STATS procedure is used to unlock table-level statistics.

Syntax

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

Parameters

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 the SYSDBA privilege.

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

• 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 on dependent objects such as partitions and indexes. Additionally, if the table statistics are locked, the dependent objects are also locked, so you do not need to explicitly call the LOCK_PARTITION_STATS stored procedure to lock partition statistics.

Examples

Unlock the table-level statistics of the t1 table in the test user.

obclient> CALL DBMS_STATS.UNLOCK_TABLE_STATS('test', 't1');
Query OK, 0 rows affected