How to delete table statistics in Oracle

Delete the optimizer statistics on a table.

With no statistics the optimizer falls back to dynamic sampling or defaults, which usually hurts plans. Delete stats only deliberately (for example before a re-gather with different settings), and consider locking instead.

-- Check current stats first (read-only)
SELECT num_rows,
       TO_CHAR(last_analyzed,'YYYY-MM-DD HH24:MI') AS last_analyzed
FROM   dba_tables
WHERE  owner = UPPER('&&owner')
  AND  table_name = UPPER('&&table_name');

BEGIN
  DBMS_STATS.DELETE_TABLE_STATS(ownname => '&owner', tabname => '&table_name');
END;
/

-- Verify (read-only): num_rows should be NULL now
SELECT num_rows, last_analyzed
FROM   dba_tables
WHERE  owner = UPPER('&owner')
  AND  table_name = UPPER('&table_name');