What is the new value SAFE of the STATISTICS parameter at IMPORT ?
------------------------------------------------------------------
The 4 possible values for this parameter are :
-> ALWAYS : Always imports database optimizer statistics regardless of whether or not they are questionable.
无论是否有可疑信息,都会导入优化器的统计信息
-> NONE : Does not import or recalculate the database optimizer statistics.
不导入或重新计算优化器的统计信息
-> SAFE : Imports database optimizer statistics back only if they are not questionable.
If they are questionable, recalculates the optimizer statistics.
如果无可疑信息,则导入原优化器统计信息
如果有可疑信息,则重新计算优化器的统计信息
-> RECALCULATE : Does not import the database optimizer statistics. Instead, recalculates them on import.
不导入优化器的统计信息,在imp的时候重新收集优化器的统计信息
---------------------------------------------------------------------
When are statistics questionable ?
(什么是统计信息可疑)
----------------------------------
The precalculated optimizer statistics are flagged as questionable at export
time if:
在exp时,如果出现以下几种情况,那么预先计算好的优化器的统计信息被标记为可疑
-> There are row errors while exporting
exp时有行的错误
-> The client character set or NCHAR character set does not match the server character set or NCHAR character set
客户端和server端的字符集不一致
-> A QUERY clause is specified
有查询语句
-> Only certain partitions or subpartitions are exported
exp只导出某一个分区或者子分区
e.g.
1. 导出用户 ecc_view2 的dmp文件 (制造统计信息不是最新的情况)
a. ECC_VIEW2 检查统计信息
select table_name, tablespace_name, status, num_rows, blocks,last_analyzed
from user_tables
table_name tablespace_name status num_rows blocks last_analyzed
----------------- ------------------------- --------- --------------- ---------- -----------------------
TEST USERS VALID 2 5 2009-6-23 13:47:05
b. 重新收集统计信息
EXEC DBMS_STATS.gather_table_stats(ownname => 'ECC_VIEW2',tabname => 'TEST',cascade => TRUE);
select table_name, tablespace_name, status, num_rows, blocks,last_analyzed
from user_tables
table_name tablespace_name status num_rows blocks last_analyzed
----------------- ------------------------- --------- -------------- --------- -----------------------
TEST USERS VALID 5 5 2009-6-24 14:35:40
c. 插入5条记录,exp
insert into test
select * from TEST
select count(*) from test -- 10 rows
d. exp ecc_view2
[oracle@rac1 ~]$ exp ecc_view2/ecc@devdb1 file=./ecc_view2.dmp ;
. about to export ECC_VIEW2's tables via Conventional Path ...
. . exporting table TEST 10 rows exported
Export terminated successfully without warnings.
2. 创建用户 ecc_view3
create user ecc_view3
identified by ecc
default tablespace DATA03
grant connect, resource to ecc_view3
3. 导入数据到ecc_view3
a. statistics = ALWAYS (default)
[oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view2.dmp fromuser=ecc_view2 touser=ecc_view3
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table "TEST" 10 rows imported
Import terminated successfully without warnings.
select table_name, tablespace_name, status, num_rows, blocks,last_analyzed
from user_tables
table_name tablespace_name status num_rows blocks last_analyzed
------------------ ----------------------- ------ -------- ------ -----------------------
TEST USERS VALID 5 5 2009-6-24 14:42:44
b. statistics = none
drop table test
[oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view2.dmp fromuser=ecc_view2 touser=ecc_view3 statistics=none
select table_name, tablespace_name, status, num_rows, blocks,last_analyzed
from user_tables
table_name tablespace_name status num_rows blocks last_analyzed
---------- ------------------------------ ------ ------------- ------ -----------------------
TEST USERS VALID
c. statistics = safe
drop table test
[oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view2.dmp fromuser=ecc_view2 touser=ecc_view3 statistics=safe
select table_name, tablespace_name, status, num_rows, blocks,last_analyzed
from user_tables
table_name tablespace_name status num_rows blocks last_analyzed
--------------- --------------------------- ------ -------- ------ -----------------------
TEST USERS VALID 10 5 2009-6-24 14:54:51
d. statistics = RECALCULATE
drop table test
[oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view2.dmp fromuser=ecc_view2 touser=ecc_view3 statistics=RECALCULATE;
select table_name, tablespace_name, status, num_rows, blocks,last_analyzed
from user_tables
table_name tablespace_name status num_rows blocks last_analyzed
----------------- ---------------- -------- ------------ ------ ---------------------------
TEST USERS VALID 10 5 2009-6-24 14:56:03
参考文献:
Subject: Import parameter: STATISTICS=SAFE
Doc ID: 159787.1 Type: BULLETIN
Modified Date : 11-NOV-2008 Status: PUBLISHED
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/9252210/viewspace-607446/ ,如需转载,请注明出处,否则将追究法律责任。