Database Reference
In-Depth Information
Naturally, it's also possible to remove the locks, which you can do by executing one of the following procedures:
•
unlock_schema_stats
removes locks from object statistics for all tables belonging to a
schema. Even locks that were set with the
lock_table_stats
procedure are removed:
dbms_stats.unlock_schema_stats(ownname => user)
•
unlock_table_stats
removes the lock from object statistics for a single table:
dbms_stats.unlock_table_stats(ownname => user, tabname => 'T')
To execute these four procedures, you need to be connected as owner or have the
analyze any
system privilege.
When the object statistics of a table are locked, all the object statistics related to that table (including table
statistics, column statistics, histograms, and index statistics on all dependent indexes) are considered to be locked.
When the object statistics of a table are locked, procedures in the
dbms_stats
package that modify object
statistics of a single table (for example
gather_table_stats
) raise an error (ORA-20005). In contrast, procedures that
operate on multiple tables (for example
gather_schema_stats
) skip the locked table. Most procedures that modify
object statistics can override the lock by setting the
force
parameter to
TRUE
. The following example demonstrates
that behavior (a full example is provided in the
lock_statistics.sql
script):
SQL> BEGIN
2 dbms_stats.lock_schema_stats(ownname => user);
3 END;
4 /
SQL> BEGIN
2 dbms_stats.gather_schema_stats(ownname => user);
3 END;
4 /
SQL> BEGIN
2 dbms_stats.gather_table_stats(ownname => user,
3 tabname => 'T');
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 33859
ORA-06512: at line 2
SQL> BEGIN
2 dbms_stats.gather_table_stats(ownname => user,
3 tabname => 'T',
4 force => TRUE);
5 END;
6 /
