While testing the AWS RDS environment, I ran across the issue that the statistics were locked:
begin DBMS_STATS.GATHER_TABLE_STATS ( ownname => '"DEAN"', tabname => '"table name"', estimate_percent => 5 ); end;
The error was:
Error starting at line : 6 in command - begin DBMS_STATS.GATHER_TABLE_STATS ( ownname => '"DEAN"', tabname => '"table name"', estimate_percent => 5 ); end; Error report - ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 34707 ORA-06512: at line 2
I checked with
select table_name, stattype_locked from dba_tab_statistics where table_name in ( select table_name from dba_tables where owner = 'DEAN');
The STATTYPE_LOCKED column contained the value of “ALL”. I was able to unlock this with
exec dbms_stats.unlock_table_stats('DEAN', 'table name');
This changed the value in the STATTYPE_LOCKED to “null” after which I was able to gather statistics.