WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606
全部博文(599)
分类: Oracle
2011-05-13 22:02:40
在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.