Oracle Locks Temporary Table statistics

The statistics of the global temporary table cannot be collected, if it is collected, its statistic is definitely 0, it will cause the execution plan is not allowed, so we should lock up its statistic information and prevent the system from collecting automatically.

-- Unlock the table statistics First, then delete the table statistics, and finally lock the table statistics

Declare

V_sql varchar2 (a);

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;

exit when rowlist1%notfound;

Endloop;

Loop

fetch rowList2 into v_sql;

Executeimmediate v_sql;

exit when rowlist2%notfound;

Endloop;

Loop

fetch rowList3 into v_sql;

Executeimmediate v_sql;

exit when rowlist3%notfound;

Endloop;

close rowList1;

close rowList2;

close rowList3;

End ;

-- stattype_locked= ' All ' It means that the statistics of the table are locked

Select s.table_name,s.stattype_locked from user_tab_statistics s where s.stattype_locked= ' All ' ;

Oracle Locks Temporary Table statistics