DBMS_STATS.GATHER_TABLE_STATS gives ORA-20005: object statistics are locked (stattype = ALL)

##########################
## Error
##########################

ORA-20005: object statistics are locked (stattype = ALL)

### Full Error

SQL> exec dbms_stats.gather_table_Stats('STHIRUNAVUKKARASU','T1');
BEGIN dbms_stats.gather_table_Stats('STHIRUNAVUKKARASU','T1'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 23154
ORA-06512: at "SYS.DBMS_STATS", line 23205
ORA-06512: at line 1

##########################
#  Error Occurred
##########################

Error occured while trying to gather statistics for a table is run in 11.2.0.3.5 version

##########################
## Command Executed
##########################

exec dbms_stats.gather_table_Stats('STHIRUNAVUKKARASU','T1');

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check the table Statistics
#########################################

set lines 200
Select owner,table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') from dba_tables where table_name='T1';

OWNER                          TABLE_NAME                     TO_CHAR(LAST_ANALYZED,'DD-MON
------------------------------ ------------------------------ -----------------------------
STHIRUNAVUKKARASU              T1                             10-JUL-2014 07:24:38

#### Check for table with Locked Statistics

SQL> select owner,table_name,stattype_locked from dba_tab_statistics where stattype_locked is not null and owner='STHIRUNAVUKKARASU';

OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
STHIRUNAVUKKARASU              T1                             ALL

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

Error is occuring when stats for a single table is being gathered.

From Step 1), we can see Statistics has been locked for the table. Usually application developers or DBA's lock statistics for some table such that
the path oracle uses to fetch records from the table will not get changed.

=====================================================================================================================

##########################
## Solution
##########################

#### In case if statistics for the table needs to be gathered, it can be done in 2 ways..

=====================================================================================================================
a) Gather Statistics with Force option
=====================================================================================================================

exec dbms_Stats.gather_table_stats(ownname=>'STHIRUNAVUKKARASU',tabname=>'T1',force=>TRUE);

SQL> exec dbms_Stats.gather_table_stats(ownname=>'STHIRUNAVUKKARASU',tabname=>'T1',force=>TRUE);
PL/SQL procedure successfully completed.
SQL>

Select owner,table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') from dba_tables where table_name='T1';

OWNER                          TABLE_NAME                     TO_CHAR(LAST_ANALYZED,'DD-MON
------------------------------ ------------------------------ -----------------------------
STHIRUNAVUKKARASU              T1                             10-JUL-2014 07:26:16

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

OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
STHIRUNAVUKKARASU              T1                             ALL

=====================================================================================================================
b) Unlock the table stats and run normal stats gather on that table.
=====================================================================================================================

exec dbms_stats.unlock_table_Stats('STHIRUNAVUKKARASU','T1');

SQL> exec dbms_stats.unlock_table_Stats('STHIRUNAVUKKARASU','T1');
PL/SQL procedure successfully completed.

SQL> select owner,table_name,stattype_locked from dba_tab_statistics where stattype_locked is not null and owner='STHIRUNAVUKKARASU';

no rows selected

SQL>

exec dbms_stats.gather_table_Stats('STHIRUNAVUKKARASU','T1');

SQL> exec dbms_stats.gather_table_Stats('STHIRUNAVUKKARASU','T1');
PL/SQL procedure successfully completed.

SQL>

SQL> Select owner,table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') from dba_tables where table_name='T1';

OWNER                          TABLE_NAME                     TO_CHAR(LAST_ANALYZED,'DD-MON
------------------------------ ------------------------------ -----------------------------
STHIRUNAVUKKARASU              T1                             10-JUL-2014 07:28:18

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================