ORA-38029: Statistics Locked - Unlock Oracle Table Stats

ORA-38029: Object Statistics Are Locked

Error Overview

Error Text: ORA-38029: object statistics are locked

ORA-38029 occurs when DBMS_STATS attempts to gather, delete, or modify statistics on a table, partition, or schema object whose statistics have been explicitly locked. Statistics locking prevents any automated or manual statistics gathering from overwriting the locked values, preserving a specific plan-stable set of statistics.

The error fires when DBMS_STATS.GATHER_TABLE_STATS, DBMS_STATS.GATHER_SCHEMA_STATS, DBMS_STATS.DELETE_TABLE_STATS, or any other statistics-modifying procedure is called against a locked object. The lock was placed deliberately using DBMS_STATS.LOCK_TABLE_STATS or DBMS_STATS.LOCK_SCHEMA_STATS.

Locked statistics are a legitimate and useful tool for plan stability, but they become a problem when:

• DBAs forget they locked the stats and wonder why gathering does nothing
• Tables grow significantly but the optimizer uses stale locked statistics
• Automated statistics jobs silently skip locked objects without notification

Common Causes

1. Deliberately Locked Statistics for Plan Stability

• DBA locked statistics after tuning a critical query to preserve the execution plan
• Application vendor recommended locking statistics on specific tables
• Statistics were locked during a UAT period and never unlocked in production

2. Oracle Internal Objects With Locked Statistics

• Oracle itself locks statistics on certain fixed tables and internal dictionary objects
• Attempts to gather stats on X$ tables or certain SYS objects trigger ORA-38029
• Statistics on dictionary objects locked after DBMS_STATS.LOCK_SCHEMA_STATS('SYS')

3. Schema-Level Lock Applied Broadly

• DBMS_STATS.LOCK_SCHEMA_STATS was called on an entire schema
• All objects in the schema are now locked, including those that grow
• Developer or DBA applied the lock during testing and forgot to reverse it

4. Automated Statistics Gathering Job Encountering Locked Objects

• Oracle’s nightly GATHER_STATS_JOB (or AUTO_TASKS) silently skips locked tables
• No visible error in a scheduled job — statistics become stale over time
• Long-running queries slow down as table data changes but statistics remain old

5. Statistics Locked After Import/Export

• Data Pump impdp with INCLUDE=STATISTICS imported locked statistics from the source
• Statistics were locked on the source database as part of production stabilization
• Target database now has locked statistics that do not reflect actual imported data volumes

Diagnostic Queries

Find All Objects With Locked Statistics

-- Tables with locked statistics:
SELECT
  owner,
  table_name,
  stattype_locked,
  last_analyzed,
  num_rows,
  blocks,
  avg_row_len
FROM dba_tab_statistics
WHERE stattype_locked IS NOT NULL
ORDER BY owner, table_name;

-- Count of locked objects per schema:
SELECT
  owner,
  COUNT(*) AS locked_tables,
  MIN(last_analyzed) AS oldest_stats,
  MAX(last_analyzed) AS newest_stats
FROM dba_tab_statistics
WHERE stattype_locked IS NOT NULL
GROUP BY owner
ORDER BY locked_tables DESC;

Check Staleness of Locked Statistics

-- Locked tables where statistics are stale (more than 10% row change since last analysis):
SELECT
  s.owner,
  s.table_name,
  s.stattype_locked,
  s.last_analyzed,
  s.num_rows              AS stats_row_count,
  t.num_rows              AS current_approx_rows,
  ABS(NVL(t.num_rows, 0) - NVL(s.num_rows, 0))
    / NULLIF(s.num_rows, 0) * 100 AS pct_change
FROM dba_tab_statistics s
JOIN dba_tables t ON s.owner = t.owner AND s.table_name = t.table_name
WHERE s.stattype_locked IS NOT NULL
  AND s.last_analyzed < SYSDATE - 30  -- Not analyzed in the last 30 days
ORDER BY pct_change DESC NULLS LAST;

-- Monitoring statistics table for locked objects:
SELECT
  owner,
  table_name,
  stattype_locked,
  TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') AS last_analyzed,
  stale_stats
FROM dba_tab_statistics
WHERE stattype_locked IS NOT NULL
  AND (stale_stats = 'YES' OR last_analyzed < SYSDATE - 30)
ORDER BY last_analyzed ASC NULLS FIRST;

Find Column-Level and Index-Level Locks

-- Column statistics locks:
SELECT
  owner,
  table_name,
  column_name,
  stattype_locked,
  last_analyzed,
  num_distinct,
  num_nulls,
  density
FROM dba_col_statistics
WHERE stattype_locked IS NOT NULL
ORDER BY owner, table_name, column_name;

-- Index statistics locks:
SELECT
  owner,
  index_name,
  table_name,
  stattype_locked,
  last_analyzed,
  num_rows,
  leaf_blocks,
  distinct_keys
FROM dba_ind_statistics
WHERE stattype_locked IS NOT NULL
ORDER BY owner, table_name;

Check the Context of When the Lock Was Applied

-- AWR snapshot history for tables with locked stats — see recent plan changes:
SELECT
  ss.snap_id,
  TO_CHAR(ss.begin_interval_time, 'YYYY-MM-DD HH24:MI:SS') AS snap_time,
  sp.object_name,
  sp.old_plan_hash_value,
  sp.new_plan_hash_value
FROM dba_hist_sql_plan_baseline_history sp
JOIN dba_hist_snapshot ss ON sp.snap_id = ss.snap_id
WHERE sp.object_name = UPPER('&table_name')
ORDER BY snap_time DESC
FETCH FIRST 20 ROWS ONLY;

-- Find all pending statistics (gathered but not published — if pending stats mode is on):
SELECT owner, table_name, num_rows, last_analyzed
FROM dba_tab_pending_stats
ORDER BY owner, table_name;

Step-by-Step Resolution

1. Confirm the Object Has Locked Statistics

SELECT owner, table_name, stattype_locked, last_analyzed
FROM dba_tab_statistics
WHERE owner = UPPER('&schema')
  AND table_name = UPPER('&table_name');

-- stattype_locked = 'ALL' means both data and column stats are locked.
-- stattype_locked = 'DATA' means only table/partition-level stats are locked.
-- stattype_locked = 'CACHE' means buffer cache statistics are locked.

2. Unlock Statistics on a Specific Table

-- Unlock table statistics:
BEGIN
  DBMS_STATS.UNLOCK_TABLE_STATS(
    ownname => 'SCHEMA_NAME',
    tabname => 'TABLE_NAME'
  );
END;
/

-- Verify unlock:
SELECT stattype_locked FROM dba_tab_statistics
WHERE owner = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME';
-- Should return NULL.

3. Gather Fresh Statistics After Unlocking

-- Gather statistics with optimal settings:
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'SCHEMA_NAME',
    tabname          => 'TABLE_NAME',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE,
    degree           => DBMS_STATS.AUTO_DEGREE,
    no_invalidate    => FALSE
  );
END;
/

-- Confirm updated statistics:
SELECT table_name, num_rows, last_analyzed
FROM dba_tables
WHERE owner = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME';

4. Re-Lock Statistics if Plan Stability Is Still Required

-- After gathering fresh, accurate statistics, re-lock if needed:
BEGIN
  DBMS_STATS.LOCK_TABLE_STATS(
    ownname => 'SCHEMA_NAME',
    tabname => 'TABLE_NAME'
  );
END;
/

5. Unlock an Entire Schema

-- Unlock all statistics for a schema:
BEGIN
  DBMS_STATS.UNLOCK_SCHEMA_STATS(ownname => 'SCHEMA_NAME');
END;
/

-- Then gather fresh stats for the whole schema:
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname          => 'SCHEMA_NAME',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE,
    degree           => DBMS_STATS.AUTO_DEGREE
  );
END;
/

6. Handle Locked Statistics During Data Pump Import

-- After impdp, unlock all stats in the target schema before gathering fresh ones:
BEGIN
  DBMS_STATS.UNLOCK_SCHEMA_STATS(ownname => 'IMPORTED_SCHEMA');
END;
/

-- Delete the imported statistics (which reflect source volumes, not target):
BEGIN
  DBMS_STATS.DELETE_SCHEMA_STATS(ownname => 'IMPORTED_SCHEMA');
END;
/

-- Gather fresh statistics based on actual imported data:
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname          => 'IMPORTED_SCHEMA',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    cascade          => TRUE
  );
END;
/

7. Unlock Only Stale Locked Statistics (Selective Approach)

-- Unlock and regather only tables that are stale (>10% change in rows):
BEGIN
  FOR r IN (
    SELECT s.owner, s.table_name
    FROM dba_tab_statistics s
    WHERE s.stattype_locked IS NOT NULL
      AND (s.stale_stats = 'YES' OR s.last_analyzed < SYSDATE - 90)
  ) LOOP
    DBMS_STATS.UNLOCK_TABLE_STATS(r.owner, r.table_name);
    DBMS_STATS.GATHER_TABLE_STATS(
      ownname => r.owner,
      tabname => r.table_name,
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
      cascade => TRUE
    );
    DBMS_STATS.LOCK_TABLE_STATS(r.owner, r.table_name);
    DBMS_OUTPUT.PUT_LINE('Refreshed locked stats: ' || r.owner || '.' || r.table_name);
  END LOOP;
END;
/

Prevention Strategies

1. Document Every Statistics Lock

-- Maintain a log of locked statistics with the reason:
CREATE TABLE dba_stats_lock_log (
  schema_name  VARCHAR2(128),
  table_name   VARCHAR2(128),
  locked_by    VARCHAR2(128),
  locked_date  DATE,
  reason       VARCHAR2(500),
  review_date  DATE
);

-- Log the lock when applying it:
INSERT INTO dba_stats_lock_log
VALUES ('SCHEMA', 'TABLE_NAME', USER, SYSDATE,
        'Locking for Q4 batch run stability', SYSDATE + 90);
COMMIT;

2. Set a Review Policy for Locked Statistics

-- Report on statistics locks that are past review date or very old:
SELECT
  s.owner,
  s.table_name,
  s.stattype_locked,
  s.last_analyzed,
  l.reason,
  l.review_date
FROM dba_tab_statistics s
LEFT JOIN dba_stats_lock_log l ON s.owner = l.schema_name AND s.table_name = l.table_name
WHERE s.stattype_locked IS NOT NULL
  AND (l.review_date < SYSDATE OR l.review_date IS NULL OR s.last_analyzed < SYSDATE - 90)
ORDER BY s.last_analyzed ASC NULLS FIRST;

3. Prefer SQL Plan Baselines Over Statistics Locking

-- SQL Plan Baselines offer plan stability without stale statistics risk:
-- Capture a known-good plan:
DECLARE
  l_plans NUMBER;
BEGIN
  l_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => '&sql_id'
  );
  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_plans);
END;
/

-- SQL Plan Management preserves the plan without locking statistics.

4. Monitor Automated Statistics Job Skipping Locked Objects

-- Check the automated stats job history for skipped objects:
SELECT
  target,
  status,
  TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
  duration
FROM dba_optstat_operations
WHERE operation IN ('gather_table_stats', 'gather_schema_stats')
  AND status = 'SKIPPED'
  AND start_time > SYSDATE - 7
ORDER BY start_time DESC;

Related Errors

• ORA-38028 - Cannot lock statistics (prerequisite issue)
• ORA-20005 - Object statistics locked (older version error code)
• ORA-06512 - At line (PL/SQL stack trace)

Emergency Response

Quick Unlock and Regather

-- 1. Unlock:
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');

-- 2. Gather:
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME', cascade => TRUE);

-- 3. Verify:
SELECT table_name, num_rows, last_analyzed, stattype_locked
FROM dba_tables
WHERE owner = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME';

List All Locked Tables for a Quick Assessment

SELECT owner, table_name, stattype_locked, last_analyzed
FROM dba_tab_statistics
WHERE stattype_locked IS NOT NULL
ORDER BY last_analyzed ASC NULLS FIRST
FETCH FIRST 20 ROWS ONLY;

If You Need to Suppress ORA-38029 Without Unlocking

-- Use FORCE option (21c+) to overwrite locked statistics:
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCHEMA_NAME',
    tabname => 'TABLE_NAME',
    force   => TRUE  -- Gathers even on locked tables; lock remains after
  );
END;
/