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

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');

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.