How to Transfer Histograms or Extended Column Statistics between Databases (Doc ID 2340023.1)

利用以下脚本可以在数据库迁移中对原库中的表的直方图进行收集​

copy_hist.sql

PROMPT ======================================================================================
PROMPT Generate script to create extended statistics based on an example schema
PROMPT ======================================================================================
set serveroutput on
set verify off
set feedback off
set linesize 1000
set trims on
set serveroutput on

accept schema prompt 'Enter the name of the schema to copy: '

spool gen_copy_ext.sql
declare
  cursor extlist is
     select table_name,extension,creator,owner 
     from dba_stat_extensions
     where owner = upper('&schema')
     order by creator,table_name,extension_name;
begin
  dbms_output.put_line('var r VARCHAR2(50)');
  for c in extlist
  loop
    dbms_output.put('exec :r := dbms_stats.create_extended_stats('''||c.owner||''','''||c.table_name||''','''||c.extension||''')');
    dbms_output.put_line(' /* '||c.creator||' */');
  end loop;
end;
/
spool off

​copy_hist_a.sql

PROMPT =======================================================================================
PROMPT Generate a script to create table histogram preferences based on example schema.
PROMPT AUTO histogram creation is enabled for columns that don't already have a histogram.
PROMPT This enables new histograms to be created if skews are found.
PROMPT =======================================================================================
whenever sqlerror exit

set serveroutput on
set verify off
set feedback off
set linesize 1000
set trims on

accept schema prompt 'Enter the name of the schema to copy: '

PROMPT Table histogram script...

spool gen_copy_hist_a.sql
declare
  n number(10) := 0;
  tname varchar2(100);
  cursor tlist is
    select distinct cs.owner,cs.table_name
    from  dba_tab_col_statistics cs,
          dba_tables t
    where cs.histogram is not null
    and   cs.histogram != 'NONE'
    and   cs.owner = upper('&schema')
    and   t.owner = upper('&schema')
    and   t.table_name = cs.table_name
    order by cs.owner,cs.table_name;
  cursor collist is
    select column_name
    from  dba_tab_col_statistics
    where histogram is not null
    and   histogram != 'NONE'
    and   owner = upper('&schema')
    and   table_name = tname
    order by owner,table_name,column_name;
begin
  dbms_output.put_line('PROMPT NOTE! It is assumed that global or schema METHOD_OPT is its default value.');
  dbms_output.put_line('PROMPT For example:');
  dbms_output.put_line('PROMPT    EXEC DBMS_STATS.SET_GLOBAL_PREFS(''METHOD_OPT'',''FOR ALL COLUMNS SIZE AUTO'')');
  dbms_output.put_line('PROMPT Alternatively:');
  dbms_output.put_line('PROMPT    EXEC DBMS_STATS.SET_SCHEMA_PREFS(''&schema'',''METHOD_OPT'',''FOR ALL COLUMNS SIZE AUTO'')');
  for t in tlist
  loop
     dbms_output.put('exec dbms_stats.set_table_prefs('''||t.owner||''','''||t.table_name||''',''METHOD_OPT'',');
     dbms_output.put('''FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 254 ');
     tname := t.table_name;
     for c in collist
     loop
        dbms_output.put(c.column_name||' ');
     end loop;
     dbms_output.put(''')');
     dbms_output.put_line('');
  end loop;
end;
/
spool off

copy_hist

PROMPT ======================================================================================
PROMPT Generate a script to create table histogram preferences based on example schema
PROMPT ======================================================================================
whenever sqlerror exit

set serveroutput on
set verify off
set feedback off
set linesize 1000
set trims on

accept schema prompt 'Enter the name of the schema to copy: '

PROMPT Table histogram script...

spool gen_copy_hist.sql
declare
  n number(10) := 0;
  tname varchar2(100);
  cursor tlist is
    select distinct cs.owner,cs.table_name
    from  dba_tab_col_statistics cs,
          dba_tables t
    where cs.histogram is not null
    and   cs.histogram != 'NONE'
    and   cs.owner = upper('&schema')
    and   t.owner = upper('&schema')
    and   t.table_name = cs.table_name
    order by cs.owner,cs.table_name;
  cursor collist is
    select column_name
    from  dba_tab_col_statistics
    where histogram is not null
    and   histogram != 'NONE'
    and   owner = upper('&schema')
    and   table_name = tname
    order by owner,table_name,column_name;
begin
  dbms_output.put_line('PROMPT NOTE! It is assumed that histograms are disabled for tables not included in this script.');
  dbms_output.put_line('PROMPT For example:');
  dbms_output.put_line('PROMPT    EXEC DBMS_STATS.SET_GLOBAL_PREFS(''METHOD_OPT'',''FOR ALL COLUMNS SIZE 1'')');
  dbms_output.put_line('PROMPT Alternatively:');
  dbms_output.put_line('PROMPT    EXEC DBMS_STATS.SET_SCHEMA_PREFS(''&schema'',''METHOD_OPT'',''FOR ALL COLUMNS SIZE 1'')');
  for t in tlist
  loop
     dbms_output.put('exec dbms_stats.set_table_prefs('''||t.owner||''','''||t.table_name||''',''METHOD_OPT'',');
     dbms_output.put('''FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 ');
     tname := t.table_name;
     for c in collist
     loop
        dbms_output.put(c.column_name||' ');
     end loop;
     dbms_output.put(''')');
     dbms_output.put_line('');
  end loop;
end;
/
spool off