Saturday, 13 June 2015

Lock / Unlock on a table particular table

tried to gather stats on a table using

exec dbms_stats.gather_table_stats(‘TABLE_OWNER’, ‘TABLE_NAME’);

and got the below error message…
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 1

At times, we don’t want to gather stats on some critical tables automatically. So, we can lock those tables from getting stats gathered by locking them.

To lock stats on a particular table
SQL> exec dbms_stats.lock_table_stats(‘TABLE_OWNER’, ‘TABLE_NAME’);

So, for this scenario above, I first unlocked the table using below query.
To unlock stats on a particular table

SQL> exec dbms_stats.unlock_table_stats(‘TABLE_OWNER’, ‘TABLE_NAME’);
Don’t forget to lock the table’s stats once you are done with your work.

No comments:

Post a Comment