Oracle 锁定临时表统计信息

  全局临时表的统计信息是不能被收集的，如果被收集，它的统计信息肯定为0，会造成执行计划不准，所以要锁定它的统计信息，禁止系统自动收集。

--先解锁表的统计信息，然后删除表的统计信息，最后锁住表的统计信息

declare

  v_sql varchar2(500);

  cursor rowList1 is

   select‘begin dbms_stats.unlock_table_stats(user,‘‘‘ || table_name || ‘‘‘); end;‘

     from user_tables s

   where s.temporary = ‘Y‘; 

  cursor rowList2 is

   select‘begin dbms_stats.delete_table_stats(user,‘‘‘ || table_name || ‘‘‘); end;‘

     from user_tables s

   where s.temporary = ‘Y‘;

  cursor rowList3 is

   select‘begin dbms_stats.lock_table_stats(user,‘‘‘ || table_name || ‘‘‘); end;‘

     from user_tables s

   where s.temporary = ‘Y‘;  

begin

  open rowList1;

  open rowList2;

  open rowList3;

  loop

    fetch rowList1  into v_sql;

       executeimmediate v_sql;

    exitwhen rowList1%notfound;

  endloop;

  loop

    fetch rowList2  into v_sql;

       executeimmediate v_sql;

    exitwhen rowList2%notfound;

  endloop;

  loop

    fetch rowList3  into v_sql;

       executeimmediate v_sql;

    exitwhen rowList3%notfound;

  endloop;   

  close rowList1;

  close rowList2;

  close rowList3;

end;

-- STATTYPE_LOCKED=‘ALL‘意思是表的统计信息被锁

select s.table_name,s.STATTYPE_LOCKEDfrom user_TAB_STATISTICS s  where s.STATTYPE_LOCKED=‘ALL‘;