在imp的时候，为了加快速度，想先导入数据，最后再创建index或者启用约束

    今天同事问了一个问题，在imp的时候，为了加快速度，想先导入数据，最后再创建index或者启用约束，该如何操作？做了一些，得出如下结论：

1. 在imp的时候，是先imp数据，然后再创建index和创建约束的。(我以前一直认为先创建约束，禁用，然后imp完数据以后再启用)，结论是约束在imp完数据以后再创建，如果违反了约束则会报错。

2. 如果exp中的约束是通过
    alter table table1 add(constraint ck_name unique(name) deferrable novalidate); 来创建的，即不验证原始数据，那么在imp的时候会报错：
IMP-00003: 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 ~]$expfile=./table1.dmp
Export: Release 10.2.0.1.0 - Production on Mon Jan 11 11:08:56 2010
Copyright (c) 1982, , Oracle.  All rights reserved.
Connected to: Oracle Database 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> connas sysdba
SQL> alter user ecc_view quota 1m on leiz ;
用户已更改。
SQL> alter user ecc_view quota 1m on data ; 
 
a. 正常导入
[oracle@ntkdb ~]$impfile=./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 ~]$impfile=./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 ~]$ impfile=./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> connas sysdba
SQL> alter user ecc_view quota 0m on data ;
用户已更改。

[oracle@ntkdb ~]$impfile=./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> connas sysdba
SQL>ALTER TABLE "ZHANGLEI"."TABLE1" ADD (CONSTRAINT "CK_NAME" UNIQUE("NAME") DEFERRABLE  NOVALIDATE) ;
用户已更改。
   
[oracle@ntkdb ~]$impfile=./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 ~]$ expfile=./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 ~]$impfile=./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.