Oracle数据库导入(expdp)和导出(impdp)

文档最后，列出了常用的一些导入导出的场景，以及一些导入导出的属性说明。

一、数据库导出(expdp)

1. 使用sys或system账号登录oracle

             通过"Window + R" 打开dos命令行界面，使用sys或system登录oracle。格式：sqlplus sys/密码@数据库实例名 as sysdba

          

         2、创建逻辑目录 ： create or replace directory data_dir as 'E:\orcl\data';

             data_dir为路径名称，可自命名，E:\orcl\data为数据库导出文件存放路径（路径必须存在）； 

             创建备份逻辑目录，此目录不是真实的目录，此目录需要手动在数据库服务端创建。

              

            通过 select * from dba_directories  可以查看所有的目录.

         3、为用户授予访问数据目录的权限，输入命令：Grant read,write on directory data_dir to dbuser;

        dbuser为数据库用户名（与第4步中相同）

　　　　

          4、导入导出操作授权，输入命令：grant exp_full_database,imp_full_database to dbuser; 

          5、退出，输入命令：exit;

          6、数据导出，执行命令：

      expdp dbuser/123456@orcl schemas=dbuser dumpfile=expdp.dmp directory=data_dir logfile=expdp.log

           注意：命令结束不需要加“;”

           

　　  expdp [为用户名]/[密码]@[服务名] 
      schemas=[为用户名] 
      dumpfile=[导出数据库文件（可自命名）] 
      directory=[目录名] 
      logfile=[日志文件文件名（可自命名）] 

数据库还原前准备

1、创建表空间   

    create tablespace tbs_dbsunny datafile 
    'D:\app\Sunny\oradata\TableSpace\tbs_dbsunny.DBF' size 1G
    autoextend on next 100M maxsize unlimited logging
    extent management local autoallocate
    segment space management auto;

2、创建临时表空间

create temporary tablespace tbs_dnsunny_temp tempfile 'D:\app\Sunny\oradata\TableSpace\tbs_dnsunny_temp.DBF' size 1000M autoextend on next 100M maxsize unlimited ;

3、创建用户

create user sunny identified by sunny123 DEFAULT TABLESPACE  tbs_dbsunny  TEMPORARY TABLESPACE tbs_dnsunny_temp;

4、授权

alter user sunny temporary tablespace tbs_dnsunny_temp;

ALTER USER sunny QUOTA UNLIMITED ON TBS_DBSUNNY
grant connect to sunny; 
grant resource to sunny; 
grant dba to sunny;

grant create trigger to sunny;
grant create session to sunny;   
grant create sequence to sunny;
grant create synonym to sunny;
grant create table to sunny;
grant create view to sunny;
grant create procedure to sunny;
grant alter session to sunny;
grant execute on ctxsys.ctx_ddl to sunny;
grant create job to sunny;
grant sysdba to sunny;
alter user sunny default role all;

  -- 删除这个用户以及这个用户下的所有对象
  DROP USER sunny CASCADE;

数据库导入(impdp)

1、使用sys或system 登录

      通过"Window + R" 打开dos命令行界面，使用sys或system登录oracle。格式：sqlplus sys/密码@数据库实例名 as sysdba

sqlplus sys/12345@dborcl as sysdba

2、创建逻辑目录，并手动创建真实目录，并将备份文件DMP，放进此目录下

sqlplus  create or replace directory data_dir as 'E:\orcl\data';

3、给目标用户授权

sqlplus  grant read,write on directory data_dir to sunny;

4、导入：在dos命令行，执行

注意 : impdp 语句 后面 不要加 " ; "

impdp sunny/sunny123@DBSUNNY directory=data_dir dumpfile=EXPDPBUDGET.DMP logfile=impbudgett.log remap_schema =budgett:sunny remap_tablespace=PIMS:TBS_DBSUNNY

注：remap_schema=olduser:newuser               表示把左边的olduser用户的数据，导入到右边的newuser 用户里面

       remap_tablespace=old_tbs:new_tbs          表示把将要导入的备份库的表空间old_tbs，导入到新库替换为 new_tbs  

 

expdp导出数据

语法： expdp 用户名/密码@ip地址/实例 ....     ip地址不写默认就是本地

属性说明：
userid=test/test            --导出的用户，本地用户!!
directory=dmpfile          --导出的逻辑目录，一定要在oracle中创建完成的，并且给用户授权读写权限
dumpfile=xx.dmp      --导出的数据文件的名称，如果想在指定的位置的话可以写成dumpfile=/home/oracle/userxx.dmp
logfile=xx.log          --日志文件，如果不写这个参数的话默认名称就是export.log，可以在本地的文件夹中找到
schemas=userxx       --使用dblink导出的用户不是本地的用户，需要加上schema来确定导出的用户，类似于exp中的owner，但还有一定的区别
EXCLUDE=TABLE:"IN('T1','T2','T3')"     --exclude 可以指定不导出的东西，table，index等，后面加上不想导出的表名
network_link=db_local          --这个参数是使用的dblink来远程导出，需要指定dblink的名称

列出一些场景：

1)导出用户及其对象
expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp directory=dump_dir logfile=expdp.log;

2)导出指定表
expdp scott/tiger@orcl tables=emp,dept dumpfile=expdp.dmp directory=dump_dir logfile=expdp.log;

3)按查询条件导
expdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp tables=empquery='where deptno=20' logfile=expdp.log;

4)按表空间导
expdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmp tablespaces=temp,example logfile=expdp.log;

5)导整个数据库
expdp scott/123@127.0.0.1/orcl directory=dump_dir dumpfile=ly.dmp full=y  logfile=expdp.log;

一般用的都是导出整个数据库，本人使用的代码：

//包含所有用户的表、视图、索引等
expdp JCPT/123@127.0.0.1/orcl directory=mydata dumpfile=ly.dmp full=y  logfile=expdp.log;
//指定用户的表、视图、索引等
expdp JCPT/123@127.0.0.1/orcl directory=mydata schemas=jcpt dumpfile=ly.dmp logfile=expdp.log;

impdp 导入

列出一些场景：

1)导入用户（从用户scott导入到用户scott）
impdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp schemas=scott logfile=impdp.log;

2)导入表（从scott用户中把表dept和emp导入到system用户中）
impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmp tables=scott.dept,scott.emp remap_schema=scott:system logfile=impdp.log table_exists_action=replace (表空间已存在则替换);

3)导入表空间
impdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmp tablespaces=example logfile=impdp.log;

4)导入整个数据库
impdb system/manager@orcl directory=dump_dir dumpfile=full.dmp full=y logfile=impdp.log;

5)追加数据
impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmp schemas=systemtable_exists_action logfile=impdp.log;

日常使用的：

//把用户jcpt中所有的表导入到lyxt用户下
impdp lyxt/lyxt123@127.0.0.1/orcl directory=mydata dumpfile=LY.DMP   remap_schema=jcpt:lyxt logfile=ims20171122.log table_exists_action=replace