IMP的indexfile参数使用

imp的indexfile参数指定了导入时保持create index的SQL脚本

indexfile参数默认值：none
指定存储创建索引的文件。如果 CONSTRAINTS=y，约束也会存储在索引文件中。为了方便识别索引，文件有CREATE TABLE，CREATE CLUSTER 语句。比如
当indexes=n时
[oracle@lzl ~]$ vi parfile_imp.txt 
[oracle@lzl ~]$ cat parfile_imp.txt 
userid="scott/tiger"
file=/home/oracle/exp_lzl.dmp
log=/home/oracle/imp_lzl.log
buffer=102400
tables=emp
fromuser=scott
touser=smith
indexes=n
indexfile=/home/oracle/imp_indexfile.sql
[oracle@lzl ~]$ imp parfile=parfile_imp.txt 
Import: Release 11.2.0.3.0 - Production on Tue Jul 11 15:36:14 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by SYS, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . skipping table "EMP"                              
Import terminated successfully without warnings.
[oracle@lzl ~]$ cat imp_indexfile.sql 
REM  CREATE TABLE "SMITH"."EMP" ("EMPNO" NUMBER(4, 0), "ENAME" 
REM  VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, 
REM  "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0)) 
REM  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 
REM  NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL 
REM  DEFAULT) TABLESPACE "USERS_LZL" LOGGING NOCOMPRESS ;
REM  ... 14 rows
REM  ALTER TABLE "SMITH"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY 
REM  ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
REM  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST 
REM  GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS_LZL" LOGGING ENABLE ;
REM  ALTER TABLE "SMITH"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY 
REM  ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE ;
REM  ALTER TABLE "SMITH"."EMP" ENABLE CONSTRAINT "FK_DEPTNO" ;
indexfile中只会创建表和约束的定义且被REM注释
当indexes=y时
[oracle@lzl ~]$ vi parfile_imp.txt 
[oracle@lzl ~]$ cat parfile_imp.txt 
userid="scott/tiger"
file=/home/oracle/exp_lzl.dmp
log=/home/oracle/imp_lzl.log
buffer=102400
tables=emp
fromuser=scott
touser=smith
indexes=y
indexfile=/home/oracle/imp_indexfile_y.sql
[oracle@lzl ~]$ cat imp_indexfile_y.sql 
REM  CREATE TABLE "SMITH"."EMP" ("EMPNO" NUMBER(4, 0), "ENAME" 
REM  VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, 
REM  "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0)) 
REM  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 
REM  NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL 
REM  DEFAULT) TABLESPACE "USERS_LZL" LOGGING NOCOMPRESS ;
REM  ... 14 rows
CONNECT SMITH;
CREATE UNIQUE INDEX "SMITH"."PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 
INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS_LZL" 
LOGGING ;
REM  ALTER TABLE "SMITH"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY 
REM  ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
REM  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST 
REM  GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS_LZL" LOGGING ENABLE ;
REM  ALTER TABLE "SMITH"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY 
REM  ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE ;
REM  ALTER TABLE "SMITH"."EMP" ENABLE CONSTRAINT "FK_DEPTNO" ;
indexfile中能执行的只有connect和create index语句。
怎么使用这个indexfile特性？
1.指定 INDEXFILE参数导出，报错create index语句到文件中。 如果指明了indexfile参数，导入不会实际发生，仅仅只会生成创建对象的SQL脚本文件。
2.编辑文件，更改connect的密码。
3.使用indexes=n，重新导入。这个时候没有导入索引，只导入了其他对象
4.执行第一步的SQL脚本创建索引
以上步骤其实就是完成了imp时indexes=y，只不过是手动执行了index的创建。


indexfile参数其实是很有用的。在实际imp之前使用indexfile参数，可以保留imp之时的DDL语句，对于校验导入的对象有很好的效果。
甚至在某个导入命令的某个DDL部分执行失败了，可以提取indexfile中对应的sql语句去手动执行

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

转载于:http://blog.itpub.net/31461640/viewspace-2141910/