Fy_Recover_Data ———— 用于数据恢复的PLSQL包
来源:互联网 发布:淘宝卖家怎么贷款 编辑:程序博客网 时间:2024/06/08 17:07
http://www.hellodba.com/reader.php?ID=191&lang=CN
Fy_Recover_Data ———— 用于数据恢复的PLSQL包
[English]
作者: fuyuncat
来源: www.HelloDBA.com
日期: 2012-08-14 03:56:53
分享到
Fy_Recover_Data是利用Oracle表扫描机制、数据嫁接机制恢复TRUNCATE或者损坏数据的工具包。由纯PLSQL编写。
关于其实现原理和使用示例请参考:移花接木————利用Oracle表扫描机制恢复被Truncate的数据。
包中过程、函数描述如下:-- 更新记录:
-- 15/08/2012, Fuyuncat:
-- 1. Fixed Bug in Clean_Up_Ts (Not chage TS status correctly)
-- 2. Added Exception Handle when Restore Data
-- 3. Added Parameter in recover_table,
-- to balance Fault Tolerance and Performance
--
-- 16/08/2012, Fuyuncat:
-- 1. Enhanced corrupted block processing, get rows as possilbe
-- to balance Fault Tolerance and Performance
--
-- 17/08/2012, Fuyuncat:
-- 1. Ommit the LOB columns raised ORA-22922 exception
SQL代码
- create or replace package FY_Recover_Data is
- -------------------------------------------------------------------------
- -- WWW.HelloDBA.COM ---
- -- Created By: Fuyuncat ---
- -- Created Date: 08/08/2012 ---
- -- Email: Fuyuncat@gmail.com ---
- -- Coyprigh (c), 2012, WWW.HelloDBA.COM All rights reserved. ---
- -- Last Version: http://www.HelloDBA.com/download/FY_Recover_Data.zip ---
- -------------------------------------------------------------------------
- /************************************************************************
- ** Copy file
- **
- ** srcdir: Directory of Source File;
- ** srcfile: Source File Name;
- ** dstdir: Directory of Destination File;
- ** dstfile: Destination File Name;
- ************************************************************************/
- procedure copy_file(srcdir varchar2,
- srcfile varchar2,
- dstdir varchar2 default null,
- dstfile varchar2 default null);
- /************************************************************************
- ** Recover Table Data From Special Data File;
- **
- ** oriobjid: Object Id of Table to be Recovered;
- ** recowner: Owner of Table to be used as recovering dummy table;
- ** rectable: Name of Table to be used as recovering dummy table;
- ** rstowner: Owner of Table to store the recovered data;
- ** rsttable: Name of Table to store the recovered data;
- ** srcdir: Directory of the Data File to be recovered;
- ** srcfile: Name of the Data File to be recovered;
- ** recdir: Directory of Data File that rectable is stored;
- ** recfile: Name of Data File that rectable is stored;
- ** copydir: Directory of Copy of Data File that rectable is stored;
- ** coryfile: Name of Copy of Data File that rectable is stored;
- ** blksz: Block size of the Tablespace Storing the Table to be recovered;
- ************************************************************************/
- procedure recover_table(oriobjid number,
- recowner varchar2,
- rectab varchar2,
- rstowner varchar2,
- rsttab varchar2,
- srcdir varchar2,
- srcfile varchar2,
- recdir varchar2,
- recfile varchar2,
- copydir varchar2,
- copyfile varchar2,
- blksz number default 8192);
- /************************************************************************
- ** Recover Table Data From Data Files of Targe Table;
- **
- ** tgtowner: Owner of Target Table to be recovered;
- ** tgttable: Name of Target Table to be recovered;
- ** recowner: Owner of Table to be used as recovering dummy table;
- ** rectable: Name of Table to be used as recovering dummy table;
- ** rstowner: Owner of Table to store the recovered data;
- ** rsttable: Name of Table to store the recovered data;
- ** srcdir: Directory of the Data File to be recovered;
- ** srcfile: Name of the Data File to be recovered;
- ** recdir: Directory of Data File that rectable is stored;
- ** recfile: Name of Data File that rectable is stored;
- ** copydir: Directory of Copy of Data File that rectable is stored;
- ** coryfile: Name of Copy of Data File that rectable is stored;
- ** blksz: Block size of the Tablespace Storing the Table to be recovered;
- ************************************************************************/
- procedure recover_table(tgtowner varchar2,
- tgttable varchar2,
- recowner varchar2,
- rectab varchar2,
- rstowner varchar2,
- rsttab varchar2,
- srcdir varchar2,
- recdir varchar2,
- recfile varchar2,
- copydir varchar2,
- copyfile varchar2,
- blksz number default 8192);
- /************************************************************************
- ** Prepare the data files to be use during recovering;
- **
- ** tgtowner: Owner of Target Table to be recovered;
- ** tgttable: Name of Target Table to be recovered;
- ** datapath: Absolute path of Data Files;
- ** datadir: Directory to be created referring to datapath;
- ** rects: Tablespace to store the recovering dummy table;
- ** recfile: Name of Data File to store the recovering dummy table;
- ** rstts: Tablespace to store table storing the recovered data;
- ** rstfile: Name of Data File to store restoring table;
- ** blksz: Block size of the Tablespace Storing the Table to be recovered;
- ************************************************************************/
- procedure prepare_files(tgtowner varchar2,
- tgttable varchar2,
- datapath in varchar2,
- datadir in out varchar2,
- rects out varchar2,
- recfile out varchar2,
- rstts out varchar2,
- rstfile out varchar2,
- blksz out varchar2);
- /************************************************************************
- ** Clean up existing Recover and Restore Tablespace. Drop tables in the tablespaces
- **
- ** rects: Recover tablespace name
- ** rects: Restore tablespace name, default NULL, will not do cleaning up;
- ************************************************************************/
- procedure clean_up_ts(rects varchar2,
- rstts varchar2 default null);
- /************************************************************************
- ** Fill Blocks of Recovering Table, to format the blocks;
- **
- ** tgtowner: Owner of Target Table to be recovered;
- ** tgttable: Name of Target Table to be recovered;
- ** datadir: Directory to be created referring to datapath;
- ** rects: Tablespace to store the recovering dummy table;
- ** recfile: Name of Data File to store the recovering dummy table;
- ** rstts: Tablespace to store table storing the recovered data;
- ** blks: Number blocks in Initial Extent of the recovering dummy table;
- ** recowner: Owner of Table to be used as recovering dummy table;
- ** rstowner: Owner of Table to store the recovered data;
- ** rectable: Name of Table to be used as recovering dummy table;
- ** rsttable: Name of Table to store the recovered data;
- ** coryfile: Name of Copy of Data File that rectable is stored;
- ************************************************************************/
- procedure fill_blocks(tgtowner varchar2,
- tgttable varchar2,
- datadir varchar2,
- rects varchar2,
- recfile varchar2,
- rstts varchar2,
- blks number default 8,
- recowner varchar2 default user,
- rstowner varchar2 default user,
- rectab out varchar2,
- rsttab out varchar2,
- copyfile out varchar2);
- end FY_Recover_Data;
点击此处:下载源代码。
--- Fuyuncat ---
- Fy_Recover_Data ———— 用于数据恢复的PLSQL包
- mysql的一大坑——细说mysql 开启log_bin日志以用于恢复数据库
- 一个用于大规模数据科学的API——DataFrame
- 闭包——藏在代码中的“房间”,用于和外界沟通的桥梁
- plsql误操作表(增删改)的数据恢复
- 存储当前的数据信息,用于恢复---备忘录模式
- Linux数据恢复专题——恢复丢失的分区(转载)
- Linux数据恢复专题——恢复丢失的分区(转载)
- 复制plsql—develop表中的数据出现中文乱码的解决
- 数据安全的法宝——数据库恢复
- 恢复数据的“魔术师”——奇人李作萃
- 测试8——oracle数据恢复的研究
- 一些数据恢复软件——介绍
- 恢复plsql dev的试用
- CUDA——用于大量数据的超级计算:第一部分 (Rob Farber专栏)
- CUDA——用于大量数据的超级计算:第二部分 (Rob Farber专栏)
- CUDA——用于大量数据的超级计算:第三部分 (Rob Farber专栏)
- 【Map】——Map用于存储具有映射关系的数据
- UIColor使用——16进制数颜色转换成RGB颜色
- Android zip文件压缩解压缩
- Altium Designer 10 PCB简要设计及其例程
- Android-x86虚拟机安装配置全攻略
- Javascript 匿名函数
- Fy_Recover_Data ———— 用于数据恢复的PLSQL包
- JS实现兼容IE6、IE7、IE8的图片上传前预览效果
- HDU 2056 Rectangles (求两个相交矩形面积)
- 创建数据库
- OpenCV 编程简介(矩阵/图像/视频的基本读写操作)
- POJ 3537 Crosses and Crosses
- 利用xml在不同数据库中交换数据
- DBMS_SPACE查找使用和未使用的空间
- 向oracle中获取记录集的代码