Oracle数据库导入(expdp)和导出(impdp)
文档最后,列出了常用的一些导入导出的场景,以及一些导入导出的属性说明。
一、数据库导出(expdp)
- 使用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 用户名/密码
属性说明:
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