以交互方式使用exp/imp的演示
以交互方式使用exp/imp的演示
总所周知,用exp/imp对数据库进行逻辑备份,包括表,用户,整个数据库,我们通常所熟悉的是使用命令行指定参数的方式来做的。下面我来演示一下不太常用的以交互方式的操作,操作很简单,就是只要输入exp/imp的命令,以交互方式提供导入导出所需的参数来完成。虽然这种方式没有实际的应用意义,但作为Oracle提供的一种方法,我们也是有必要熟悉一下的。 ----------------------------华丽丽的分割线----------------------------Oracle 单实例 从32位 迁移到 64位 方法
在CentOS 6.4下安装Oracle 11gR2(x64)
Oracle 11gR2 在VMWare虚拟机中安装步骤
Debian 下 安装 Oracle 11g XE R2
Oracle导入导出expdp IMPDP详解
Oracle 10g expdp导出报错ORA-4031的解决方法
Oracle 10gr2 rac expdp 报错UDE-00008 ORA-31626
Oracle中利用expdp/impdp备份数据库的使用说明
Oracle备份还原(expdp/impdp)
----------------------------Expdp/Impdp的相关参数----------------------------
SQL> select username,default_tablespace from dba_users where username='ZLM'; USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ ZLM ZLM SQL> create table t1 as select * from dba_objects; Table created. SQL> create table t2 as select * from user_objects; Table created. SQL> select count(*) from t1; COUNT(*) ---------- 50355 SQL> select count(*) from t2; COUNT(*) ---------- 2 SQL> ! 注意,由于之前指定过zlm用户的缺省表空间为ZLM,因此这2个测试表是会创建到ZLM表空间中去的 --执行导出t1表 [oracle@ora10g ~]$ exp Export: Release 10.2.0.1.0 - Production on 11 11:04:17 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. Username: zlm Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Enter array fetch buffer size: 4096 > 8192 Export file: expdat.dmp > t1.dmp -指定导出的dmp文件名 (1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > t --可以输入“T”,也可以输入“3” Export table data (yes/no): yes > Compress extents (yes/no): yes > Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ... Table(T) or Partition(T:P) to be exported: (RETURN to quit) > --如果不输入表名直接回车表示会退出的,不导出任何表 Export terminated successfully without warnings. [oracle@ora10g ~]$ exp --由于刚才直接退出了,再重新导一次 Export: Release 10.2.0.1.0 - Production on 11 11:05:00 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. Username: zlm Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Enter array fetch buffer size: 4096 > 8192 --指定buffer,默认是4k,这里指定了8k,实际使用时还可以设置更大的值,如1000000,即10M Export file: expdat.dmp > t1.dmp (1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3 Export table data (yes/no): yes > Compress extents (yes/no): yes > Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ... Table(T) or Partition(T:P) to be exported: (RETURN to quit) > t1 --指定需要导出的t1测试表 . . exporting table T1 50355 rows exported Table(T) or Partition(T:P) to be exported: (RETURN to quit) > --没有其他想导出的表,就回车退出 Export terminated successfully without warnings. [oracle@ora10g ~]$ exit exit --删除测试表t1 SQL> drop table t1 purge; Table dropped. SQL> select count(*) from t1; select count(*) from t1 * ERROR at line 1: ORA-00942: table or view does not exist --执行导入t1表 SQL> ! [oracle@ora10g ~]$ imp Import: Release 10.2.0.1.0 - Production on 11 11:06:12 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. Username: zlm Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Import file: expdat.dmp > t1.dmp --指定刚才由exp生成的dmp文件作为导入的源 Enter insert buffer size (minimum is 8192) 30720> --指定insert的buffer,直接回车采用默认值30720 Export file created by EXPORT:V10.02.01 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set List contents of import file only (yes/no): no > --默认是no,如果选择了yes,那么就没有以下3个选项了 Ignore create error due to object existence (yes/no): no > Import grants (yes/no): yes > Import table data (yes/no): yes > Import entire export file (yes/no): no > yes --由于导出的时候只是对t1表单独的操作,所以选择yes也仅仅是导入t1表而已 . importing ZLM's objects into ZLM . importing ZLM's objects into ZLM . . importing table "T1" 50355 rows imported Import terminated successfully without warnings. [oracle@ora10g ~]$ exit exit SQL> select count(*) from t1;
COUNT(*) ---------- 50355 SQL> ! 现在表已经成功地被重新导入到数据库中的zlm用户中,相当于利用了原来对t1表的逻辑备份,对误删除的t1表进行恢复,如果再对已经存在的t1表执行一次导入操作,会发生什么情况呢? [oracle@ora10g ~]$ imp Import: Release 10.2.0.1.0 - Production on 11 11:07:12 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. Username: zlm Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Import file: expdat.dmp > t1.dmp Enter insert buffer size (minimum is 8192) 30720> Export file created by EXPORT:V10.02.01 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set List contents of import file only (yes/no): no > yes (选yes就没有了刚才的3个选项了) Import entire export file (yes/no): no > yes --依然是选择导入整个导出文件t1.dmp(即:文件中仅有t1表的逻辑语句) . importing ZLM's objects into ZLM . importing ZLM's objects into ZLM "CREATE TABLE "T1" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBO" "BJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJ" "ECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" V" "ARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VAR" "CHAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRA" "NS 255 STORAGE(INITIAL 6291456 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DE" "FAULT) TABLESPACE "ZLM" LOGGING NOCOMPRESS" . . skipping table "T1" Import terminated successfully without warnings. 这里虽然提示“无警告地成功地结束了命令”,但并没有执行真正的导入,原因是之前的t1表已经存在,默认如果发生object existance的error,那么导入就会终止,这时候我们可以通过选择“Ignore create error due to object existence (yes/no): no > yes”来解决,这个交互模式中的yes就相当于我们熟悉的命令行中的参数ignore=y,表示使用追加的方式导入,并不会覆盖原来的数据,也不会因为对象已经存在而终止导入 [oracle@ora10g ~]$ imp Import: Release 10.2.0.1.0 - Production on 11 11:07:38 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. Username: zlm Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Import file: expdat.dmp > t1.dmp Enter insert buffer size (minimum is 8192) 30720> Export file created by EXPORT:V10.02.01 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set List contents of import file only (yes/no): no > --选择no才能出现更多的参数选项,否则直接跳到是否导入整个文件 Ignore create error due to object existence (yes/no): no > yes Import grants (yes/no): yes > Import table data (yes/no): yes > Import entire export file (yes/no): no > yes . importing ZLM's objects into ZLM . importing ZLM's objects into ZLM . . importing table "T1" 50355 rows imported Import terminated successfully without warnings. 同样是提示“无警告地成功地结束了命令”,但这次真正导出了t1表,我们看到了“"T1" 50355 rows imported”的字样 [oracle@ora10g ~]$ exit exit SQL> select count(*) from t1; COUNT(*) ---------- 100710 此时表中的数据为10W行,比原来的5W行多了一倍,因为又重复导入了一次,相当于把原来的数据又复制了一份。但要注意,如果表中有主键或者唯一约束的话,这种方式导入是不推荐的,可能会报错,未测试。
更多详情见请继续阅读下一页的精彩内容:
|
评论暂时关闭