数据泵导出索引数据和统计信息吗

关于在数据泵问题:
(1)索引数据导出吗?
(2)源表统计数据导出/导入吗？

1创建测试表
drop table test;
create table test
as
select dbms_random.string('x',50) as x from dual
connect by level<=1e6;

create index idx_test_x on test(x);

begin
  dbms_stats.gather_table_stats(user,'TEST');
end;
/ 

select table_name,num_rows,blocks,to_char(last_analyzed,'YYYYMMDD hh24miss') as l_anay from user_tables where table_name='TEST';
TABLE_NAME   NUM_ROWS     BLOCKS L_ANAY
---------- ---------- ---------- --------------------
TEST           997971       7884 20140315 153237

select index_name,leaf_blocks,to_char(last_analyzed,'YYYYMMDD hh24miss') as l_anay from user_indexes where table_name='TEST';
INDEX_NAME      LEAF_BLOCKS L_ANAY
--------------- ----------- --------------------
IDX_TEST_X             8467 20140315 153238

col segment_name for a20
select segment_name,bytes/1024/1024 from user_segments where segment_name in('TEST','IDX_TEST_X');
SEGMENT_NAME         BYTES/1024/1024
-------------------- ---------------
TEST                              62
IDX_TEST_X                        72

2修改统计数据
begin
  dbms_stats.set_table_stats(ownname => user,tabname => 'TEST',numrows => 1000,numblks => 100);
end;
/ 

begin
  dbms_stats.set_index_stats(ownname => user,indname => 'IDX_TEST_X',numlblks => 100);
end;
/ 

select table_name,num_rows,blocks,to_char(last_analyzed,'YYYYMMDD hh24miss') as l_anay from user_tables where table_name='TEST';
TABLE_NAME   NUM_ROWS     BLOCKS L_ANAY
---------- ---------- ---------- --------------------
TEST             1000        100 20140315 153457

select index_name,leaf_blocks,to_char(last_analyzed,'YYYYMMDD hh24miss') as l_anay from user_indexes where table_name='TEST';
INDEX_NAME      LEAF_BLOCKS L_ANAY
--------------- ----------- --------------------
IDX_TEST_X              100 20140315 153457

3包含/不包含索引导出
(1)不包含索引导出
oracle@rhel5 mah]$ expdp \'sys/sys as sysdba\' directory='DMP_DIR' tables=MH.TEST dumpfile=test.dmp exclude=INDEX estimate=statistics
Export: Release 10.2.0.1.0 - Production on Saturday, 15 March, 2014 15:36:19
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TABLE_01":  'sys/******** AS SYSDBA' directory=DMP_DIR tables=MH.TEST dumpfile=test.dmp exclude=INDEX estimate=statistics
Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "MH"."TEST"                                 54.86 KB   ==>estimate=blocks时,估计为62 MB
Total estimation using STATISTICS method: 54.86 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MH"."TEST"                                 52.45 MB 1000000 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/mah/test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 15:36:26

(2)包含索引导出
[oracle@rhel5 mah]$ expdp \'sys/sys as sysdba\' directory='DMP_DIR' tables=MH.TEST dumpfile=test2.dmp  estimate=statistics
Export: Release 10.2.0.1.0 - Production on Saturday, 15 March, 2014 15:37:22
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TABLE_01":  'sys/******** AS SYSDBA' directory=DMP_DIR tables=MH.TEST dumpfile=test2.dmp estimate=statistics
Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "MH"."TEST"                                 54.86 KB
Total estimation using STATISTICS method: 54.86 KB
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
. . exported "MH"."TEST"                                 52.45 MB 1000000 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/mah/test2.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 15:37:30

导出文件比较:
[oracle@rhel5 mah]$ ls -lrt
-rw-r----- 1 oracle oinstall 55070720 Mar 15 15:36 test.dmp
-rw-r----- 1 oracle oinstall 55078912 Mar 15 15:37 test2.dmp  ==>含索引

==>两个文件大小差异极小(8192bytes),因TEST表上索引列较大(50bytes*1000000rows),不可能含索引数据;多出来的数据块仅包含索引结构.

4导入
(1)删除表MH.TEST后:
impdp \'sys/sys as sysdba\' directory='DMP_DIR' tables=MH.TEST dumpfile=test.dmp
SQL> select table_name,num_rows,blocks,to_char(last_analyzed,'YYYYMMDD hh24miss') as l_anay from user_tables where table_name='TEST';

TABLE_NAME   NUM_ROWS     BLOCKS L_ANAY
---------- ---------- ---------- --------------------
TEST             1000        100 20140315 153457

SQL> select index_name,leaf_blocks,to_char(last_analyzed,'YYYYMMDD hh24miss') as l_anay from user_indexes where table_name='TEST';

no rows selected

(2)删除表MH.TEST后:
impdp \'sys/sys as sysdba\' directory='DMP_DIR' tables=MH.TEST dumpfile=test2.dmp

SQL> select table_name,num_rows,blocks,to_char(last_analyzed,'YYYYMMDD hh24miss') as l_anay from user_tables where table_name='TEST';

TABLE_NAME   NUM_ROWS     BLOCKS L_ANAY
---------- ---------- ---------- --------------------
TEST             1000        100 20140315 153457

SQL> select index_name,leaf_blocks,to_char(last_analyzed,'YYYYMMDD hh24miss') as l_anay from user_indexes where table_name='TEST';

INDEX_NAME      LEAF_BLOCKS L_ANAY
--------------- ----------- --------------------
IDX_TEST_X              100 20140315 153457

==>行数和数据块是导出时设置的统计信息,而不是数据本身真实分布信息,包括索引和表上的上次分析时间

5结论
(1)expdp不导出索引数据,仅导出索引DDL
(2)expdp/impdp分别导出/导入统计数据

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

转载于:http://blog.itpub.net/18922393/viewspace-719227/