Category Archives: Oracle

In this we are handling Oracle Database Administration and development task. If provide solution of ORA Errors and configuration Steps for setup in Oracle.

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