[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
--//無法正常的分析表.