DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, February 9, 2016

Locking statistics for a table

You can lock the statistics for the table if you don't want it be analyzed automatically by scheduled jobs.

exec dbms_stats.lock_table_stats('&owner','&table name');
exec dbms_stats.lock_table_stats('GOLD','TEST');

To list all tables with locked statistics in a particular schema 

select table_name, stattype_locked from dba_tab_statistics where owner ='&owner' and stattype_locked is not null;

To unlock the stats of a particular table

exec DBMS_STATS.UNLOCK_TABLE_STATS('&owner','&table name');

To unlock the tables with locked stats of a particular schema

select 'exec DBMS_STATS.UNLOCK_TABLE_STATS ('''|| owner ||''','''|| table_name ||''');' from dba_tab_statistics where owner = '&owner' and stattype_locked is not null;

To Check 

when stats is not locked the value of stattype_locked is NULL and  ALL when stats is locked

SQL> SELECT TABLE_NAME , stattype_locked FROM dba_tab_statistics where owner = 'GOLD';
TABLE_NAME                STATT
------------------------------ -----
TEST                            ALL < --- LOCKED
TEST1                                      <  --- UNLOCKED


1 comment:

Sridevi K said...


Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.