本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer，已开源在github ，欢迎star！
这篇文章介绍统计信息锁定，《》，当时提出猜测，就是原库用了lock锁定统计信息，因此需要解锁，但是有位朋友很认真，提出了另一种可能的场景，
测试了下，果然如此，
- 创建测试表数据，
SQL create table a (id number); Table created.
SQL insert into a values(1); 1 row created.
SQL commit; Commit complete.
SQL select * from a; ID ---------- 1
- 导出数据，
$ exp system/oracle file=/home/ora11g/a.dmp schema=bisal
- 场景一：仅导入表结构，
$ imp system/oracle file=/home/ora11g/a.dmp fromuser=bisal touser=phibisal rows=n
Import: Release 11.2.0.4.0 - Production on Mon Feb 5 14:49:30 2018 Copyright (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, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in AL32UTF8 character set and AL16UTF16 NCHAR character set . importing BISAL's objects into PHIBISAL Import terminated successfully without warnings.
检索dba_tab_statistics，STATTYPE_LOCKED值为ALL，
SQL exec print_table('select owner, table_name, num_rows, blocks, sample_size, last_analyzed, stattype_locked from dba_tab_statistics where owner=''PHIBISAL'''); OWNER: PHIBISAL TABLE_NAME: A NUM_ROWS: BLOCKS: SAMPLE_SIZE: LAST_ANALYZED: STATTYPE_LOCKED: ALL ----------------- PL/SQL procedure successfully completed.
执行统计信息的收集，出现ORA-20005错误，
SQL dbms_stats.gather_table_stats('PHIBISAL','A',cascade=true); SP2-0734: unknown command beginning "dbms_stats..." - rest of line ignored. SQL exec dbms_stats.gather_table_stats('PHIBISAL','A',cascade=true); BEGIN dbms_stats.gather_table_stats('PHIBISAL','A',cascade=true); 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
- 场景二：导入数据，
imp system/oracle file=/home/ora11g/a.dmp fromuser=bisal touser=phibisal
Import: Release 11.2.0.4.0 - Production on Mon Feb 5 14:52:02 2018 Copyright (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, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in AL32UTF8 character set and AL16UTF16 NCHAR character set . importing BISAL's objects into PHIBISAL . . importing table "A" 1 rows imported Import terminated successfully without warnings.
检索dba_tab_statistics，STATTYPE_LOCKED值为“”，
SQL exec print_table('select owner, table_name, num_rows, blocks, sample_size, last_analyzed, stattype_locked from dba_tab_statistics where owner=''PHIBISAL'''); OWNER: PHIBISAL TABLE_NAME: A NUM_ROWS: BLOCKS: SAMPLE_SIZE: LAST_ANALYZED: STATTYPE_LOCKED: -----------------
PL/SQL procedure successfully completed.此时可以采集统计信息，
SQL exec dbms_stats.gather_table_stats('PHIBISAL','A',cascade=true); PL/SQL procedure successfully completed.
因此除了使用dbms_stats.lock_schema_stats/dbms_stats.lock_table_stats会所统计信息，执行imp外加rows=n只导入表结构，同样会锁定统计信息，需要执行解锁的操作。
谢谢captain朋友的提醒，对于imp导入，需要注意这些小问题，如果有其他问题，欢迎朋友们提出。
如果您觉得本文有帮助，欢迎关注转发：bisal的个人杂货铺，
