oracle only_data,11G IMP新增DATA_ONLY选项

在11G之前，IMP的时候，如果表已经存在，那么导入会报错，此时如果我们要继续导入数据的话，需要设置IGNORE=Y。

而在11G中，IMP的时候，我们可以只导入数据。 看一个简单的例子：

[oracle@dbtest ~]$ sqlplus test/test

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 13 21:55:07 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE T(ID INT,NAME VARCHAR2(30));

Table created.

SQL> INSERT INTO T SELECT ROWNUM,OBJECT_NAME FROM ALL_OBJECTS WHERE ROWNUM<=10;

10 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM T;

ID NAME

---------- ------------------------------

1 ICOL$

2 I_USER1

3 CON$

4 UNDO$

5 C_COBJ#

6 I_OBJ#

7 PROXY_ROLE_DATA$

8 I_IND1

9 I_CDEF2

10 I_OBJ5

10 rows selected.

SQL> host exp test/test file=/tmp/t.dmp tables=t

Export: Release 11.2.0.1.0 - Production on Fri May 13 21:58:28 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table                              T         10 rows exported

Export terminated successfully without warnings.

SQL> truncate table t;

Table truncated.

如果不加IGNORE=Y ，那么导入会报错。

SQL> host imp test/test file=/tmp/t.dmp tables=t

Import: Release 11.2.0.1.0 - Production on Fri May 13 21:59:13 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing TEST's objects into TEST

. importing TEST's objects into TEST

IMP-00015: following statement failed because the object already exists:

"CREATE TABLE "T" ("ID" NUMBER(*,0), "NAME" VARCHAR2(30))  PCTFREE 10 PCTUSE"

"D 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS "

"1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                       "

"  LOGGING NOCOMPRESS"

Import terminated successfully with warnings.

在11G之前，我们只能采用如下的方法进行导入。

SQL> host imp test/test file=/tmp/t.dmp tables=t ignore=y

Import: Release 11.2.0.1.0 - Production on Fri May 13 22:00:11 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing TEST's objects into TEST

. importing TEST's objects into TEST

. . importing table                            "T"         10 rows imported

Import terminated successfully without warnings.

SQL> select * from t;

ID NAME

---------- ------------------------------

1 ICOL$

2 I_USER1

3 CON$

4 UNDO$

5 C_COBJ#

6 I_OBJ#

7 PROXY_ROLE_DATA$

8 I_IND1

9 I_CDEF2

10 I_OBJ5

10 rows selected.

11G之后我们还可以采用DATA_ONLY选项只导入数据。

SQL> host imp test/test file=/tmp/t.dmp tables=t data_only=y

Import: Release 11.2.0.1.0 - Production on Fri May 13 22:01:31 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing TEST's objects into TEST

. . importing table                            "T"         10 rows imported

Import terminated successfully without warnings.

SQL> select * from t;

ID NAME

---------- ------------------------------

1 ICOL$

2 I_USER1

3 CON$

4 UNDO$

5 C_COBJ#

6 I_OBJ#

7 PROXY_ROLE_DATA$

8 I_IND1

9 I_CDEF2

10 I_OBJ5

10 rows selected.