1.导出
source /home/oracle/.bash_profile //先设置下参数
expdp SX_SZD_MIS_BASE/xxxxx directory=DATA_PUMP_DIR dumpfile=SX_SZD_MIS_BASE_2018_04_23.DMP schemas=SX_SZD_MIS_BASE
//导出数据库
expdp QH_MIS_BASE/xxxxx schemas=QH_MIS_BASE dumpfile=QH_MIS_BASE_20191010.DMP logfile=expdp.log directory=DATA_PUMP_DIR
select * from dba_directories; //可以查看 directory 对应的地址(得有dba权限的人才能查看,比如system)
unzip xxx.zip
2.导入
impdp SX_SZD_MIS_BASE/love431 schemas=SX_SZD_MIS_BASE dumpfile =SX_SZD_MIS_BASE_20180116.DMP logfile=expdp.log directory=DATA_PUMP_DIR
有时文件会提醒无权限,需要:
chmod 777 SX_SZD_MIS_BASE_20180116.DMP 赋予权限
当然,用system权限高一点
impdp system/love431 schemas=EDU_INFO_BASE dumpfile =EDU_INFO_BASE_2018_06_07.DMP logfile=expdp.log directory=DATA_PUMP_DIR
也可以给某个用户赋予dba权限:
grant dba to SX_SZD_MIS_BASE; 赋予权限
revoke dba from SX_SZD_MIS_BASE; 删除权限
其他:创建表空间:
create tablespace MY_PROJECT
nologging
datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\MY_PROJECT.ora' size 1024M
autoextend on next 10M maxsize unlimited
EXTENT MANAGEMENT local autoallocate
segment space management auto;
create user MY_PROJECT
identified by love431 default tablespace MY_PROJECT
account unlock;
grant connect,resource to MY_PROJECT;
grant read,write on directory DATA_PUMP_DIR to MY_PROJECT。
把导出写成sh脚本
#!/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=orcl
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0
export PATH=$PATH:$ORACLE_HOME/bin
export NLS_LANG=American_America.ZHS16GBK
export BAKUPTIME=`date +%Y%m%d%H%M%S`
export DATA_DIR=/home/oracle/backup/datas/
export TAR_DIR=/home/oracle/backup/tars/
cd $DATA_DIR
echo "Starting bakup..."
echo "Bakup file path $DATA_DIR/hlj_mis_base_$BAKUPTIME.DMP"
expdp hlj_mis_base/123456@orcl directory=MY_BACKUP_PATH dumpfile=hlj_mis_base_$BAKUPTIME.DMP schemas=hlj_mis_base
echo "Starting tar..."
echo "Tar file path $TAR_DIR/SCOTT_$BAKUPTIME.tar.gz"
zip $TAR_DIR/hlj_mis_base_$BAKUPTIME.zip $DATA_DIR/hlj_mis_base_$BAKUPTIME.DMP
echo "Bakup completed."
其中几个注意事项:
1.参数可以去/home/oracle/.bash_profile查看配置
2.脚本从win复制到linux后需要赋权限777,并且:set ff=unix设置编码后:wq保存
3.可能文件夹也需要赋予下权限