DB导入/导出统计数据

一个用户paulITPUB个人空间KlU-he~i
SQL> create table test as select * from dba_objects;
Zx?!S)J,p0SQL> create index ind_test on test(object_id);
6`q C'w.va|0创建一个表，存放export出来的统计信息
x qHZN;T~? l1][0SQL> exec dbms_stats.create_stat_table(ownname => 'PAUL', stattab => 'STAT_AT', tblspace => 'SYSTEM');ITPUB个人空间[S:f^iVl
刚创建完成，记录数为0
7]e$ut$J;X [0SQL> select count(*) from STAT_AT;
4C"`~B6M'x0n"p0
*f9f8s/|\?S0COUNT(*)ITPUB个人空间)BSZbB"usZ;wyX
----------
_T2E t-R7Xi00ITPUB个人空间jSUH5x Gi^}
收集表和索引的统计信息ITPUB个人空间}iu+q$rQ
ITPUB个人空间z5s C/U*W*F
SQL>exec dbms_stats.gather_table_stats(ownname=>'PAUL',tabname=>'TEST',estimate_percent => 100,method_opt=> 'FOR ALL INDEXED COLUMNS');ITPUB个人空间em^/N'bIa \{v

9|,hrA{ DHC/W0SQL>exec dbms_stats.gather_index_stats(ownname=>'PAUL',indname=>'IND_TEST',estimate_percent=>100);ITPUB个人空间9Z Z9io!p(}*v)i U
我们导出统计信息
(C4i TT)f l0
V#e]L'^@(`F)x0SQL>exec dbms_stats.export_table_stats(ownname=>'PAUL', tabname=>'TEST', stattab=> 'STAT_AT');ITPUB个人空间/s:`e8oJ
ITPUB个人空间F |:o L.r8A^
SQL> select count(*) from STAT_AT;
T`euIw G0
jct*~7W7o,}'R0COUNT(*)ITPUB个人空间-lci5iM/},[8v#{
----------
~@4g})oX s0L03
1EB&c7wp^r0切换到另外一个用户test
%unA9B4r j6ZP&x0
d"ci.I WM7}B0SQL> create table test as select * from dba_objects;ITPUB个人空间Z0k%Q1H-Z~~"I&V

5WXA VAn/p3E~0Table created.
0Q []/J Aj#b0
-ixiu6[Of{4I0SQL> create index ind_test on test(object_id);ITPUB个人空间zmOdk ~1t `

|e|K.N\ [okZ*k0Index created.
U Y6P4q(C2pNd+?U0
uf/qX%D-EEE0SQL> create table STAT as select * from paul.STAT_AT;
7gYFp;E$@0
j X:X,U ~?hf/j3U0Table created.
!fs Y W
q8\Kj0如果直接执行当前的用户与表名，可以执行成功，但是，没有统计数据导入，需要把原来的导出用户更新成导入用户。
N7Hm@ Y8xK3H(y6z0
(Qsg6f_I\'o0SQL>exec dbms_stats.import_table_stats(ownname=>'TEST', tabname=>'TEST', stattab => 'STAT');
Z[cba)s u7b,\*S0ITPUB个人空间mI,W6?~0d7P {U
SQL>update stat set c5='TEST' where c5='PAUL'ITPUB个人空间
eh2O%]E0c$e'm@5R

E#R%Y[Q/H%i.e*N?0SQL>commit;
x,u-D!Q0}0ITPUB个人空间Pw+w~u9gI2c
SQL>exec dbms_stats.import_table_stats(ownname=>'TEST', tabname=>'TEST', stattab => 'STAT');ITPUB个人空间;rgg_R'|
然后再导入成功，导入信息包括表统计，索引统计信息，列的分析信息
6A`Y#QEv0

有关dbms_stats包收集统计信息和删除统计信息的用法ITPUB个人空间J+BN U!L t
1.收集统计信息ITPUB个人空间0xK]ti:@
DBMS_STATS.GATHER_TABLE_STATS (
~5M,acUbF$J0_0ownname VARCHAR2,
c"tx^^ezvY3K0tabname VARCHAR2,ITPUB个人空间j*X du*z1H(L
partname VARCHAR2 DEFAULT NULL,ITPUB个人空间.L$[-w!z)|{ p
estimate_percent NUMBER DEFAULT to_estimate_percent_typeITPUB个人空间
tc:M*U4i"Ze2L
(get_param('ESTIMATE_PERCENT')),
x!I!klT&l)?0block_sample BOOLEAN DEFAULT FALSE,
%t$@8}sq@%N\"f lf0method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),ITPUB个人空间eU{Js2T4j&U0bY
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
0F.L8jShlx T
J)l0granularity VARCHAR2 DEFAULT 'AUTO',ITPUB个人空间2p2q"hFq
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),ITPUB个人空间:gW.r6u!OX
stattab VARCHAR2 DEFAULT NULL,ITPUB个人空间+J2p8zai'[|o
statid VARCHAR2 DEFAULT NULL,
I2R*y'E~uihfv0statown VARCHAR2 DEFAULT NULL,
R'ne%u'd[l0no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (ITPUB个人空间v]jx
X Z5oE
get_param('NO_INVALIDATE')));ITPUB个人空间4q"F&\\ `Rl*B
ITPUB个人空间zw!e Nc
2.删除统计信息ITPUB个人空间H6V7a^2t;\8yI r
DBMS_STATS.DELETE_TABLE_STATS (ITPUB个人空间q8gMU8A8^%e
ownname VARCHAR2,ITPUB个人空间%Md/PBb3I2?%y
tabname VARCHAR2,ITPUB个人空间4sg x/pm r4c4?^ki
partname VARCHAR2 DEFAULT NULL,
8@?In d0stattab VARCHAR2 DEFAULT NULL,
-tQ-D{5e
Vqw4W0statid VARCHAR2 DEFAULT NULL,
!f.e9TIO IOZ3J0cascade_parts BOOLEAN DEFAULT TRUE,
^XjVPF^s+v0cascade_columns BOOLEAN DEFAULT TRUE,
^9TNdlZ8d2g(@X0cascade_indexes BOOLEAN DEFAULT TRUE,
~`4_e$P7SE9Q-Hi6nx:r0statown VARCHAR2 DEFAULT NULL,
OgRT ]3Z;|0no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (ITPUB个人空间7^ vR5Y{
get_param('NO_INVALIDATE')),ITPUB个人空间*xJRe)A]7jd#YS
force BOOLEAN DEFAULT FALSE);