一、expdp导出数据库
1、按用户导出
expdp scott/tiger@orcl DIRECTORY=oracle_dmp dumpfile=bak.dmp schemas=scott version=10.2.0.1.0;--如果10g导入11g,加上版本号
2.按数据库导出
expdp system/manager DIRECTORY=oracle_dmp DUMPFILE=full.dmp FULL=y;
3.按表名导出
expdp scott/tiger@orcl TABLES=emp,dept DIRECTORY=oracle_dmp dumpfile=bak.dmp ;
4.命令优化
?
二、impdp导入数据库
1、先创建一个用户
create temporary tablespace EAS_D_YXDB_STANDARD_TMEP
tempfile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\EAS_D_YXDB_STANDARD_TMEP.DBF'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local; create tablespace EAS_D_YXDB_STANDARD --EAS_D_EAS32_STANDARD
logging
datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\EAS_D_YXDB_STANDARD.DBF'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local; create user yangxiang identified by abc123
default tablespace EAS_D_YXDB_STANDARD
temporary tablespace EAS_D_YXDB_STANDARD_TMEP; grant connect,resource,dba to yangxiang;
2、执行导入命令
impdp yangxiang/abc123@orcl directory=oracle_dmp dumpfile=EAS3220150805.dmp remap_schema=EAS32:yangxiang remap_tablespace=(EAS_D_EAS32_INDEX:sx,EAS_D_EAS32_STANDARD:sx,EAS_D_YXDB_STANDARD:sx,USERS:sx) transform=oid:n
三、exp和imp的使用
EXP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y --完全导出
EXP SONIC/SONIC BUFFER=64000 FILE=C:\SONIC.DMP OWNER=SONIC --导出某个用户
EXP SONIC/SONIC BUFFER=64000 FILE=C:\SONIC.DMP OWNER=SONIC TABLES=(SONIC) --表导出 IMP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y --完全导入
IMP SONIC/SONIC BUFFER=64000 FILE=C:\SONIC.DMP FROMUSER=SONIC TOUSER=SONIC--用户之间导入
IMP SONIC/SONIC BUFFER=64000 FILE=C:\SONIC.DMP OWNER=SONIC TABLES=(SONIC) --表导入