truncate表后impdp导入该表时加exclude=index参数并不能排除索引数据的导入

最新推荐文章于 2023-12-14 14:00:42 发布

conggangdian4456

最新推荐文章于 2023-12-14 14:00:42 发布

阅读量2k

点赞数

文章标签：数据库

1.truncate表:
SQL> truncate table GCADJUSTMENTMAIN;

Table truncated.

XIEZQ用户下只有一个表：
SQL> SELECT segment_name, SUM (bytes) / 1024 / 1024
2 FROM dba_segments
3 WHERE owner = 'XIEZQ' AND segment_name LIKE 'PK%' or segment_name like 'IDX%' group by segment_name;

SEGMENT_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
IDX_ADJUST_CLAIMNO .0625
IDX_ADJUST_UNDERWRITEENDDATE .0625
IDX_ADJUST_UNDERWRITEIND .0625
IDX_ADJUST_REGISTNO .0625
PK_GCADJUSTMENTMAIN 6
IDX_ADJUST_COMPANYCODE .0625
IDX_ADJUST_LASTOPCOD .0625
IDX_ADJUST_POLICYNO .0625
IDX_ADJUST_INPUTDATE .0625

9 rows selected.

2.开始导入该表：
[oracle@node1 ~]$ cat par_impdp_GCADJUSTMENTMAIN
directory=DUMP_DIR
dumpfile=20140519_GCADJUSTMENTMAIN_%U.dmp
logfile=impdp_GCADJUSTMENTMAIN_20140520.log
tables=tpprod.GCADJUSTMENTMAIN
remap_schema=tpprod:xiezq
REMAP_TABLESPACE=TBLSPACE_OTH:USERS
parallel=4
job_name=impdp_GCADJUSTMENTMAIN
exclude=INDEX
status=30
TABLE_EXISTS_ACTION=truncate

[oracle@node1 ~]$ impdp xiezq parfile=par_impdp_GCADJUSTMENTMAIN

Import: Release 11.2.0.4.0 - Production on Wed May 21 11:11:03 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
......

3.此时可以看到索引数据还是在涨的：
SQL> SELECT segment_name, SUM (bytes) / 1024 / 1024
2 FROM dba_segments
3 WHERE owner = 'XIEZQ' AND segment_name LIKE 'PK%' or segment_name like 'IDX%' group by segment_name;

SEGMENT_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
IDX_ADJUST_CLAIMNO 31
IDX_ADJUST_UNDERWRITEENDDATE 14
IDX_ADJUST_UNDERWRITEIND 15
IDX_ADJUST_REGISTNO 31
PK_GCADJUSTMENTMAIN 33
IDX_ADJUST_COMPANYCODE 23
IDX_ADJUST_LASTOPCOD 19
IDX_ADJUST_POLICYNO 31
IDX_ADJUST_INPUTDATE 20

9 rows selected.

4.结论
虽然加了exclude=index参数，但导入时依然会导入索引数据。
这种情况下，如果不想导入索引数据，在truncate表后，可以把表上的索引drop掉，然后用exclude=index参数导完表数据后，再用include=index参数导入索引。

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

转载于:http://blog.itpub.net/25465866/viewspace-1165892/