达梦dexp和dimp

 

-----------------------------------------------------------------导出-----------------------------------------------------------------------------------------------------

全库导出
[dmdba@host135 tmp]$ /dmdbms/product/bin/dexp userid=sysdba/dameng123 directory=/tmp/ file=dexpfull.dmp log=dexpfull.log full=y

用户级导出
[dmdba@host135 tmp]$ /dmdbms/product/bin/dexp userid=sysdba/dameng123 directory=/tmp/ file=hxl.dmp log=hxl.log owner=hxl

模式级导出
[dmdba@host135 tmp]$ /dmdbms/product/bin/dexp userid=sysdba/dameng123 directory=/tmp/ file=hxl_schema.dmp log=hxl_schema.log schemas=hxl

表级导出
[dmdba@host135 tmp]$ /dmdbms/product/bin/dexp userid=sysdba/dameng123 directory=/tmp/ file=tb_test01.dmp log=tb_test01.log tables=hxl.tb_test01

 带上查询条件语句

/dmdbms/product/bin/dexp userid=sysdba/dameng123 directory=/tmp/ file=where_tb_test01.dmp log=where_tb_test01.log tables=hxl.tb_test01 query="where id=1"

 

 createtime为datetime类型

 

[dmdba@host135 tmp]$ /dmdbms/product/bin/dexp userid=sysdba/dameng123 directory=/tmp/ file=where_tb_test02.dmp log=where_tb_test02.log tables=hxl.tb_test02 query="where to_char(createtime,'YYYY-MM-DD')='2022-07-15'"
[dmdba@host135 tmp]$ /dmdbms/product/bin/dexp userid=sysdba/dameng123 directory=/tmp/ file=where_tb_test02.dmp log=where_tb_test02.log tables=hxl.tb_test02 query="where createtime>='2022-07-15' and createtime<'2022-07-16'"

 

 [dmdba@host135 tmp]$/dmdbms/product/bin/dexp userid=sysdba/dameng123 directory=/tmp/ file=where_tb_test02.dmp log=where_tb_test02.log tables=hxl.tb_test02 query="where createtime>=to_date('2022-07-15','YYYY-MM-DD') and createtime<to_date('2022-07-16','YYYY-MM-DD')"

 

 压缩导出：

[dmdba@host135 tmp]$ /dmdbms/product/bin/dexp userid=sysdba/dameng123 directory=/tmp/ file=dexpfull.dmp log=dexpfull.log full=y compress=y

 

------------------------------------------------导入----------------------------------------------------

 

表还原:

还原表:
/dmdbms/product/bin/dimp userid=sysdba/dameng123 file=/tmp/tb_test01.dmp log=imp_tb_test01.log tables=hxl.tb_test01 rows=y
或是
/dmdbms/product/bin/dimp userid=sysdba/dameng123 directory=/tmp/ file=tb_test01.dmp log=imp_tb_test01.log tables=hxl.tb_test01 rows=y

rows=y 表示导入结构和数据 rows=n表示只导入结构

 

/dmdbms/product/bin/dimp userid=sysdba/dameng123 directory=/tmp/ file=tb_test01.dmp log=imp_tb_test01.log tables=hxl.tb_test01 rows=y ignore=y 

ignore=y 若对象已经存在的话 该选项会或略建表错误，若表已经有数据的话，会直接追加到表里面

 

导出导入例子1:

 

使用普通用户导出导入,hxl用户具有如下角色,新库的用户需要提前创建好
grant RESOURCE to hxl;
grant PUBLIC to hxl;
grant VTI to hxl;
grant SOI to hxl;

导出
dexp userid=hxl/dameng123 directory=/tmp/ file=yeemiao.dmp log=yeemiao.log owner=hxl

导入
删除用户下的所有对象
select 'drop table '||table_name||';' from user_tables;
把语句拷贝出来执行
dimp userid=hxl/dameng123 file=/tmp/hxl.dmp log=imp_hxl.log owner=hxl

 

2.导出备份例子

[dmdba@localhost logs]$ crontab -l
##dexp备份hxl用户
10 15 * * * /home/dmdba/script/dexp.sh
[dmdba@localhost logs]$ more /home/dmdba/script/dexp.sh
#! /bin/bash

. /etc/profile
. ~/.bash_profile

backup_date=`date "+%Y%m%d"`
delete_date=`date +%Y%m%d -d "7 days ago"` 

dblist=(HXL)
for db_name in "${dblist[@]}"
do

dumpfilename=${db_name}_BAK_${backup_date}.dmp

dexp userid=sysdba/dameng123 directory=/home/dmdba/dumpbakcup/ file=${dumpfilename} log=/home/dmdba/script/logs/EXP_${db_name}_${backup_date}.log
 owner=${db_name} compress=y

##删除本地文件
delete_file=/home/dmdba/dumpbakcup/${db_name}_BAK_${delete_date}.dmp

if [ -f "${delete_file}" ];then
  rm ${delete_file}
fi

done