Oracle数据库迁移、备份、导入、导出

1、创建表空间

create tablespace MYTS datafile 'D:\myts-data\mytsData.dbf' size 8192m autoextend on next 100m maxsize unlimited extent management local segment space management auto;

2、创建用户

create user MaYun identified by MaYun  default tablespace MYTS account unlock;

3、给用户授权限

grant connect,resource,dba to MaYun;

4、給用户绑定默认空间

alter user MaYun default tablespace MYTS;

5、创建目录　

create or replace directory data_pump_dir as 'D:\abc';
grant read,write on directory data_pump_dir to MaYun;
grant exp_full_database,imp_full_database to MaYun;
exit;

6、导入文件

imp userid=MaYun/MaYun@orcl file=D:\abc\123.dmp full=y;

　　或　　

impdp MaYun/MaYun directory=data_pump_dir dumpfile=123.dmp table_exists_action=append data_options=skip_constraint_errors nologfile=y;

7、导出文件　　

expdp MaYun/MaYun directory=data_dump_dir dumpfile=456.dmp logfile=MaYun.log compression=all schemas=MaYun include=table:"\like 'GT%'\";

　　或

exp MaYun/MaYun file=文件路径\456.dmp;