[20231212]impdp content=metadata_only locks the stats.txt

[20231212]impdp content=metadata_only locks the stats.txt --//以前的测试[20170917]impdp content=metadata_only locks the stats.txt,在12.1.0.1.0 for windows下不存在这个问题. --//最近在优化一个项目时遇到的问题,全部应用表的统计信息都是lock的,再分析时必须加入force=>true,最后我先写脚本unlock. --//在我记忆里这个问题在于开始导入是选择CONTENT=METADATA_ONLY引起的,在12c重复测试块看看,加强记忆. 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 create table demo as select * from dual; create index demo on demo(dummy); exec dbms_stats.gather_table_stats(user,'DEMO'); CREATE OR REPLACE DIRECTORY TMP_EXPDP AS 'D:\tmp\expdp\'; GRANT READ, WRITE ON DIRECTORY TMP_EXPDP TO SCOTT; GRANT EXECUTE, READ, WRITE ON DIRECTORY TMP_EXPDP TO SYSTEM WITH GRANT OPTION; SCOTT@test01p> select count(*) from DEMO;   COUNT(*) ----------          1 SCOTT@test01p> select object_name,object_type from dba_objects where owner=user and object_name='DEMO'; OBJECT_NAME          OBJECT_TYPE -------------------- -------------------- DEMO                 TABLE DEMO                 INDEX SCOTT@test01p> select owner,table_name,last_analyzed,stattype_locked,num_rows from dba_tab_statistics where owner=user and table_name='DEMO'; OWNER                TABLE_NAME           LAST_ANALYZED       STATT   NUM_ROWS -------------------- -------------------- ------------------- ----- ---------- SCOTT                DEMO                 2023-12-11 20:54:38                1 SCOTT@test01p> select owner,table_name,last_analyzed,stattype_locked,num_rows from dba_tab_statistics where owner=user and table_name='DEMO'; OWNER  TABLE_NAME LAST_ANALYZED       STATT   NUM_ROWS ------ ---------- ------------------- ----- ---------- SCOTT  DEMO       2017-09-17 22:15:03                1 2.导出: d:\tmp\expdp>expdp scott/btbtms@test01p DIRECTORY=TMP_EXPDP DUMPFILE=demo.dmp tables=demo Export: Release 12.2.0.1.0 - Production on Mon Dec 11 20:56:40 2023 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a*****@test01p DIRECTORY=TMP_EXPDP DUMPFILE=demo.dmp tables=demo Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX . . exported "SCOTT"."DEMO"                              5.054 KB       1 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:   D:\TMP\EXPDP\DEMO.DMP Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Mon Dec 11 21:02:32 2023 elapsed 0 00:05:30 --//机器内存太小很慢.. SCOTT@test01p> alter table demo rename to demo1; Table altered. SCOTT@test01p> alter index demo rename to i_demo1_dummy; Index altered. 3.导入: d:\tmp\expdp>impdp scott/btbtms@test01p DIRECTORY=TMP_EXPDP DUMPFILE=demo.dmp tables=demo CONTENT=METADATA_ONLY Import: Release 12.2.0.1.0 - Production on Mon Dec 11 21:04:13 2023 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/a*****@test01p DIRECTORY=TMP_EXPDP DUMPFILE=demo.dmp tables=demo CONTENT=METADATA_ONLY Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 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 Mon Dec 11 21:05:05 2023 elapsed 0 00:00:46 SELECT table_name       ,global_stats       ,user_stats       ,stattype_locked       ,stale_stats       ,last_analyzed   FROM DBA_TAB_STATISTICS  WHERE owner = USER AND table_name IN ('DEMO', 'DEMO1'); TABLE_NAME           GLO USE STATT STA LAST_ANALYZED -------------------- --- --- ----- --- ------------------- DEMO1                YES NO        NO  2023-12-11 20:54:38 DEMO                 YES NO  ALL   NO  2023-12-11 20:54:38 ---//可以发现这样导入会导致表demo的统计信息被锁住. SCOTT@test01p> exec dbms_stats.gather_table_stats(user,'DEMO'); BEGIN dbms_stats.gather_table_stats(user,'DEMO'); END; * ERROR at line 1: ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 36873 ORA-06512: at "SYS.DBMS_STATS", line 36507 ORA-06512: at "SYS.DBMS_STATS", line 8582 ORA-06512: at "SYS.DBMS_STATS", line 9461 ORA-06512: at "SYS.DBMS_STATS", line 35836 ORA-06512: at "SYS.DBMS_STATS", line 36716 ORA-06512: at line 1 --//无法正常的分析表.