数据库无统计无索引导出导入测试

 

同时排除统计与索引

exclude=STATISTICS,INDEX

expdp \'/ as sysdba\'  directory=EXPDP_DIR dumpfile=core_full1.dmp full=y

******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /orabak/expdpdir/core_full1.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Wed Nov 30 15:35:03 2016 elapsed 0 00:06:32

expdp \'/ as sysdba\'  directory=EXPDP_DIR dumpfile=core_full_no_index_stat.dmp exclude=STATISTICS,INDEX

******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /orabak/expdpdir/core_full_no_index_stat.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Nov 30 15:52:18 2016 elapsed 0 00:00:30

[oracle@fangcheng expdpdir]$ ll
total 101964
-rw-r----- 1 oracle oinstall 104243200 Nov 30 15:35 core_full1.dmp
-rw-r----- 1 oracle oinstall    159744 Nov 30 15:52 core_full_no_index_stat.dmp
-rw-r--r-- 1 oracle oinstall       953 Nov 30 15:52 export.log

同时排除统计与索引且指定用户

expdp \'/ as sysdba\' schemas=scott,root directory=EXPDP_DIR dumpfile=scott_root_no_index_stat.dmp exclude=STATISTICS,INDEX
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /orabak/expdpdir/scott_root_no_index_stat.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Nov 30 16:02:52 2016 elapsed 0 00:00:16

测试无索引导出后，导入时还是否有索引

set linesize 210
col index_name for a27
col index_type for a12
col column_name for a27
col table_name for a21
select ic.INDEX_OWNER,
       ic.INDEX_NAME,
       di.index_type,
       di.uniqueness,
       di.status,
       ic.TABLE_NAME,
       ic.COLUMN_NAME
  from dba_ind_columns ic, dba_indexes di
where ic.INDEX_OWNER = di.owner
   and ic.INDEX_NAME = di.index_name
   and ic.table_owner='ROOT' and ic.table_name='C_USER';

INDEX_OWNER               INDEX_NAME           INDEX_TYPE    UNIQUENES STATUS   TABLE_NAME         COLUMN_NAME
------------------------------ --------------------------- ------------ --------- -------- --------------------- ---------------------------
ROOT                   USER_ID_KEY           NORMAL    UNIQUE      VALID    C_USER         CU_CSTID
ROOT                   USER_ID_KEY           NORMAL    UNIQUE      VALID    C_USER         CU_UID
ROOT                   C_USER_INDEX           NORMAL    NONUNIQUE VALID    C_USER         CU_UID

 

导入时表空间必须有，用户会自动创建

impdp \'/ as sysdba\' schemas=root directory=DATA_PUMP_DIR dumpfile=scott_root_no_index_stat.dmp remap_tablespace=ts_root:users

Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Wed Nov 30 16:37:00 2016 elapsed 0 00:00:04

INDEX_OWNER               INDEX_NAME           INDEX_TYPE    UNIQUENES STATUS   TABLE_NAME         COLUMN_NAME
------------------------------ --------------------------- ------------ --------- -------- --------------------- ---------------------------
ROOT                   USER_ID_KEY           NORMAL    UNIQUE      VALID    C_USER         CU_CSTID
ROOT                   USER_ID_KEY           NORMAL    UNIQUE      VALID    C_USER         CU_UID

索引会创建，但有些不一样了，对比发现原库表上的索引，CU_UID 已经是唯一索引了，另外一个索引没有必要，这样来看，实验是成功的。

通过指定用户、排除统计信息与索引，可以节省备份的时间和空间。