Oracle – How to Gather Stats for Table where stats of that table is locked.

Check Stats of Table :

–example : ‘DEPT’,’EMP’,’SALGRADE’
set linesize 300
set pagesize 100
col owner for a15
col LAST_ANALYZED for a30
col table_name for a40
select OWNER,TABLE_NAME , NUM_ROWS , BLOCKS , EMPTY_BLOCKS ,
CHAIN_CNT, AVG_ROW_LEN
CACHE, to_char(LAST_ANALYZED,’DD-MON-YYYY HH24:MI:SS’) LAST_ANALYZED
from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner=’&OWNER’;

Backup Existing Stats :

exec dbms_stats.create_stat_table ( ownname => ‘SCOTT’ , stattab => ‘DEPT_stat’ ) ;
exec dbms_stats.export_table_stats ( ownname => ‘SCOTT’ , stattab => ‘DEPT_stat’, tabname => ‘DEPT’, statid => ‘stats04252016’)
select count(*) from scott.DEPT_stat;

Check Table Stats Locked or not :

set linesize 300
col table_name for a40
SELECT stattype_locked,table_name,last_analyzed FROM dba_tab_statistics WHERE owner = ‘&OWNER’ and stattype_locked=’ALL’;

Unlock the Stats :

–exec dbms_stats.unlock_table_stats(‘SCOTT’, ‘DEPT’);
exec dbms_stats.unlock_table_stats(‘&OWNER’,’&TABLE’);

Gather Stats :

It’s example only.
Options to execute below command will change from Environment to Environment
exec dbms_stats.gather_table_stats(ownname=>’SCOTT’,tabname=>’DEPT’,estimate_percent=>100, cascade=> TRUE, DEGREE => 16 ,granularity => ‘ALL’ , method_opt=>’for all columns size auto’);

Validate new stats :

set linesize 300
set pagesize 100
col owner for a15
col LAST_ANALYZED for a30

select OWNER,TABLE_NAME , NUM_ROWS , BLOCKS , EMPTY_BLOCKS ,
CHAIN_CNT, AVG_ROW_LEN
CACHE, to_char(LAST_ANALYZED,’DD-MON-YYYY HH24:MI:SS’) LAST_ANALYZED
from dba_tables where table_name in (&Selected_Tables_use_Comma) and owner=’&OWNER’;

Lock the Stats :

exec dbms_stats.lock_table_stats(‘SCOTT’, ‘DEPT’);

Check Table Stats Locked or not :

set linesize 300
col table_name for a40
SELECT stattype_locked,table_name,last_analyzed FROM dba_tab_statistics WHERE owner = ‘&OWNER’ and stattype_locked=’ALL’;

Import old Stats :

exec dbms_stats.import_table_stats(‘SCOTT’,’DEPT’,NULL,’DEPT_STAT’,’stats04252016′,CASCADE=>TRUE);

Lock the Stats :

exec dbms_stats.lock_table_stats(‘SCOTT’, ‘DEPT’);