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

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

Today I analyze a table and get the error messages below

ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 23829
ORA-06512: at “SYS.DBMS_STATS”, line 23880
ORA-06512: at line 2

Solution:

You can see list of all locked tables in a schema by running following query:

select table_name, stattype_locked from dba_tab_statistics where owner = ‘<scheama>’ and stattype_locked is not null;

To generate unlock statement for all tables in the schema you can use following,

select ‘exec DBMS_STATS.UNLOCK_TABLE_STATS (”’|| owner ||”’,”’|| table_name ||”’);’ from dba_tab_statistics where owner = ‘<schema>” and stattype_locked is not null;

 

Advertisements
Categories: ORA-XXX
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: