Oracle - Extended Statistics

> Database > Oracle Database

1 - 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;