Day: November 10, 2014

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

datapump might lock table stats

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
Advertisement