[20160904]表统计信息lock.txt

[20160904]表统计信息lock.txt

晚上看链接:https://blogs.oracle.com/Database4CN/entry/%E8%AF%8A%E6%96%AD%E7%BB%9F%E8%AE%A1%E4%BF%A1%E6%81%AF%E4%B8%8D%E6%94%B6%E9%9B%86%E5%8E%9F%E5%9B%A0

提到如果导入使用ROWS=n,会导致导入的表lock,测试看看:

官方有如下解释：
If ROWS=n, then statistics for all imported tables will be locked after the import operation is finished.

--自己测试看看。作者测试使用的是exp/imp，我先测试看看expdp/impdp是否存在这个问题。

1.环境：
SCOTT@test01p> @ ver1
PORT_STRING          VERSION    BANNER                                                                       CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production      0

SCOTT@test01p> create table deptx as select * from dept ;
Table created.

2.导出(expdp)：
D:\tools\rlwrap>expdp scott/btbtms@test01p DIRECTORY=TMP_EXPDP DUMPFILE=deptx.dmp tables=deptx
expdp scott/btbtms@test01p DIRECTORY=TMP_EXPDP DUMPFILE=deptx.dmp tables=deptx
Export: Release 12.1.0.1.0 - Production on Sun Sep 4 20:27:10 2016
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a*******@test01p DIRECTORY=TMP_EXPDP DUMPFILE=deptx.dmp tables=deptx
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/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "SCOTT"."DEPTX"                             6.007 KB       4 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  D:\TMP\EXPDP\DEPTX.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sun Sep 4 20:28:03 2016 elapsed 0 00:00:51

3.导入(impdp)：
D:\tools\rlwrap>impdp scott/btbtms@test01p DIRECTORY=TMP_EXPDP DUMPFILE=deptx.dmp tables=deptx CONTENT=METADATA_ONLY REMAP_TABLE=SCOTT.DEPTX:DEPTY
impdp scott/btbtms@test01p DIRECTORY=TMP_EXPDP DUMPFILE=deptx.dmp tables=deptx CONTENT=METADATA_ONLY REMAP_TABLE=SCOTT.DEPTX:DEPTY
Import: Release 12.1.0.1.0 - Production on Sun Sep 4 20:33:38 2016
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/a**@test01p DIRECTORY=TMP_EXPDP DUMPFILE=deptx.dmp tables=deptx CONTENT=METADATA_ONLY REMAP_TABLE=SCOTT.DEPTX:DEPTY
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Sun Sep 4 20:34:06 2016 elapsed 0 00:00:25

SELECT table_name
      ,global_stats
      ,user_stats
      ,stattype_locked
      ,stale_stats
      ,last_analyzed
  FROM DBA_TAB_STATISTICS
 WHERE owner = USER AND table_name IN ('DEPTX', 'DEPTY');

TABLE_NAME GLO USE STATT STA LAST_ANALYZED
---------- --- --- ----- --- -------------------
DEPTX      YES NO        NO  2016-09-04 20:19:55
DEPTY      NO  NO

--可以发现没有锁定。

4.测试使用exp：

D:\tools\rlwrap>exp scott/btbtms@test01p file=deptx.dmp tables=deptx
exp scott/btbtms@test01p file=deptx.dmp tables=deptx
Export: Release 12.1.0.1.0 - Production on Sun Sep 4 20:44:08 2016
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table                          DEPTX          4 rows exported
Export terminated successfully without warnings.

SCOTT@test01p> drop table depty purge ;
Table dropped.

SCOTT@test01p> rename deptx to deptx_old;
Table renamed.

D:\tools\rlwrap>imp scott/btbtms@test01p file=deptx tables=deptx rows=N
imp scott/btbtms@test01p file=deptx tables=deptx rows=N
Import: Release 12.1.0.1.0 - Production on Sun Sep 4 20:48:32 2016
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Export file created by EXPORT:V12.01.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
Import terminated successfully without warnings.

SELECT table_name
      ,global_stats
      ,user_stats
      ,stattype_locked
      ,stale_stats
      ,last_analyzed
  FROM DBA_TAB_STATISTICS
 WHERE owner = USER AND table_name IN ('DEPTX', 'DEPTY');

TABLE_NAME GLO USE STATT STA LAST_ANALYZED
---------- --- --- ----- --- -------------------
DEPTX      YES NO  ALL   NO  2016-09-04 20:48:32

--stattype_locked=ALL,说明表统计信息已经锁定。再次导入：

D:\tools\rlwrap>imp scott/btbtms@test01p file=deptx tables=deptx rows=Y ignore=y
imp scott/btbtms@test01p file=deptx tables=deptx rows=Y ignore=y
Import: Release 12.1.0.1.0 - Production on Sun Sep 4 20:51:04 2016
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Export file created by EXPORT:V12.01.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                        "DEPTX"          4 rows imported
IMP-00017: following statement failed with ORACLE error 20005:
 "BEGIN  DBMS_STATS.SET_TABLE_STATS(NULL,'"DEPTX"',NULL,NULL,NULL,4,4,20,6); "
 "END;"
IMP-00003: ORACLE error 20005 encountered
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 14937
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20005:
 "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := 'C10B'; SREC.MAXVAL "
 ":= 'C129'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.N"
 "UMARRAY(10,40); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBM"
 "S_STATS.SET_COLUMN_STATS(NULL,'"DEPTX"','"DEPTNO"', NULL ,NULL,NULL,4,.25,0"
 ",srec,3,6); END;"
IMP-00003: ORACLE error 20005 encountered
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 7581
ORA-06512: at "SYS.DBMS_STATS", line 8505
ORA-06512: at "SYS.DBMS_STATS", line 13489
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20005:
 "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '4143434F554E54494E4"
 "7'; SREC.MAXVAL := '53414C4553'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC."
 "NOVALS := DBMS_STATS.NUMARRAY(338863550087541000000000000000000000,43228503"
 "8677786000000000000000000000); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SRE"
 "C.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"DEPTX"','"DNAME"', NULL ,NUL"
 "L,NULL,4,.25,0,srec,10,6); END;"
IMP-00003: ORACLE error 20005 encountered
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 7581
ORA-06512: at "SYS.DBMS_STATS", line 8505
ORA-06512: at "SYS.DBMS_STATS", line 13489
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20005:
 "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '4348494341474F'; SR"
 "EC.MAXVAL := '4E455720594F524B'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC."
 "NOVALS := DBMS_STATS.NUMARRAY(349350027483572000000000000000000000,40640554"
 "4089997000000000000000000000); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SRE"
 "C.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"DEPTX"','"LOC"', NULL ,NULL,"
 "NULL,3,.333333333333333,0,srec,8,6); END;"
IMP-00003: ORACLE error 20005 encountered
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 7581
ORA-06512: at "SYS.DBMS_STATS", line 8505
ORA-06512: at "SYS.DBMS_STATS", line 13489
ORA-06512: at line 1
Import terminated successfully with warnings.

--//可以发现再次导入时导入统计信息时报错。

D:\tools\rlwrap>imp scott/btbtms@test01p file=deptx tables=deptx rows=Y ignore=y STATISTICS=NONE
imp scott/btbtms@test01p file=deptx tables=deptx rows=Y ignore=y STATISTICS=NONE
Import: Release 12.1.0.1.0 - Production on Sun Sep 4 20:55:21 2016
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Export file created by EXPORT:V12.01.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                        "DEPTX"          4 rows imported
Import terminated successfully without warnings.

SCOTT@test01p> exec dbms_stats.gather_table_stats ( OwnName => 'scott',TabName => 'deptx');
BEGIN dbms_stats.gather_table_stats ( OwnName => 'scott',TabName => 'deptx'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 33859
ORA-06512: at line 1

--解锁再分析就ok了。
SCOTT@test01p> execute sys.dbms_stats.unlock_table_stats (ownname => user,tabname  => 'deptx')
PL/SQL procedure successfully completed.

SCOTT@test01p> exec dbms_stats.gather_table_stats ( OwnName => 'scott',TabName => 'deptx');
PL/SQL procedure successfully completed.

--总结：
--可以发现使用这种方式(exp/imp)导入数据要注意统计信息时lock的。