【Oracle】expdp/impdp 数据泵导入导出

EXPDP数据导出

流程：

一、新建逻辑目录

第一步：在服务器上创建真实的目录：

创建真实服务器目录：‘真实服务器目录’
注意：第三步创建逻辑目录的命令不会在OS上创建真正的目录，所以要先在服务器上创建真实的目录。

第二步：用sys管理员登陆sqlplus：

SQLPLUS / SYS AS SYSDBA

第三步：创建逻辑目录：

CREATE DIRECTORY 逻辑目录名 AS '逻辑目录路径(同真实服务器路径)';

第四步：检查管理员目录，检查是否存在：

SQL>SELECT * FROM dba_directory/dba_directories;    #查看逻辑目录是否创建成功       

第五步：用sys管理员给指定用户赋予在该目录的操作权限

SQL>GRANT READ,WRITE ON DIRECTORY 逻辑目录名 TO 用户；

用EXPDP导出数据

格式：EXPDP 用户名/密码@ip地址/实例 【属性】——ip地址不写默认为本地
相关语句：

EXPDP 用户名/密码@ip/实例名 DIRECTORY=逻辑目录名 DUMPFILE=DMP文件.dmp FULL=y  LOGFILE=LOG文件.log;    #包含所有用户的表、视图、索引等

EXPDP 用户名/密码@ip/实例名 DIRECTORY=逻辑目录名 SCHEMAS=指定用户 DUMPFILE=DMP文件.dmp LOGFILE=LOG文件.log;            #指定用户的表、视图、索引等

1）导出用户及其对象
EXPDP scott/密码@orcl SCHEMAS=scott DUMPFILE=DMP文件.dmp DIRECTORY=逻辑目录名 LOGFILE=LOG文件.log;

2)导出指定表：
EXPDP scott/密码@orcl TABLES=用户,表1 DUMPFILE=DMP文件.dmp DIRECTORY=逻辑目录名 LOGFILE=LOG文件.log;

3)按查询条件导
EXPDP scott/密码@orcl DIRECTORY=逻辑目录名 DUMPFILE=DMP文件.dmp TABLES=表1 QUERY='where deptno=20' LOGFILE=LOG文件.log;

4)按表空间导
EXPDP system/密码@orcl DIRECTORY=逻辑目录名 DUMPFILE=DMP文件.dmp TABLESPACES=temp,example(表空间) LOGFILE=LOG文件.log;

5)导整个数据库
EXPDP scott/密码@ip/orcl DIRECTORY=逻辑目录名 DUMPFILE=DMP文件.dmp FULL=y  LOGFILE=LOG文件.log;

相关字段解释：

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的名称

IMPDP数据导入

在正式导入数据前，要先确保要导入的用户已存在，如果没有存在，请先用下述命令进行新建用户
流程：

一、创建表空间

使用system登陆Oracle，执行sql

查询表空间

在创建表空间之前需要查询当前数据库表空间路径：

SQL>SELECT name FROM v$datafile      #查询出当前数据库表空间，使用里面的路径

创建表空间

格式：create tablespace 表空间名 datafile ‘数据文件名’ size 表空间大小

CREATE TABLESPACE 【tablespacename】             #指出表空间的名称
DATAFILE '【/home/oracle/app/oradata/zfba.dbf】' 
SIZE【100M】 reuse                               #设置表空间大小；如果存在相关的数据文件时可省略
AUTOEXTEND ON NEXT【40M】                        #设置表空间自动扩展，每次1280K
MAXSIZE UNLIMITED                               #设置最大大小为无限制

ALTER DATABASE DATAFILE 'D:\oracle\oradata\orcl\表空间名称.dbf' AUTOEXTEND ON  MAXSIZE UNLIMITED;      #修改表空间未自动扩展
ALTER DATABASE DATAFILE 'D:\oracle\oradata\orcl\表空间名称.dbf' RESIZE 1024M;    #修改表空间大小
DROP TABLESPACE 表空间名称 INCLUDING CONTENTS AND DATAFILES;     #删除表空间
DROP USER 用户名 CASCADE;        #执行该语句请小心，会级联删除该用户下所有对象。

注：其中大括号表示为需要填写的相关参数；
reuse表示：

1. If the file already exists, then Oracle reuses the filename and applies the new size (if you specify SIZE) or retains the original size.
--如果file 已经存在，并且在创建时指定了file size，那么就重用原文件，并应用新的size，如果没有指定file size，则保留原有的大小。
2. If the file does not exist, then Oracle ignores this clause and creates the file.
-- 如果file 不存在，oracle 将忽略该参数。

size 【100M】 reuse：
省略Size参数的条件是目标数据文件已存在。举个例子：

CREATE TABLESPACE test DATAFILE 'D:\datafiles\test01.dbf' size 10M;    #创建一个表空间
DROP TABLESPACE test;       #删除这个表空间
##这里删除表空间，没有加including contents and datafiles，保留了数据文件
CREATE TABLESPACE new_test DATAFILE 'D:\datafiles\test01.dbf';     #创建一个新的表空间
这时候不用size参数也能创建，因为已经存在数据文件。
PS: 删除表空间后留下的数据文件，如果要重新使用是可以像例子中那样省略size 参数的，如果添加了size 参数反而会报错，如果一定要指定新的 size，则需要用reuse；

二、创建用户并授权

创建用户并指定表空间

格式：create user 用户名 IDENTIFIED BY 密码 DEFAULT TABLESPACE 表空间;

CREATE user  username IDENTIFIED BY password DEFAULT TABLESPACE tablespacename   #创建用户，密码，指定表空间

CREATE user 用户名 IDENTIFIED BY 密码          #创建用户
ALTER user 用户名 IDENTIFIED BY 新密码         #修改用户密码

给用户赋予权限

格式：GRANT DBA TO 用户名;
格式：GRANT CREATE SESSION TO 用户名;
格式：GRANT READ,WRITE ON DIRECTORY 逻辑目录名 TO 用户名;
相关语法：

GRANT UNLIMITED TABLESPACE TO 用户名；    #给用户赋予表空间操作的权限
GRANT CONNECT TO 用户名；                 #connect是赋予连接数据库的权限
GRANT DBA TO 用户名；                     #给用户赋予所有权限
GRANT RESOURCE TO 用户名；                #resource 是赋予用户只可以创建实体但是没有创建数据结构的权限。
GRANT CREATE SESSION TO 用户名  　　      #这个是给用户赋予登录的权限。
GRANT CREATE TABLE TO 用户名     　　     #给用户赋予表操作的权限
GRANT UNLIMITED TABLESPACE TO 用户名     #给用户赋予表空间操作的权限
GRANT SELECT ANY TABLE TO 用户名         #给该用户赋予访问任务表的权限   同理可以赋予update 和delete 的

GRANT SELECT ON srapp_hz_zhpt_yl.jggl TO srapp_hz_zhpt_ylcs    #这里是给srapp_hz_zhpt_ylcs用户赋予select srapp_hz_zhpt_yl用户的jggl表的查询的权限。同理可以有alter，drop，insert等权限。   
#注意 这个语句在没有访问另一个用户的权限情况下这个语句要在另一个用户登录情况下执行，这样才能生效。

-------------撤销权限
基本语法同grant,关键字为revoke 如：
REVOKE CREATE TABLE TO 用户名       #取消这个用户的对表操作的权限。

-----------查看权限
SELECT * FROM user_sys_privs;           #查看当前用户所有权限
SELECT * FROM user_tab_privs;           #查看所用用户对表的权限

-----------角色

角色即权限的集合，可以把一个角色授予给用户，管理角色的权限比较简单，可以在一个用户下创建多个角色，用户只需要添加角色就可以管理权限了，便于用户管理权限。

CREATE ROLE myrole;                     #创建角色
GRANT CREATE SESSION TO myrole;         #将创建session的权限授予myrole
GRANT MYROLE TO zhangsan;               #授予zhangsan用户myrole的角色
DROP ROLE myrole;                       #删除角色

三、IMPDP导入

命令在cmd中或者使用Oracle用户登陆Linux系统的控制台中输入，不是SQL语句
格式：IMPDP 用户名/密码@ip地址:实例 [属性];——ip地址不写默认为本地
把用户a中所有的表导入到b用户下

IMPDP b/用户b密码@127.0.0.1/orcl DIRECTORY=mydata DUMPFILE=DMP文件.dmp   REMAP_SCHEMA=a:b LOGFILE=LOG文件.log TABLE_EXISTS_ACTION=REPLACE;

1）导入用户(从用户scott导入到用户scott)
IMPDP scott/密码@实例名 directory=逻辑目录名称 dumpfile=DMP文件.dmp SCHEMAS=scott LOGFILE=LOG文件.log;

2）导入表（从scott用户中把表dept和emp导入到system用户中）
IMPDP system/密码@实例名 DIRECTORY=逻辑目录名 DUMPFILE=DMP文件.dmp TABLES=scott.dept,scott.emp REMAP_SCHEMA=scott:system LOGFILE=impdp.log TABLE_EXISTS_ACTION=REPLACE (表空间已存在则替换);

3）导入表空间
IMPDP system/密码@实例名 DIRECTORY =逻辑目录名 DUMOFILE=DMP文件.dmp TABLESPACES=表空间名 LOGFILE=LOG文件.log;

4)导入整个数据库
IMPDP system/密码@实例名 DIRECTORY=逻辑目录名 DUMPFILE=DMP文件.dmp FULL=y LOGFILE=LOG文件.log;