imp中的indexfile引數解決imp指定表空間問題

exp和imp工具中可能存在把table從一個庫exp然後imp到另一個資料庫出現沒有指定tablespace而無法imp，
imp的indexfile引數中可以解決的
imp file=d:test.dmp tables=tablename indexfile=d:test.idx rows=n
首先把dmp檔案中的資訊匯入到d:test.idx中去，檢視d:test.idx如下
REM CREATE TABLE "ABC"."INDEXFILE_TEST" ("USER_ID" NUMBER(16, 0) NOT NULL
REM ENABLE, "DEPT_ID" CHAR(32), "STAFF_CODE" VARCHAR2(200) NOT NULL
REM ENABLE, "USER_NAME" VARCHAR2(100) NOT NULL ENABLE, "PASSWORD"
REM VARCHAR2(100) NOT NULL ENABLE, "TITLE" VARCHAR2(30) NOT NULL ENABLE,
REM "PHONE" VARCHAR2(20), "EMAIL_ADDRESS" VARCHAR2(100),
REM "LAST_LOGIN_DATE" DATE, "CREATE_DATE" DATE NOT NULL ENABLE, "STATE"
REM CHAR(1) NOT NULL ENABLE, "STATE_DATE" DATE, "STYLE" CHAR(1),
REM "BELONG_ORG" NUMBER(16, 0), "IS_FULL" CHAR(1)) PCTFREE 10 PCTUSED 40
REM INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "NETBAISDATA" LOGGING
REM NOCOMPRESS ;
把上訴檔案的REM註釋去掉，然後看見後面的tablespace沒有，改成自己想要的tablespace
CREATE TABLE "ABC"."INDEXFILE_TEST" ("USER_ID" NUMBER(16, 0) NOT NULL
ENABLE, "DEPT_ID" CHAR(32), "STAFF_CODE" VARCHAR2(200) NOT NULL
ENABLE, "USER_NAME" VARCHAR2(100) NOT NULL ENABLE, "PASSWORD"
VARCHAR2(100) NOT NULL ENABLE, "TITLE" VARCHAR2(30) NOT NULL ENABLE,
"PHONE" VARCHAR2(20), "EMAIL_ADDRESS" VARCHAR2(100),
"LAST_LOGIN_DATE" DATE, "CREATE_DATE" DATE NOT NULL ENABLE, "STATE"
CHAR(1) NOT NULL ENABLE, "STATE_DATE" DATE, "STYLE" CHAR(1),
"BELONG_ORG" NUMBER(16, 0), "IS_FULL" CHAR(1)) PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "BJ" LOGGING
NOCOMPRESS ;

然後把上述的改過的指令碼放在sql>@d:test.idx
然後查user_tables中tablespace會變成BJ
最後imp file=d:test.dmp tables=tablename ignore=y
就可以把指定表空間的資料匯入到你想要的tablespace中去。

[@more@]