<一>、批处理部分：
01_导出垃圾附件.bat
@echo ********************************************************************************************************** @echo 导出垃圾附件(须知) @echo 1.该批处理是备份垃圾附件，先备份，然后才执行清理。 @echo 2.注意 @echo 输入盘符参数p_path的值应与批处理脚本的盘符一样,如输入E,批处理脚本的盘符也应在E盘,如e:atm_script @echo 盘符:atm_scriptack (存放导出垃圾附件的脚本) @echo 盘符:atm_scriptdata (存放导出的垃圾附件及所产生的日志文件) @echo 盘符:atm_scriptdelete (存放清理的垃圾附件的脚本) @echo 盘符:atm_script 1_导出垃圾附件.bat (备份垃圾附件的批处理文件) @echo 盘符:atm_script 2_清理垃圾附件数据.bat (清理垃圾附件的批处理文件) @echo 盘符:atm_script 3_恢复垃圾附件.bat (恢复垃圾附件的批处理文件,需要时,才执行此批处理) @echo 3.执行批处理文件时,需要输入数据库用户密码(如用启密码@SID)，才能连接上数据库 @echo 数据库连接串举例:atm01/HondaHsu2011@dw @echo ********************************************************************************************************** @echo off set /p p_user="请输入数据库用户名:" set /p p_password="请输入数据库密码:" set /p p_sid="请输入数据库连接串(TNS名):" set /p p_path="请输入脚本所在的盘符(如e:atm_script,则输入e):" exp %p_user%/%p_password%@%p_sid% parfile=%p_path%:atm_scriptackgd01.txt file=%p_path%:atm_scriptdata op_attachment_comtent_gd0930.dmp log=%p_path%:atm_scriptdataexp_top_att_comtent.log exp %p_user%/%p_password%@%p_sid% parfile=%p_path%:atm_scriptackgd02.txt file=%p_path%:atm_scriptdata op_attachment_extend_gd0930.dmp log=%p_path%:atm_scriptdataexp_top_att_extend.log exp %p_user%/%p_password%@%p_sid% parfile=%p_path%:atm_scriptackgd03.txt file=%p_path%:atm_scriptdata op_attachment_obj_rel_gd0930.dmp log=%p_path%:atm_scriptdataexp_top_att_ojb_rel.log exp %p_user%/%p_password%@%p_sid% parfile=%p_path%:atm_scriptackgd04.txt file=%p_path%:atm_scriptdata op_attachment_pro_rel_gd0930.dmp log=%p_path%:atm_scriptdataexp_top_att_pro_rel.log pause
02_清理垃圾附件数据.bat
@echo ********************************************************************************************************** @echo 清理垃圾附件(须知) @echo 1.必须先执行"01_导出垃圾附件.bat"后，才能执行该清理垃圾附件的批处理 @echo 2.注意 @echo 输入盘符参数p_path的值应与批处理脚本的盘符一样,如输入E,批处理脚本的盘符也应在E盘,如e:atm_script @echo 盘符:atm_scriptack (存放导出垃圾附件的脚本) @echo 盘符:atm_scriptdata (存放导出的垃圾附件及所产生的日志文件) @echo 盘符:atm_scriptdelete (存放清理的垃圾附件的脚本) @echo 盘符:atm_script 1_导出垃圾附件.bat (备份垃圾附件的批处理文件) @echo 盘符:atm_script 2_清理垃圾附件数据.bat (清理垃圾附件的批处理文件) @echo 盘符:atm_script 3_恢复垃圾附件.bat (恢复垃圾附件的批处理文件,需要时,才执行此批处理) @echo 3.执行批处理文件时,需要输入数据库用户密码(如用启密码@SID)，才能连接上数据库 @echo 数据库连接串举例:atm01/HondaHsu2011@dw @echo ********************************************************************************************************** @echo off set /p p_user="请输入数据库用户名:" set /p p_password="请输入数据库密码:" set /p p_sid="请输入数据库连接串(TNS名):" set /p p_path="请输入脚本所在的盘符(如e:atm_script,则输入e):" if exist %p_path%:atm_scriptdata op_attachment_comtent_gd0930.dmp ( sqlplus %p_user%/%p_password%@%p_sid% @"%p_path%:atm_scriptdeletedel_top_attachment_data.sql" > "%p_path%:atm_scriptdatadelete_attachment_data.log" echo **************************************************************************** echo 已经完成清理,可查看日志 %p_path%:atm_scriptdatadelete_attachment_data.log ) else ( echo **************************************************************************** echo 没有找到备份的垃圾附件数据，请先备份垃圾附件，然后再清理垃圾附件 ) pause>nul
03_恢复垃圾附件.bat
@echo ********************************************************************************************************** @echo 恢复垃圾附件(须知) @echo 1.在需要恢复被清理掉的附件时，使用该批处理 @echo 2.注意 @echo 输入盘符参数p_path的值应与批处理脚本的盘符一样,如输入E,批处理脚本的盘符也应在E盘,如e:atm_script @echo 盘符:atm_scriptack (存放导出垃圾附件的脚本) @echo 盘符:atm_scriptdata (存放导出的垃圾附件) @echo 盘符:atm_scriptdelete (存放清理的垃圾附件的脚本及所产生的日志文件) @echo 盘符:atm_script 1_导出垃圾附件.bat (备份垃圾附件的批处理文件) @echo 盘符:atm_script 2_清理垃圾附件数据.bat (清理垃圾附件的批处理文件) @echo 盘符:atm_script 3_恢复垃圾附件.bat (恢复垃圾附件的批处理文件,需要时,才执行此批处理) @echo 3.执行批处理文件时,需要输入数据库用户密码(如用启密码@SID)，才能连接上数据库 @echo 数据库连接串举例:atm01/HondaHsu2011@dw @echo ********************************************************************************************************** @echo off set /p p_user="请输入数据库用户名:" set /p p_password="请输入数据库密码:" set /p p_sid="请输入数据库连接串(TNS名):" set /p p_path="请输入脚本所在的盘符(如e:atm_script,则输入e):" if exist %p_path%:atm_scriptdata op_attachment_comtent_gd0930.dmp ( imp %p_user%/%p_password%@%p_sid% file=%p_path%:atm_scriptdata op_attachment_comtent_gd0930.dmp log=%p_path%:atm_scriptdataimp_top_att_comtent.log grants=n rows=y ignore=y full=y ) else ( echo **************************************************************************** echo 没有找到备份的垃圾附件数据: %p_path%:atm_scriptdata op_attachment_comtent_gd0930.dmp ) if exist %p_path%:atm_scriptdata op_attachment_extend_gd0930.dmp ( imp %p_user%/%p_password%@%p_sid% file=%p_path%:atm_scriptdata op_attachment_extend_gd0930.dmp log=%p_path%:atm_scriptdataimp_top_att_extend.log grants=n rows=y ignore=y full=y ) else ( echo **************************************************************************** echo 没有找到备份的垃圾附件数据: %p_path%:atm_scriptdata op_attachment_extend_gd0930.dmp ) if exist %p_path%:atm_scriptdata op_attachment_obj_rel_gd0930.dmp ( imp %p_user%/%p_password%@%p_sid% file=%p_path%:atm_scriptdata op_attachment_obj_rel_gd0930.dmp log=%p_path%:atm_scriptdataimp_top_att_obj_rel.log grants=n rows=y ignore=y full=y ) else ( echo **************************************************************************** echo 没有找到备份的垃圾附件数据: %p_path%:atm_scriptdata op_attachment_obj_rel_gd0930.dmp ) if exist %p_path%:atm_scriptdata op_attachment_pro_rel_gd0930.dmp ( imp %p_user%/%p_password%@%p_sid% file=%p_path%:atm_scriptdata op_attachment_pro_rel_gd0930.dmp log=%p_path%:atm_scriptdataimp_top_att_pro_rel.log grants=n rows=y ignore=y full=y echo **************************************************************************** echo 已经完成导入,可查看日志 %p_path%:atm_scriptdataimp_top_att_********.log ) else ( echo **************************************************************************** echo 没有找到备份的垃圾附件数据: %p_path%:atm_scriptdata op_attachment_pro_rel_gd0930.dmp ) pause
<二>、SQL脚本组装
- 备份脚本:
atm_scriptackgd01.txt #导出附件内容表中垃圾数据(不存在于附件信息表中的附件) tables=(top_attachment_content) feedback=1000 buffer=102400000 TRIGGERS=N query="where attachment_id in (select a.attachment_id from top_attachment_content a where not exists (select 1 from top_attachment b where a.attachment_id=b.attachment_id))" atm_scriptackgd02.txt #导出垃圾附件的扩展属性条件数据 tables=(top_attachment_extend) TRIGGERS=N query=" where attachment_id in (select a.attachment_id from top_attachment_content a where not exists (select 1 from top_attachment b where a.attachment_id=b.attachment_id))" atm_scriptackgd03.txt #导出垃圾附件的关联对象条件 tables=(top_attachment_object_relation) TRIGGERS=N query="where attachment_id in (select a.attachment_id from top_attachment_content a where not exists (select 1 from top_attachment b where a.attachment_id=b.attachment_id))" atm_scriptackgd04.txt #导出垃圾附件的关联属性条件 tables=(top_attachment_pro_relation) TRIGGERS=N query="where attachment_id in (select a.attachment_id from top_attachment_content a where not exists (select 1 from top_attachment b where a.attachment_id=b.attachment_id))"
- 清理脚本:
atm_scriptdeletedel_top_attachment_data.sql delete from top_attachment_extend nologging where attachment_id in (select a.attachment_id from top_attachment_content a where not exists (select 1 from top_attachment b where a.attachment_id=b.attachment_id)); delete from top_attachment_object_relation nologging where attachment_id in (select a.attachment_id from top_attachment_content a where not exists (select 1 from top_attachment b where a.attachment_id=b.attachment_id)); delete from top_attachment_pro_relation nologging where attachment_id in (select a.attachment_id from top_attachment_content a where not exists (select 1 from top_attachment b where a.attachment_id=b.attachment_id)); delete from top_attachment_content nologging where attachment_id in (select a.attachment_id from top_attachment_content a where not exists (select 1 from top_attachment b where a.attachment_id=b.attachment_id) ); commit; exit;