Database Reference
In-Depth Information
To know which tables object statistics are locked for, you can use a query like the following:
SQL> SELECT table_name
2 FROM user_tab_statistics
3 WHERE stattype_locked IS NOT NULL;
TABLE_NAME
----------
T
Be aware that the
dbms_stats
package isn't the only one that gathers object statistics and, therefore, is affected by
locks on object statistics. In fact, the
ANALYZE
,
CREATE INDEX
, and
ALTER INDEX
statements—as well as, from version
12.1 onward, CTAS statements and direct-path inserts into empty tables—also gather object statistics. The first gathers
object statistics when it's explicitly instructed to do so. But, as stated at the beginning of this chapter, you should no
longer use it for that purpose. The others automatically gather object statistics while carrying out the task they're
design for. This is useful because the overhead associated with the gathering of statistics while executing these SQL
statements is negligible. Consequently, when the object statistics of a table are locked, these SQL statements may
behave differently or even fail. The following example, a continuation of the previous one, shows this behavior:
SQL> ANALYZE TABLE t COMPUTE STATISTICS;
ANALYZE TABLE t COMPUTE STATISTICS
*
ERROR at line 1:
ORA-38029: object statistics are locked
SQL> ANALYZE TABLE t VALIDATE STRUCTURE;
SQL> ALTER INDEX t_pk REBUILD COMPUTE STATISTICS;
ALTER INDEX t_pk REBUILD COMPUTE STATISTICS
*
ERROR at line 1:
ORA-38029: object statistics are locked
SQL> ALTER INDEX t_pk REBUILD;
SQL> CREATE INDEX t_i ON t (pad) COMPUTE STATISTICS;
CREATE INDEX t_i ON t (pad) COMPUTE STATISTICS
*
ERROR at line 1:
ORA-38029: object statistics are locked
SQL> CREATE INDEX t_i ON t (pad);
Notice that the SQL statement
CREATE INDEX
and
ALTER INDEX
fails only when the deprecated
COMPUTE
STATISTICS
clause is specified. Because these SQL statements gather object statistics by default, it's pointless to use
the
COMPUTE STATISTICS
clause.
