Purge Stats for column, partition, table or schema in Oracle

Delete the Schema stats

exec dbms_stats.delete_schema_Stats(OWNNAME=>'HR');


Check and Purge Table Stats

-- Check Table stats
SELECT table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED"
FROM DBA_TABLES WHERE owner='HR' and table_name = 'TRAN';

-- Delete the Table stats
EXEC DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'HR', TABNAME=>'TRAN');


Check and Purge table partition Stats

-- Check partition stats of table
col table_name for a10
col partition_name for a10
SELECT table_name, partition_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED"
FROM DBA_TAB_PARTITIONS
WHERE table_name='TRAN' AND partition_name like 'TRAN201701'

--Delete the partition Stats
EXEC DBMS_STATS.DELETE_TABLE_STATS (OWNNAME => 'HR', TABNAME => 'TRAN',PARTNAME => 'TRAN201701');


Check and Purge the Column Stats of Table

--Check stats of column in table
Col table_name for a10
col column_name for a10
col lastanalyzed for a18
SELECT table_name,column_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" from DBA_TAB_COL_STATISTICS where table_name = 'TRAN';

-- Drop the column stats
EXEC DBMS_STATS.DELETE_COLUMN_STATS(OWNNAME=>'HR', TABNAME=>'TRAN', COLNAME=>'TRANID', CASCADE_PARTS=>TRUE, COL_STAT_TYPE=>'HISTOGRAM');