Handling Locked Statistics in Gather Schema Statistics

Issue

During the execution of the Gather Schema Statistics concurrent program, the log file reported that certain tables had locked statistics, such as:

• WF_NOTIFICATION_OUT

• WF_NOTIFICATION_IN

This prevented the program from collecting fresh statistics.

---

Cause

The Gather Schema Statistics process failed because:

1. Some tables had their statistics locked, and

2. The FND_STATS_HIST table contained large volumes of data, impacting performance.

---

How to Identify Locked Statistics

Use the query below to find tables with locked statistics:

select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null;

This will list all objects where statistics are currently locked.

---

Solution

1. Unlock All Tables in a Schema

If many tables are locked, you can unlock the entire schema at once:

exec dbms_stats.unlock_schema_stats('schema_owner');

---

2. Unlock Statistics for a Specific Table

To unlock statistics for a single table, run:

exec dbms_stats.unlock_table_stats('table_owner', 'table_name');

Example:

exec dbms_stats.unlock_table_stats('APPLSYS', 'WF_NOTIFICATION_OUT');