数据泵expdp/impdp基本操作

最新推荐文章于 2023-11-24 23:41:32 发布
最新推荐文章于 2023-11-24 23:41:32 发布
阅读量1.1k 收藏 4
点赞数
分类专栏： Oracle 11g
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/leo__1990/article/details/91403050
版权

1.创建备份目录   

  

[root@rac1 ~]# cd /u01
[root@rac1 u01]# mkdir backup
[root@rac1 u01]# ls
app  backup
[root@rac1 u01]# chown oracle:oinstall backup

2.准备数据  

SQL>select * from dba_data_files;
SQL> create tablespace leo datafile'+DATA/leo/datafile/cndba01.dbf' size 500m; 
Tablespace created.
SQL> create user leo identified by leo ;
User created.
SQL> grant connect,resource to leo;
Grant succeeded.
SQL> conn leo/leo
Connected.
SQL> create table leo tablespace leo as select * from all_objects;
Table created.
SQL> select count(*) from leo;
  COUNT(*)
----------
     68339
SQL> create directory backup as '/u01/backup';
Directory created.
SQL> grant read,write on directory backup to leo;
Grant succeeded.

3数据泵基本操作

3.1整库库导出与导入   

expdp /'/as sysdba/'  directory=backup dumpfile=leofull.dmp logfile=leofull.log full=y 
--删除表空间进行测试 
SQL> drop tablespace leo including contents and datafiles; 
Tablespace dropped. 
impdp /'/as sysdba/'  directory=backup dumpfile=leo.dmp logfile=leoimp.log full=y 
--数据恢复成功 
SQL> select count(*) from leo; 
  COUNT(*) 
---------- 
     68340

3.2用户级别导出与导入   

--导出用户 
两种方式 
expdp /'/as sysdba/' schemas=leo directory=backup dumpfile=leo2.dmp logfile=leo2.log 
--用户leo 要赋操作目录的权限 
expdp leo/leo schemas=leo directory=backup dumpfile=leo.dmp logfile=leoexp.log 
--导入用户 
--同库测试先 
SQL> drop user leo cascade; 
User dropped. 
SQL> conn /as sysdba 
Connected. 
--导入用户之前要先建用户 
SQL> create user leo identified by leo ; 
User created. 
SQL> grant read,write on directory backup to leo; 
Grant succeeded. 
impdp /'/as sysdba/' schemas=leo directory=backup dumpfile=leo.dmp logfile=leoimp.log 
--用户leo 要赋操作目录的权限 
impdp leo/leo schemas=leo directory=backup dumpfile=leo.dmp logfile=leoimp.log 
SQL> select count(*) from leo; 
  COUNT(*) 
---------- 
     68339

3.3表级导出与导入   

导出表 
expdp /'/as sysdba/'  directory=backup dumpfile=leotable.dmp logfile=leotable.log tables=leo.leo 
expdp leo/leo directory=backup dumpfile=leotable2.dmp logfile=leotable2.log  tables=leo 
导入表 
本库测试 
drop table leo; 
impdp /'/as sysdba/'  directory=backup dumpfile=leotable.dmp logfile=leotable.log tables=leo.leo 
impdp leo/leo directory=backup dumpfile=leotable2.dmp logfile=leotable2.log  tables=leo 
SQL> select count(*) from leo; 
  COUNT(*) 
---------- 
     68339

4.4表空间导出与导入   

 expdp  /'/as sysdba/'  directory=backup dumpfile=tbspace1.dmp logfile=tbspace1.log tablespaces=leo 
[oracle@rac1 ~]$ expdp  /'/as sysdba/'  directory=backup dumpfile=tbspace1.dmp logfile=tbspace1.log tablespaces=leo 
Export: Release 11.2.0.4.0 - Production on Wed May 3 11:00:27 2017 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 
Data Mining and Real Application Testing options 
Starting "SYS"."SYS_EXPORT_TABLESPACE_01":  "/******** AS SYSDBA" directory=backup dumpfile=tbspace1.dmp logfile=tbspace1.log tablespaces=leo 
Estimate in progress using BLOCKS method... 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
Total estimation using BLOCKS method: 8 MB 
Processing object type TABLE_EXPORT/TABLE/TABLE 
. . exported "LEO"."LEO"                                 6.706 MB   68340 rows 
Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded 
****************************************************************************** 
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is: 
  /u01/backup/tbspace1.dmp 
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at Wed May 3 11:00:34 2017 elapsed 0 00:00:05 
SQL> drop tablespace leo including contents and datafiles; 
Tablespace dropped. 
--在导入表空间之前要先建表空间，在导入表空间回复表空间数据 
SQL> create tablespace leo datafile'+DATA/cndba/datafile/cndba01.dbf' size 100m; 
Tablespace created. 
 impdp  /'/as sysdba/'  directory=backup dumpfile=tbspace1.dmp logfile=tbspace2.log tablespaces=leo 
[oracle@rac1 backup]$ impdp  /'/as sysdba/'  directory=backup dumpfile=tbspace1.dmp logfile=tbspace2.log tablespaces=leo 
Import: Release 11.2.0.4.0 - Production on Wed May 3 11:06:44 2017 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 
Data Mining and Real Application Testing options 
Master table "SYS"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded 
Starting "SYS"."SYS_IMPORT_TABLESPACE_01":  "/******** AS SYSDBA" directory=backup dumpfile=tbspace1.dmp logfile=tbspace2.log tablespaces=leo 
Processing object type TABLE_EXPORT/TABLE/TABLE 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
. . imported "LEO"."LEO"                                 6.706 MB   68340 rows 
Job "SYS"."SYS_IMPORT_TABLESPACE_01" successfully completed at Wed May 3 11:06:48 2017 elapsed 0 00:00:02 
SQL> select count(*) from leo; 
  COUNT(*) 
---------- 
     68339

5.5导入过程中指定remap user 和 remap tablespace 以及忽略remap功能的TRANSFORM=segment_attributes:n 使用。  

--remap user 
SQL> create user leo1 identified by leo1; 
User created. 
SQL>  grant read,write on directory backup to leo1; 
Grant succeeded. 
SQL> grant connect,resource to leo1; 
Grant succeeded. 
SQL> exit 
[oracle@rac1 backup]$ impdp leo1/leo1 remap_schema=leo:leo1 directory=backup dumpfile=leo.dmp logfile=leoexp.log 
Import: Release 11.2.0.4.0 - Production on Wed May 3 11:44:10 2017 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 
Data Mining and Real Application Testing options 
Master table "LEO1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded 
Starting "LEO1"."SYS_IMPORT_FULL_01":  leo1/******** remap_schema=leo:leo1 directory=backup dumpfile=leo.dmp logfile=leoexp.log 
Processing object type TABLE_EXPORT/TABLE/TABLE 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
. . imported "LEO1"."LEO"                                6.706 MB   68340 rows 
Job "LEO1"."SYS_IMPORT_FULL_01" successfully completed at Wed May 3 11:44:15 2017 elapsed 0 00:00:03 
[oracle@rac1 backup]$ sqlplus /nolog 
SQL*Plus: Release 11.2.0.4.0 Production on Wed May 3 11:44:26 2017 
Copyright (c) 1982, 2013, Oracle.  All rights reserved. 
SQL> conn leo1/leo1 
Connected. 
--remap  tablespace 
SQL> set lines 200 
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='LEO'; 
USERNAME         DEFAULT_TABLESPACE       TEMPORARY_TABLESPACE 
------------------------------ ------------------------------ ------------------------------ 
LEO          USERS         TEMP 
SQL> select count(*) from leo; 
  COUNT(*) 
---------- 
     68340 
expdp /'/as sysdba/' directory=backup dumpfile=leo_user.dmp schemas=leo1 
SQL> drop user leo1 cascade; 
User dropped. 
SQL> SET LINES 200 
impdp /'/as sysdba/' directory=backup dumpfile=leo_user.dmp logfile=leoimp.log remap_tablespace=users:leo 
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='LEO1'; 
USERNAME         DEFAULT_TABLESPACE       TEMPORARY_TABLESPACE 
------------------------------ ------------------------------ ------------------------------ 
LEO1          LEO         TEMP 

--忽略remap功能的TRANSFORM=segment_attributes:n 使用 参数说明参考链接： 
http://www.cndba.cn/Expect-le/article/1890 
SQL> set long 9999; 
SQL>  SELECT dbms_metadata.get_ddl('TABLE','LEO','LEO') FROM dual; 
DBMS_METADATA.GET_DDL('TABLE','LEO','LEO') 
-------------------------------------------------------------------------------- 
  CREATE TABLE "LEO"."LEO" 
   ( "OWNER" VARCHAR2(30) NOT NULL ENABLE, 
 "OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE, 
 "SUBOBJECT_NAME" VARCHAR2(30), 
 "OBJECT_ID" NUMBER NOT NULL ENABLE, 
 "DATA_OBJECT_ID" NUMBER, 
 "OBJECT_TYPE" VARCHAR2(19), 
 "CREATED" DATE NOT NULL ENABLE, 
 "LAST_DDL_TIME" DATE NOT NULL ENABLE, 
 "TIMESTAMP" VARCHAR2(19), 
 "STATUS" VARCHAR2(7), 
 "TEMPORARY" VARCHAR2(1), 
 "GENERATED" VARCHAR2(1), 
 "SECONDARY" VARCHAR2(1), 
 "NAMESPACE" NUMBER NOT NULL ENABLE, 
 "EDITION_NAME" VARCHAR2(30) 
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
  TABLESPACE "LEO" 
expdp /'/as sysdba/' dumpfile=leo.dmp logfile=exp_leo.log directory=backup tables=leo.leo 
SQL> drop tablespace leo including contents and datafiles; 
Tablespace dropped. 
不加TRANSFORM=SEGMENT_ATTRIBUTES:N:table： 
看到了报错：提示leo表空间不存在，无法导入 
[oracle@rac1 ~]$ impdp /'/as sysdba/' dumpfile=leo.dmp logfile=exp_leo.log directory=backup tables=leo.leo 
Import: Release 11.2.0.4.0 - Production on Wed May 3 10:45:30 2017 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 
Data Mining and Real Application Testing options 
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded 
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" dumpfile=leo.dmp logfile=exp_leo.log directory=backup tables=leo.leo 
Processing object type TABLE_EXPORT/TABLE/TABLE 
ORA-39083: Object type TABLE:"LEO"."LEO" failed to create with error: 
ORA-00959: tablespace 'LEO' does not exist 
Failing sql is: 
CREATE TABLE "LEO"."LEO" ("OWNER" VARCHAR2(30 BYTE) NOT NULL ENABLE, "OBJECT_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE, "SUBOBJECT_NAME" VARCHAR2(30 BYTE), "OBJECT_ID" NUMBER NOT NULL ENABLE, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19 BYTE), "CREATED" DATE NOT NULL ENABLE, "LAST_DDL_TIME" DATE NOT NULL ENABLE, "TIMESTAMP" VARCHAR2(19 BYTE), "STATUS" VARCHAR2(7 BYTE), "TEMPOR 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Wed May 3 10:45:34 2017 elapsed 0 00:00:02 
加TRANSFORM=SEGMENT_ATTRIBUTES:N:table： 使用默认表空间users
[oracle@rac1 ~]$ impdp /'/as sysdba/' dumpfile=leo.dmp logfile=imp_leo.log directory=backup tables=leo.leo TRANSFORM=SEGMENT_ATTRIBUTES:N:table 
Import: Release 11.2.0.4.0 - Production on Wed May 3 10:46:18 2017 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 
Data Mining and Real Application Testing options 
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded 
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" dumpfile=leo.dmp logfile=imp_leo.log directory=backup tables=leo.leo TRANSFORM=SEGMENT_ATTRIBUTES:N:table 
Processing object type TABLE_EXPORT/TABLE/TABLE 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
. . imported "LEO"."LEO"                                 6.706 MB   68340 rows 
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed May 3 10:46:22 2017 elapsed 0 00:00:03 
SQL> SELECT dbms_metadata.get_ddl('TABLE','LEO','LEO') FROM dual; 
DBMS_METADATA.GET_DDL('TABLE','LEO','LEO') 
-------------------------------------------------------------------------------- 
  CREATE TABLE "LEO"."LEO" 
   ( "OWNER" VARCHAR2(30) NOT NULL ENABLE, 
 "OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE, 
 "SUBOBJECT_NAME" VARCHAR2(30), 
 "OBJECT_ID" NUMBER NOT NULL ENABLE, 
 "DATA_OBJECT_ID" NUMBER, 
 "OBJECT_TYPE" VARCHAR2(19), 
 "CREATED" DATE NOT NULL ENABLE, 
 "LAST_DDL_TIME" DATE NOT NULL ENABLE, 
 "TIMESTAMP" VARCHAR2(19), 
DBMS_METADATA.GET_DDL('TABLE','LEO','LEO') 
-------------------------------------------------------------------------------- 
 "STATUS" VARCHAR2(7), 
 "TEMPORARY" VARCHAR2(1), 
 "GENERATED" VARCHAR2(1), 
 "SECONDARY" VARCHAR2(1), 
 "NAMESPACE" NUMBER NOT NULL ENABLE, 
 "EDITION_NAME" VARCHAR2(30) 
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
DBMS_METADATA.GET_DDL('TABLE','LEO','LEO') 
-------------------------------------------------------------------------------- 
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
  TABLESPACE "USERS"
Leo-2016
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle利用impdp 导入数据库的归纳
wonderjjm的专栏
01-09 1万+
我们知道，当我们要导入一个数据库的时候，说起来很简单，但是其中会遇到各种问题，这个就占个位置慢慢整理吧： 1, 利用impdp导入数据库分类     a, 完整导入：就是加入full=y这个关键词，不能和schemas, tables,等关键词重用  impdp \"/ as sysdba\" full=y directory=expdump dumpfile=test1.dmp logfi
账单中心数据泵expdp和impdp异机导入导出工作案例
06-30
账单中心数据泵expdp和impdp异机导入导出工作案例
Oracle expdp/impdp使用
Sxcan的博客
12-04 821
使用前需要确定导入导出的文件路径，oracle通过directory来识别系统上的文件路径。 1.查看有哪些目錄可供使用 SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH SYS XMLDIR /oracle/11204/rdbms/xml SYS ORACLE_OCM_CONFIG_DIR /oracle/11204/ccr/hosts/zz
Oracle expdp和impdp
独孤清扬玩DB
12-16 2032
文章目录1、EXPDP/IMPDP用法详解1.1、expdp/impdp模式1.2、监控expdp/impdp任务2、EXPDP/IMPDP使用技巧和案例2.0、使用技巧2.1、库、表空间、用户、表等导出导入2.2、过滤对象2.2.1、使用include2.2.2、使用query选项2.2.3、使用EXCLUDE排除对象2.2.4、使用content来过滤导出元数据还是只有数据2.3、remap对...
oracle expdp impdp总结
发歌的数据架构
07-23 1179
经常使用数据泵导数据，一直以来都是零零散散的问题，今天趁周末来个详细的总结 使用EXPDP和IMPDP时应该注意的事项： EXP和IMP是客户端工具程序，它们既可以在客户端使用，也可以在服务端使用。 EXPDP和IMPDP是服务端的工具程序，他们只能在ORACLE服务端使用，不能在客户端使用。 IMP只适用于EXP导出的文件，不适用于EXPDP导出文件；IMPDP只适用于EXPDP导出
进入expdp的工作模式
最新发布
cmx996007的博客
11-24 366
【代码】进入expdp的工作模式。
oracle导出导入工具expdp/impdp
jc_benben的专栏
02-24 847
expdp使用说明 一，前提，跟exp不同，需要使用目录 -- 创建目录 create directory dump_dir as 'c:/bak'; -- 查询目录 select * from dba_directories; -- 授权目录读写权限给别的用户 grant read,write on directory dump_dir to user1; 二,常用操作 expdp导...
ORACLE EXPDP AND IMPDPD
qq_21608393的博客
06-08 642
Oracle dump
expdp / as sysdba
cuiyan1982的专栏
03-21 8348
原文地址：http://jotey.blog.163.com/blog/static/14926370201262315327525/Export as SYS or not?but this mean that I can't use expdp to exporting from 10.1 to 10.2 using SYS?It seems so, it generates ORA-3913...
Oracle使用数据泵导入/导出数据（expdp/impdp）
lixilai_rjkf的博客
07-13 925
Oracle使用数据泵导入/导出数据（expdp/impdp） A电脑上的操作（expdp数据导出） 运行cmd； 登录数据库，输入命令：sqlplus 使用管理员角色登录需要在用户名后加“ as sysdba” 例如：sys as sysdba; 创建目录路径：输入命令：create directory data_dir as 'E:\ora\data' ; data_dir为路径名称，可自命名，E:\ora\data为数据库导出文件存放路径（路径必须存在
[Oacle] [详细] impdp/expdp 使用详解
qq_39194322的博客
09-17 7592
大部分是转的，各种，记录下。 EXPDP 可以导出所有数据库的数据，只要它能连接到这个数据库，或者它登录的数据库 上能读取到被导出的库的数据，因为此时可以通过数据库链来读取被导出的库的数据。 http://zalbb.itpub.net/post/980/395955 a. 创建到服务端的dblink create database link link_name connect to username identified by password using 'connect_string' ;.
Linux：Oracle导出导入数据泵（EXPDP，IMPDP）
12-14
数据泵技术是Oracle Database 10g 中的新技术，它比原来导入/导出(imp,exp)技术快15－45倍。速度的提高源于使用了并行技术来读写导出转储文件。 expdp/impdp和exp/imp的区别 exp和imp是客户端工具程序，它们既可以在...
Oracle expdp-impdp 数据泵导入导出（远程）
12-14
2、创建逻辑目录用于导出导入数据（目录一定要真实存在） create or replace directory out_dir as 'C:\out'; --out_dir（逻辑目录名，可以随便取） 3、也可以将这个逻辑目录授权给其他用户 grant read,write on ...
oracle数据泵expdp impdp使用说明
05-15
oracle数据泵expdp impdp使用说明
IMPDP导入用户数据到不同的Schema
Sebastien23的博客
04-13 2862
回顾一下使用EXPDP和IMPDP导入导出用户数据的方法。
IMPDP
u010037629的博客
12-05 694
IMPDP 只能把低版本中的数据导入高版本中 如果高版本导入低版本的话 需要加一个参数 version=11.2.0.4.0 hdsoaware_test create directory BACKUP_DIR as ‘/oracle/oradata/hdsoaware/backup_dir’ 导入impdp [oracle@localhost ~]$ impdp ‘sys/sys as sysd...
Oracle 数据泵expdp、impdp 导入导出 保姆及别
weixin_41790661的博客
05-05 2005
Oracle 数据泵导入导出 expdp impdp 你想要的都在这里 保姆及别
expdp/impdp使用sysdba权限迁移数据
congjianxie6566的博客
08-14 2921
expdp 'userid="/ as sysdba"' directory=DATA_PUMP_DIR full=y logfile=fullexp.log estimate_only=yes; expdp \'/ as s...
oracle11g数据库pdb,oracle数据泵从11g迁移数据库到19c的PDB(expdp/impdp)
05-27
关于从Oracle 11g迁移数据库到Oracle 19c的过程，您可以使用Oracle数据泵（expdp/impdp）进行操作，以下是具体步骤： 1. 在Oracle 11g中，使用expdp命令将需要迁移的PDB导出到一个.dmp文件中： ``` expdp system/...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值