IMP的indexfile參數(shù)怎么用

小編給大家分享一下IMP的indexfile參數(shù)怎么用，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

imp的indexfile參數(shù)指定了導(dǎo)入時(shí)保持create index的SQL腳本

indexfile參數(shù)默認(rèn)值：none
指定存儲(chǔ)創(chuàng)建索引的文件。如果 CONSTRAINTS=y，約束也會(huì)存儲(chǔ)在索引文件中。為了方便識(shí)別索引，文件有CREATE TABLE，CREATE CLUSTER 語(yǔ)句。比如
當(dāng)indexes=n時(shí)
[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中只會(huì)創(chuàng)建表和約束的定義且被REM注釋
當(dāng)indexes=y時(shí)
[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中能執(zhí)行的只有connect和create index語(yǔ)句。
怎么使用這個(gè)indexfile特性？
1.指定 INDEXFILE參數(shù)導(dǎo)出，報(bào)錯(cuò)create index語(yǔ)句到文件中。 如果指明了indexfile參數(shù)，導(dǎo)入不會(huì)實(shí)際發(fā)生，僅僅只會(huì)生成創(chuàng)建對(duì)象的SQL腳本文件。
2.編輯文件，更改connect的密碼。
3.使用indexes=n，重新導(dǎo)入。這個(gè)時(shí)候沒(méi)有導(dǎo)入索引，只導(dǎo)入了其他對(duì)象
4.執(zhí)行第一步的SQL腳本創(chuàng)建索引
以上步驟其實(shí)就是完成了imp時(shí)indexes=y，只不過(guò)是手動(dòng)執(zhí)行了index的創(chuàng)建。


indexfile參數(shù)其實(shí)是很有用的。在實(shí)際imp之前使用indexfile參數(shù)，可以保留imp之時(shí)的DDL語(yǔ)句，對(duì)于校驗(yàn)導(dǎo)入的對(duì)象有很好的效果。
甚至在某個(gè)導(dǎo)入命令的某個(gè)DDL部分執(zhí)行失敗了，可以提取indexfile中對(duì)應(yīng)的sql語(yǔ)句去手動(dòng)執(zhí)行

以上是“IMP的indexfile參數(shù)怎么用”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！