Oracle - Extended Statistics

drop_extended_stats

Script to suppress in batch all extended statiistics

• get and convert data_default from a long to a lob

create table tmp_virt as 
select table_name, TO_LOB(data_default) as data_default
from all_tab_cols
where virtual_column = 'YES'
and owner = USER
;

• generate the drop_extended_stats script

select 'exec dbms_stats.drop_extended_stats(user, '''|| table_name || ''', ''' || dbms_lob.substr( data_default, 9999, 21 ) || ''');' 
from
tmp_virt;