Delete Histograms on a specific Schema

Step 1: Script to Check Histograms on Schema 

Following script would have to be executed in the schema as the schema owner.

select distinct table_name

from

(

select table_name from user_tab_columns where histogram!='NONE'

)

If the above script return rows, then proceed to run the below step 2.

Step 2: Script to Remove Histograms on a Schema 

Following script would have to be executed in the schema as the schema owner, if there are any rows returned from the step 1.

declare

cursor cur_tables is

select distinct table_name

from

(

select table_name from user_tab_columns where histogram!='NONE'

);

begin

for rec_tables in cur_tables

loop

dbms_stats.gather_table_stats(ownname=>'<SCHEMA_OWNER>',tabname=>rec_tables.table_name,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',CASCADE=>TRUE,DEGREE=>2,ESTIMATE_PERCENT=>NULL);

end loop;

end;