imp INDEXES=N CONSTRAINTS=N

   

    今天同事問了一個問題，在imp的時候，為了加快速度，想先匯入資料，最後再建立index或者啟用約束，該如何操作？做了一些測試，得出如下結論：

1. 在imp的時候，是先imp資料，然後再建立index和建立約束的。(我以前一直認為先建立約束，禁用，然後imp完資料以後再啟用)，結論是約束在imp完資料以後再建立，如果違反了約束則會報錯。

2. 如果exp中的約束是透過
    alter table table1 add(constraint ck_name unique(name) deferrable novalidate); 來建立的，即不驗證原始資料，那麼在imp的時候會報錯：
IMP-00003: ORACLE error 2299 encountered
ORA-02299: cannot validate (ECC_VIEW.CK_NAME) - duplicate keys found

3. exp時只定了tables，則其他物件(e.g.view,procedure)不會exp，但是indexes和constraint會exp.

4. 實驗結果
--1. 建立表空間
SQL> conn
SQL> CREATE TABLESPACE "LEIZ" LOGGING DATAFILE '/u02/oradata/nfdb/LEIZ.dbf' SIZE 5M 

--2. 建立測試使用者   
SQL> create user zhanglei identified by ecc default tablespace leiz
SQL> create user ecc_view identified by ecc default tablespace data

--3. 授權
SQL> grant create session to zhanglei;
Grant succeeded
SQL> grant create table to zhanglei;
Grant succeeded
SQL> alter user ecc_view quota 1m on leiz ;
使用者已更改。
SQL> alter user ecc_view quota 1m on data ;
使用者已更改。

--4. user zhanglei exp
SQL> conn
SQL> create table table1 (id varchar2(10), name varchar2(10));
Table created
SQL> insert into table1 values(1,a);
SQL> insert into table1 values(1,b);
SQL> insert into table1 values(1,c);
SQL> insert into table1 values(1,d);
SQL> insert into table1 values(2,e);
SQL> insert into table1 values(2,f);
SQL> insert into table1 values(2,g);
SQL> insert into table1 values(3,h);
SQL> insert into table1 values(3,i);
SQL> insert into table1 values(3,j);
SQL> insert into table1 values(4,k);
SQL> commit;
Commit complete

SQL> select * from table1;
ID      NAME
---     ----
1 a
1 b
1 c
1 d
2 e
2 f
2 g
3 h
3 i
3 j
4 k
11 rows selected

SQL> create index index1 on table1(id) tablespace leiz;
Index created

SQL> create index index2 on table1(name) tablespace data;
INDEX created
 
SQL> ALTER TABLE "ZHANGLEI"."TABLE1" ADD (CONSTRAINT "CK_ID" CHECK(id<10))

[oracle@ntkdb ~]$ exp file=./table1.dmp
Export: Release 10.2.0.1.0 - Production on Mon Jan 11 11:08:56 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ZHANGLEI
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ZHANGLEI
About to export ZHANGLEI's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ZHANGLEI's tables via Conventional Path ...
. . exporting table                         TABLE1         11 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

--5. user ecc_view imp 
SQL> conn as sysdba
SQL> alter user ecc_view quota 1m on leiz ;
使用者已更改。
SQL> alter user ecc_view quota 1m on data ; 
 
a. 正常匯入
[oracle@ntkdb ~]$ imp file=./table1.dmp fromuser=zhanglei touser=ecc_view;
Import: Release 10.2.0.1.0 - Production on Mon Jan 11 11:16:47 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
CONNECTED to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ZHANGLEI, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                       "TABLE1"         11 rows imported
About to enable constraints...
Import terminated successfully without warnings.

b. INDEXES=N
[oracle@ntkdb ~]$ imp file=./table1.dmp fromuser=zhanglei touser=ecc_view INDEXES=N;
Import: Release 10.2.0.1.0 - Production on Mon Jan 11 11:17:41 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
CONNECTED to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ZHANGLEI, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                       "TABLE1"         11 rows imported
About to enable constraints...
Import terminated successfully without warnings.

c. CONSTRAINTS=N
[oracle@ntkdb ~]$ imp file=./table1.dmp fromuser=zhanglei touser=ecc_view CONSTRAINTS=N;
Import: Release 10.2.0.1.0 - Production on Mon Jan 11 11:19:20 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
CONNECTED to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ZHANGLEI, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                       "TABLE1"         11 rows imported
About to enable constraints...
Import terminated successfully without warnings.
驗證約束是否匯入：
SQL> SELECT * FROM USER_CONSTRAINTS;
     0 rows selected

d. 異常情況

(1) 表空間不足
SQL> conn as sysdba
SQL> alter user ecc_view quota 0m on data ;
使用者已更改。

[oracle@ntkdb ~]$ imp file=./table1.dmp fromuser=zhanglei touser=ecc_view
......Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ZHANGLEI, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                       "TABLE1"         11 rows imported
IMP-00017: following statement failed with ORACLE error 1536:
 "CREATE INDEX "INDEX2" ON "TABLE1" ("NAME" )  PCTFREE 10 INITRANS 2 MAXTRANS"
 " 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAUL"
 "T)                    LOGGING"
IMP-00003: ORACLE error 1536 encountered
ORA-01536: space quota exceeded for tablespace 'DATA'
IMP-00017: following statement failed with ORACLE error 20000:
 "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"INDEX2"',NULL,NULL,NULL,11,1,4,1,1"
 ",1,0,0); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: INDEX "ECC_VIEW"."INDEX2" does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 2121
ORA-06512: at "SYS.DBMS_STATS", line 5393
ORA-06512: at line 1
Import terminated successfully with warnings.

(2) 表中存在NOVALIDATE的約束
SQL> conn as sysdba
SQL> ALTER TABLE "ZHANGLEI"."TABLE1" ADD (CONSTRAINT "CK_NAME" UNIQUE("NAME") DEFERRABLE  NOVALIDATE) ;
使用者已更改。
   
[oracle@ntkdb ~]$ imp file=./table1.dmp fromuser=zhanglei touser=ecc_view
Import: Release 10.2.0.1.0 - Production on Mon Jan 11 11:12:43 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ZHANGLEI, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                       "TABLE1"         11 rows imported
IMP-00017: following statement failed with ORACLE error 2299:
 "ALTER TABLE "TABLE1" ADD  CONSTRAINT "CK_NAME" UNIQUE ("NAME") DEFERRABLE U"
 "SING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELIS"
 "TS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "LEIZ" LOGGING ENABL"
 "E "
IMP-00003: ORACLE error 2299 encountered
ORA-02299: cannot validate (ECC_VIEW.CK_NAME) - duplicate keys found
About to enable constraints...
Import terminated successfully with warnings.

e. 說明：exp時指定table時，也會exp index和constraints
[oracle@ntkdb ~]$ exp file=./table1.dmp tables=table1;
Export: Release 10.2.0.1.0 - Production on Mon Jan 11 14:53:49 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
CONNECTED to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table                         TABLE1         12 rows exported
Export terminated successfully without warnings.

[oracle@ntkdb ~]$ imp file=./table1.dmp fromuser=zhanglei touser=ecc_view;
Import: Release 10.2.0.1.0 - Production on Mon Jan 11 14:53:59 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
CONNECTED to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ZHANGLEI, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing ZHANGLEI's objects into ECC_VIEW
. . importing table                       "TABLE1"         12 rows imported
About to enable constraints...
Import terminated successfully without warnings.