Tag Archives: delete stats of objects

Delete stats of table, index, partition and column in Oracle

Delete stats of table, index, partition and column in Oracle

Check and drop the Stats of the table

-- Check the stats
col table_name for a15
SELECT table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" FROM DBA_TABLES WHERE owner='SYS' AND table_name='TEST';

-- Drop the stats
EXEC DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'SCOTT', TABNAME=>'EMP');

Check and drop the stats of partition in table

-- Check partition stats
Col table_name for a10
col partition_name for a10
col lastanalyzed for a18
SELECT table_name, partition_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" FROM DBA_TAB_PARTITIONS
WHERE table_name='EMP' AND partition_name='EMP201701';

-- Drop the partition stats
EXEC DBMS_STATS.DELETE_TABLE_STATS (OWNNAME => 'HR', TABNAME => 'EMP',PARTNAME => 'EMP201701');

Check and drop the stats of Column level in 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;

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

Check and drop the stats of Index

--Check index stats
SELECT table_name,index_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" FROM DBA_INDEXES;

-- Drop the index stats
EXEC DBMS_STATS.DELETE_INDEX_STATS('SCOTT', 'EMP_PK');