Lock and unlock table/schema statistics

Command

-- Lock (the auto stats job and normal GATHER calls no longer touch them):
EXEC DBMS_STATS.LOCK_TABLE_STATS(ownname => 'CUSTOMER_APP', tabname => 'ORDERS');
EXEC DBMS_STATS.LOCK_SCHEMA_STATS(ownname => 'CUSTOMER_APP');
-- A single partition only: DBMS_STATS.LOCK_PARTITION_STATS('CUSTOMER_APP', 'SALE_PART', 'P2026_H1');

-- Locked objects:
SELECT owner, table_name, partition_name, stattype_locked
FROM   dba_tab_statistics
WHERE  owner = 'CUSTOMER_APP' AND stattype_locked IS NOT NULL;

-- Deliberate gathering while locked (instead of ORA-20005):
EXEC DBMS_STATS.GATHER_TABLE_STATS('CUSTOMER_APP', 'ORDERS', force => TRUE);

-- Unlock:
EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS(ownname => 'CUSTOMER_APP');
EXEC DBMS_STATS.UNLOCK_TABLE_STATS(ownname => 'CUSTOMER_APP', tabname => 'ORDERS');

What it does

Locks statistics against change; the automatic task skips locked objects and a table-level GATHER call raises ORA-20005. force=>TRUE gathers without removing the lock.

When to use it

To keep representative statistics on staging/volatile tables that are emptied and refilled during the day, to protect a critical table's plans from statistics changes, or to preserve values set with DBMS_STATS.SET_TABLE_STATS.

Caution

No license required. Locked statistics drift from reality over time and cause cardinality errors; report locked objects regularly. A schema lock does not cover tables added to the schema later. On a locked table, CREATE/REBUILD INDEX does not gather index statistics automatically either.