统计信息锁定相关

实验步骤：
(1)在scott用户下创建表t1、t2、t3，分别插入数据，收集统计信息
(2)锁定scott.t1的统计信息
(3)expdp导出
(4)impdp导入到t1、t2到terry用户下，使用CONTENT=METADATA_ONLY选项将t3用impdp导入到terry用户下
(5)查看统计信息，并尝试重新收集
(6)导入数据时，在使用content=metadata_only的同时，使用exclude=TABLE_STATISTICS去掉表统计信息的导入
(7)导入数据时指定查询条件，统计信息会被导入，但不再准确
(8)手工设置统计信息
(9)解锁统计信息


环境：11.2.0.1.0
=======================
(1)在scott用户下创建表t1、t2、t3，分别插入数据，收集统计信息
drop table scott.t1;
drop table scott.t2;
drop table scott.t3;
create table scott.t1 as select * from dba_tables where rownum<1000;
create table scott.t2 as select * from dba_tables where rownum<1000;
create table scott.t3 as select * from dba_tables where rownum<1000;


exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true);
exec dbms_stats.gather_table_stats('SCOTT','T2',cascade=>true);
exec dbms_stats.gather_table_stats('SCOTT','T3',cascade=>true);


(2)锁定scott.t1的统计信息
exec dbms_stats.lock_table_stats(ownname => 'SCOTT',tabname => 'T1');
set lines 200
select owner,table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),user_stats,stattype_locked 
from dba_tab_statistics
where table_name in ('T1','T2','T3');




SQL>set lines 200
SQL>select owner,table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),user_stats,stattype_locked 
from dba_tab_statistics
where table_name in ('T1','T2','T3');


OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ USE STATT
------------------------------ ------------------------------ ---------- ---------- ------------------- --- -----
SCOTT                          T3                                    999         41 2016-01-10 20:01:06 NO
SCOTT                          T2                                    999         41 2016-01-10 20:01:05 NO
SCOTT                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL


(3)expdp导出
[oracle@ogg ~]$ expdp scott/scott directory=ORA_HOME dumpfile=testexp.dmp logfile=testexp.log tables=T1,T2,T3


Export: Release 11.2.0.1.0 - Production on Sun Jan 10 20:13:29 2016


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=ORA_HOME dumpfile=testexp.dmp logfile=testexp.log tables=T1,T2,T3 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.125 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."T1"                                269.0 KB     999 rows
. . exported "SCOTT"."T2"                                269.0 KB     999 rows
. . exported "SCOTT"."T3"                                269.0 KB     999 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /home/oracle/testexp.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 20:13:39


(4)impdp导入到t1、t2到terry用户下，使用CONTENT=METADATA_ONLY选项将t3用impdp导入到terry用户下
[oracle@ogg ~]$  impdp terry/terry remap_schema=scott:terry directory=ORA_HOME  dumpfile=testexp.dmp logfile=impdp_testexp.log tables=scott.t1,scott.t2


Import: Release 11.2.0.1.0 - Production on Sun Jan 10 20:29:33 2016


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TERRY"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TERRY"."SYS_IMPORT_TABLE_01":  terry/******** remap_schema=scott:terry directory=ORA_HOME dumpfile=testexp.dmp logfile=impdp_testexp.log tables=scott.t1,scott.t2 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TERRY"."T1"                                269.0 KB     999 rows
. . imported "TERRY"."T2"                                269.0 KB     999 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TERRY"."SYS_IMPORT_TABLE_01" successfully completed at 20:29:39






[oracle@ogg ~]$ impdp terry/terry remap_schema=scott:terry directory=ORA_HOME  dumpfile=testexp.dmp logfile=impdp_testexp.log tables=scott.t3 CONTENT=METADATA_ONLY


Import: Release 11.2.0.1.0 - Production on Sun Jan 10 20:30:36 2016


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TERRY"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TERRY"."SYS_IMPORT_TABLE_01":  terry/******** remap_schema=scott:terry directory=ORA_HOME dumpfile=testexp.dmp logfile=impdp_testexp.log tables=scott.t3 CONTENT=METADATA_ONLY 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TERRY"."SYS_IMPORT_TABLE_01" successfully completed at 20:30:42




(5)查看统计信息，并尝试重新收集
查看统计信息：
SQL> set lines 200
select owner,table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),user_stats,stattype_locked 
  2  from dba_tab_statistics
  3  where table_name in ('T1','T2','T3');


OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ USE STATT
------------------------------ ------------------------------ ---------- ---------- ------------------- --- -----
SCOTT                          T3                                    999         41 2016-01-10 20:01:06 NO
SCOTT                          T2                                    999         41 2016-01-10 20:01:05 NO
SCOTT                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL
TERRY                          T3                                    999         41 2016-01-10 20:01:06 NO  ALL
TERRY                          T2                                    999         41 2016-01-10 20:01:05 NO
TERRY                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL


6 rows selected.
可以看到：原统计信息锁定的scott.t1，导入后terry.t1仍是锁定的；
scott.t3由于导入时指定了content=metadata_only,统计信息也被锁定了。


三个表中分别插入数据：
insert into terry.t1 select * from terry.t1;
insert into terry.t2 select * from terry.t1;
insert into terry.t3 select * from terry.t1;
commit;


收集统计信息：
SQL> exec dbms_stats.gather_table_stats('TERRY','T1',cascade=>true);
BEGIN dbms_stats.gather_table_stats('TERRY','T1',cascade=>true); END;


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




SQL> exec dbms_stats.gather_table_stats('TERRY','T2',cascade=>true);


PL/SQL procedure successfully completed.


SQL> exec dbms_stats.gather_table_stats('TERRY','T3',cascade=>true);
BEGIN dbms_stats.gather_table_stats('TERRY','T3',cascade=>true); END;


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


锁定统计信息后，手工收集统计信息报错。




(6)导入数据时，在使用content=metadata_only的同时，使用exclude=TABLE_STATISTICS去掉表统计信息的导入


[oracle@ogg ~]$ impdp terry/terry remap_schema=scott:terry directory=ORA_HOME  dumpfile=testexp.dmp logfile=impdp_testexp.log remap_table=t3:t4 CONTENT=METADATA_ONLY EXCLUDE=TABLE_STATISTICS 


Import: Release 11.2.0.1.0 - Production on Sun Jan 10 20:33:56 2016


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TERRY"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TERRY"."SYS_IMPORT_FULL_01":  terry/******** remap_schema=scott:terry directory=ORA_HOME dumpfile=testexp.dmp logfile=impdp_testexp.log remap_table=t3:t4 CONTENT=METADATA_ONLY EXCLUDE=TABLE_STATISTICS 
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "TERRY"."T1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "TERRY"."T2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Job "TERRY"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 20:34:01


[oracle@ogg ~]$ 
[oracle@ogg ~]$ 
[oracle@ogg ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 10 20:34:16 2016


Copyright (c) 1982, 2009, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> set lines 200
select owner,table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),user_stats,stattype_locked 
from dba_tab_statistics
  3  where table_name in ('T1','T2','T3','T4');


OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ USE STATT
------------------------------ ------------------------------ ---------- ---------- ------------------- --- -----
SCOTT                          T3                                    999         41 2016-01-10 20:01:06 NO
SCOTT                          T2                                    999         41 2016-01-10 20:01:05 NO
SCOTT                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL
TERRY                          T4                                                                       NO
TERRY                          T3                                    999         41 2016-01-10 20:01:06 NO  ALL
TERRY                          T2                                   2997        115 2016-01-10 20:32:18 NO
TERRY                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL


7 rows selected.


从上面查询看到terry.t4的统计信息是空的。


(7)导入数据时指定查询条件，统计信息会被导入，但不再准确
删除terry.t3表，重新导入，导入时指定查询条件：
SQL> drop table terry.t3;


Table dropped.




[oracle@ogg ~]$ impdp terry/terry remap_schema=scott:terry directory=ORA_HOME  dumpfile=testexp.dmp logfile=impdp_testexp.log remap_table=t3:t3  QUERY=t3:\"WHERE rownum\< 100\"


Import: Release 11.2.0.1.0 - Production on Sun Jan 10 20:54:16 2016


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TERRY"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TERRY"."SYS_IMPORT_FULL_01":  terry/******** remap_schema=scott:terry directory=ORA_HOME dumpfile=testexp.dmp logfile=impdp_testexp.log remap_table=t3:t3 QUERY=t3:"WHERE rownum< 100" 
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "TERRY"."T1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "TERRY"."T2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TERRY"."T3"                                269.0 KB      99 out of 999 rows  --根据此处的显示，确实导入了99条数据
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TERRY"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 20:54:23


[oracle@ogg ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 10 20:54:29 2016


Copyright (c) 1982, 2009, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select count(1) from terry.t3;


  COUNT(1)
----------
        99
        
set lines 200
select owner,table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),user_stats,stattype_locked 
from dba_tab_statistics
  3  where table_name in ('T1','T2','T3','T4');


OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ USE STATT
------------------------------ ------------------------------ ---------- ---------- ------------------- --- -----
SCOTT                          T4                                      0          4 2015-11-06 22:01:03 NO
SCOTT                          T3                                    999         41 2016-01-10 20:01:06 NO
SCOTT                          T2                                    999         41 2016-01-10 20:01:05 NO
SCOTT                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL
TERRY                          T4                                                                       NO
TERRY                          T3                                    999         41 2016-01-10 20:01:06 NO
TERRY                          T2                                   2997        115 2016-01-10 20:32:18 NO
TERRY                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL


8 rows selected.


注意到上面显示结果中terry.t3统计信息仍然是999条。


(8)手工设置统计信息


SQL> exec dbms_stats.set_table_stats(ownname => 'TERRY',tabname => 'T4',numrows => 1000,numblks => 1000);


PL/SQL procedure successfully completed.


set lines 200
select owner,table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),user_stats,stattype_locked 
from dba_tab_statistics
where table_name in ('T1','T2','T3','T4');


OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ USE STATT
------------------------------ ------------------------------ ---------- ---------- ------------------- --- -----
SCOTT                          T4                                      0          4 2015-11-06 22:01:03 NO
SCOTT                          T3                                    999         41 2016-01-10 20:01:06 NO
SCOTT                          T2                                    999         41 2016-01-10 20:01:05 NO
SCOTT                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL
TERRY                          T4                                   1000       1000 2016-01-10 21:30:01 YES
TERRY                          T3                                    999         41 2016-01-10 20:01:06 NO
TERRY                          T2                                   2997        115 2016-01-10 20:32:18 NO
TERRY                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL


8 rows selected.


注意到terry.t4的user_stats字段变为YES，即手工指定了统计信息。


再次收集统计信息，该字段恢复：


SQL>  exec dbms_stats.gather_table_stats('TERRY','T4',cascade=>true);


PL/SQL procedure successfully completed.


SQL> set lines 200
SQL> select owner,table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),user_stats,stattype_locked 
  2  from dba_tab_statistics
  3  where table_name in ('T1','T2','T3','T4');


OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ USE STATT
------------------------------ ------------------------------ ---------- ---------- ------------------- --- -----
SCOTT                          T4                                      0          4 2015-11-06 22:01:03 NO
SCOTT                          T3                                    999         41 2016-01-10 20:01:06 NO
SCOTT                          T2                                    999         41 2016-01-10 20:01:05 NO
SCOTT                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL
TERRY                          T4                                      0          0 2016-01-10 21:32:21 NO
TERRY                          T3                                    999         41 2016-01-10 20:01:06 NO
TERRY                          T2                                   2997        115 2016-01-10 20:32:18 NO
TERRY                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL


8 rows selected.




(9)解锁统计信息
解锁的语句为
execute DBMS_STATS.UNLOCK_TABLE_STATS ('TERRY', 'T1');


还可以在收集统计信息时指定force选项，这样可以修改统计信息，但统计信息仍是锁定状态
exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true,force=>true);


查询结果如下：
SQL> set lines 200
SQL> from dba_tab_statistics
select owner,table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),user_stats,stattype_locked 
from dba_tab_statistics
  3  where table_name in ('T1','T2','T3','T4');


OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ USE STATT
------------------------------ ------------------------------ ---------- ---------- ------------------- --- -----
SCOTT                          T4                                      0          4 2015-11-06 22:01:03 NO
SCOTT                          T3                                    999         41 2016-01-10 20:01:06 NO
SCOTT                          T2                                    999         41 2016-01-10 20:01:05 NO
SCOTT                          T1                                   1998         78 2016-01-10 21:36:44 NO  ALL
TERRY                          T4                                      0          0 2016-01-10 21:32:21 NO
TERRY                          T3                                    999         41 2016-01-10 20:01:06 NO
TERRY                          T2                                   2997        115 2016-01-10 20:32:18 NO
TERRY                          T1                                    999         41 2016-01-10 20:01:04 NO


8 rows selected.


综上，impdp导入时如果使用了content=metadata_only,则统计信息被自动锁定，可以通过参数exclude=table_statistics去掉统计信息，或者导入后手工解锁;
如果导入数据时指定了查询条件（其实只要导出和导入，统计信息就不再准确），应根据需要重新收集统计信息。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-1979722/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-1979722/