ISSUES
When trying to collect a table statistics, we get “ORA-20005: object statistics are locked (stattype = ALL)” as below:
SQL> exec dbms_stats.gather_table_stats(ownname=>'TESTUSER',tabname=>'TEST_TABLE'); BEGIN dbms_stats.gather_table_stats(ownname=>'TESTUSER',tabname=>'TEST_TABLE'); END; * ERROR at line 1: ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 24281 ORA-06512: at "SYS.DBMS_STATS", line 24332 ORA-06512: at line 1
HOW IS the STATS LOCKED ?
1) DBMS_STATS.LOCK_[SCHEMA|TABLE]_STATS has been used to lock statistics on the table.
SQL> exec dbms_stats.unlock_table_stats('TESTUSER','TEST_TABLE');
PL/SQL procedure successfully completed.
SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED
from dba_tab_statistics
where OWNER='TESTUSER'
and TABLE_NAME='TEST_TABLE';
OWNER TABLE_NAME STATT
-------- ----------- --------
TESTUSER TEST_TABLE
SQL> exec dbms_stats.lock_table_stats('TESTUSER','TEST_TABLE');
PL/SQL procedure successfully completed.
SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED
from dba_tab_statistics
where OWNER='TESTUSER' and TABLE_NAME='TEST_TABLE';
OWNER TABLE_NAME STATT
--------- ----------- -----
TESTUSER TEST_TABLE ALL
2) Using data pump import (impdp) to import a table without data results in the table’s statistics being locked in 10gR2 and 11gR2.
a. Unlock statistics on the previous table:
SQL> exec dbms_stats.unlock_table_stats('TESTUSER','TEST_TABLE');
PL/SQL procedure successfully completed.
SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED
from dba_tab_statistics
where OWNER='TESTUSER' and TABLE_NAME='TEST_TABLE';
OWNER TABLE_NAME STATTYPE_LOCKED
--------- ----------- ---------------
TESTUSER TEST_TABLE
b. Export table TESTUSER.TEST_TABLE.
$ expdp / directory=EXPP_DIR dumpfile=testuser.TEST_TABLE.dmp tables=testuser.TEST_TABLE
Export: Release 11.2.0.4.0 -Production on Mon Nov 10 13:54:50 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options
Starting "OPS$ORACLE"."SYS_EXPORT_TABLE_01": /******** directory=EXPDP_DIR dumpfile=testuser.TEST_TABLE.dmp tables=testuser.TEST_TABLE
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TESTUSER"."TEST_TABLE" 11.46 KB 1 rows
Master table "OPS$ORACLE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************
Dump file set for OPS$ORACLE.SYS_EXPORT_TABLE_01 is: /EXPDP_DIR/testuser.TEST_TABLE.dmp
Job "OPS$ORACLE"."SYS_EXPORT_TABLE_01" successfully completed at Mon Nov 10 13:55:13 2014 elapsed 0 00:00:16
c. Drop tablete TESTUSER.TEST_TABLE, and import with CONTENT=METADATA_ONLY, so the table will have zero records.
SQL> drop table TESTUSER.TEST_TABLE; Table dropped. $ impdp / directory=EXPDP_DIR dumpfile=TESTUSER.TEST_TABLE.dmp tables=TESTUSER.TEST_TABLE content=METADATA_ONLY Import: Release 11.2.0.4.0 -Production on Mon Nov 10 13:59:07 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters and Automatic Storage Management options Master table "OPS$ORACLE"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "OPS$ORACLE"."SYS_IMPORT_TABLE_01": /******** directory=EXPDP_DIR dumpfile=TESTUSER.TEST_TABLE.dmp tables=TESTUSER.TEST_TABLE content=METADATA_ONLY Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "OPS$ORACLE"."SYS_IMPORT_TABLE_01" successfully completed at Mon Nov 10 13:59:13 2014 elapsed 0 00:00:04
d. Check table stats locked.
SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED
from dba_tab_statistics
where OWNER='TESTUSER' and TABLE_NAME='TEST_TABLE'; OWNER TABLE_NAME STATT -------- ----------- ----- TESTUSER TEST_TABLE ALL
3) If the table is a queue table then the statistics are intended to be empty and locked so that dynamic sampling will be used due to the table’s volatility.
SQL> select OWNER,QUEUE_TABLE,TYPE
from dba_queue_tables
where owner ='TESTUSER'; OWNER QUEUE_TABLE --------- --------------- TESTUSER TEST_TABLE_QTAB SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED
from dba_tab_statistics
where OWNER='TESTUSER' and TABLE_NAME='TEST_TABLE_QTAB'; OWNER TABLE_NAME STATT -------- ----------------- ----- TESTUSER TEST_TABLE_QTAB ALL
SOLUTION
Here are a couple of solutions to prevent table stats from being locked:
1)If the table is a queue table then the statistics should remain empty and locked so that dynamic sampling is used due to the volatility of queue tables.
2)If the table is not a queue table, unlock the statistics using DBMS_STATS.UNLOCK_[SCHEMA|TABLE]_STATS or gather statistics on the table using DBMS_STATS.GATHER_[SCHEMA|TABLE|INDEX]_STATS and the force=>true parameter.
3)To prevent import (imp) from locking the table’s statistics when importing a table without the rows (rows=n), use statistics=none instead.
4)To prevent data pump import (impdp) from locking the table’s statistics when importing a table without the rows (content=metadata_only), use exclude=(table_statistics,index_statistics).
SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED
from dba_tab_statistics
where OWNER='TESTUSER' and TABLE_NAME='TEST_TABLE'; no rows selected $ impdp / directory=EXPDP_DIR dumpfile=TESTUSER.TEST_TABLE.dmp tables=TESTUSER.TEST_TABLE content=METADATA_ONLY exclude=table_statistics,index_statistics Import: Release 11.2.0.4.0 -Production on Mon Nov 10 14:12:14 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters and Automatic Storage Management options Master table "OPS$ORACLE"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "OPS$ORACLE"."SYS_IMPORT_TABLE_01": /******** directory=EXPDP_DIR dumpfile=TESTUSER.TEST_TABLE.dmp tables=TESTUSER.TEST_TABLE content=METADATA_ONLY exclude=table_statistics,index_statistics Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Job "OPS$ORACLE"."SYS_IMPORT_TABLE_01" successfully completed at Mon Nov 10 14:12:17 2014 elapsed 0 00:00:02 SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED
from dba_tab_statistics
where OWNER='TESTUSER' and TABLE_NAME='TEST_TABLE'; OWNER TABLE_NAME STATT --------- ----------- ------ TESTUSER TEST_TABLE
Make DBA Life Easy 