ORACLE恢复truncate的表的方法

这两天都在研究怎么恢复被清空过的表，恢复的方法很多，可用的工具也很多，在这里分享一下个人认为比较方便的一种方法：

首先，我们分析一下truncate的过程。truncate不会逐个清除用户数据块上的数据，而仅仅重置数据字典和元数据块上的元数据(如存储段头和扩展段图)。也就是说，此时，其基本数据并未被破坏，而是被回收、等待被重新分配————因此，要恢复被truncate的数据，需要及时备份其所在的数据文件。

然后，再分析一下表扫描的过程：oracle会读取段头的元数据，获得高水位线等信息，然后读取高水位线以下被格式化的数据块。因此，理论上讲，如果能够将被重置的元数据和元数据块重新构造出来，就能使数据能被重新读取。然而，要完成这个任务，难度相当大————要找出原有的所有元数据块被保证其每个字节与被truancate之前完全相同————看起来似乎是一个不可能完成的任务。

不过，我们可以换一角度来找方法————如果我们已经有一套元数据及数据块，然后将被truncate的用户数据块的内容取代其用户数据块的内容，是否可以“骗”过oracle，让它读出这些数据呢?

回顾一下表扫描的过程，这个方法应该是可行的。我们只要想办法构造出一个结构相同、且具有完整元数据信息和格式化了的用户数据块的傀儡表对象，然后将被truncate的用户数据块找出，再将其数据内容部分嫁接到傀儡对象的用户数据块，使oracle以外这是傀儡对象的数据，就能让oracle扫描并读出数据内容。

恢复方法：新建两个表空间，分别存放a表和b表，表结构与源表一致。a表用于构造元数据，b表用于存放恢复数据。读取a表的元数据与源表的数据块，存放到新的数据文件。

调用语句：

declare

tgtowner varchar2(30);

tgttable varchar2(30);

datapath varchar2(4000);

datadir varchar2(30);

rects varchar2(30);

recfile varchar2(30);

rstts varchar2(30);

rstfile varchar2(30);

blksz number;

rectab varchar2(30);

rsttab varchar2(30);

copyfile varchar2(30);

begin

tgtowner := 'lan'; --用户

tgttable := 'test1'; --要恢复的表名

datapath := '/u01/app/oracle/oradata/db1/'; --数据文件所在的目录

datadir := 'fy_data_dir'; ---可保持不变

pg_lan_recover_tab_data.prepare_files(tgtowner, tgttable, datapath, datadir, rects, recfile, rstts, rstfile, blksz);

pg_lan_recover_tab_data.fill_blocks(tgtowner, tgttable, datadir, rects, recfile, rstts, 8, tgtowner, tgtowner, rectab, rsttab, copyfile);

pg_lan_recover_tab_data.recover_table(tgtowner, tgttable, tgtowner, rectab, tgtowner, rsttab, datadir, datadir, recfile,datadir, copyfile, blksz);

end;

说明：如果存储用的是asm，需将数据文件复制到主机。 此代码可以恢复非分区表，可恢复普通压缩和非压缩表，如需恢复分区表与混合列压缩表，请与本人联系。

程序 代码：(需dba与select any dictionary权限)

create or replace package pg_lan_recover_tab_data authid current_user is

---------------------------------------------------------------------------

/* 需要权限：1、dba角色;2、select and dictionary

*/

---------------------------------------------------------------------------

type r_cursor is ref cursor;

type o_fileprop is record (

file# number,

status$ number);

type t_fileprops is table of o_fileprop;

/************************************************************************

** 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;

** selflink: database link refer to instance self connect to dba account;

************************************************************************/

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,

fillblks number default 5,

selflink varchar2 default '',

endianess number default 1);

/************************************************************************

** 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;

** selflink: database link refer to instance self connect to dba account;

************************************************************************/

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,

fillblks number default 5,

selflink varchar2 default '');

/************************************************************************

** 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;

** rectsblks: block number of recovery tablespace

** rectsblks: block number of restore tablespace

************************************************************************/

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,

rectsblks number default 16,

rsttsblks number default 2560);

/************************************************************************

** 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 in out varchar2,

rsttab in out varchar2,

copyfile out varchar2);

/************************************************************************

** testing recovering procedure, involved initializing tablespaces and data files

**

** tgtowner: owner of target table to be recovered;

** tgttable: name of target table to be recovered;

** datapath: absolute path of data files;

** fbks: block number to be filled in recovery table;

************************************************************************/

procedure test_rec1( tow varchar2 default 'sys',

ttb varchar2 default 't_chain',

fbks number default 1,

datapath varchar2 default 'd:\oracle\product\10.2.0\oradata\edgar\datafile\');

/************************************************************************

** testing recovering procedure, without initializing tablespaces and data files

**

** tgtowner: owner of target table to be recovered;

** tgttable: name of target table to be recovered;

** fbks: block number to be filled in recovery table;

************************************************************************/

procedure test_rec2( tow varchar2 default 'sys',

ttb varchar2 default 't_chain',

fbks number default 1);

/************************************************************************

** set initial parameters

**

** tracing: trace the process for debug;

** logging: show logging information;

** repobjid: replace the data object id wiht the recover table data object id;

************************************************************************/

procedure init_set( tracing boolean default true,

logging boolean default true,

repobjid boolean default true);

end pg_lan_recover_tab_data;

/

create or replace package body pg_lan_recover_tab_data is

---------------------------------------------------------------------------

---------------------------------------------------------------------------

s_tracing boolean:= true;

s_logging boolean:= true;

s_repobjid boolean:= false;

procedure init_set (tracing boolean default true,

logging boolean default true,

repobjid boolean default true)

as

begin

s_tracing := tracing;

s_logging := logging;

s_repobjid := repobjid;

end;

procedure trace (msg varchar2)

as

begin

if s_tracing then

dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss')||': '||msg);

end if;

end;

procedure log (msg varchar2)

as

begin

if s_logging then

dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss')||': '||msg);

end if;

end;

function d2r (dig varchar2,

len number default 0)

return raw

is

begin

--trace('[d2r] hextoraw(lpad(trim(to_char('||dig||', ''xxxxxxxx'')),'||len||',''0''))');

return hextoraw(lpad(trim(to_char(dig, 'xxxxxxxx')),len,'0'));

end;

procedure copy_file(srcdir varchar2,

srcfile varchar2,

dstdir varchar2 default null,

dstfile varchar2 default null)

as

bfr utl_file.file_type;

bfw utl_file.file_type;

-- vrw raw(8192);

frw raw(8192);

bsz number := 8192;

--p_srcdir varchar2(255) := upper(srcdir);

--p_srcfile varchar2(255) := upper(srcfile);

--p_dstdir varchar2(255) := upper(dstdir);

--p_dstfile varchar2(255) := upper(dstfile);

p_srcdir varchar2(255) := srcdir;

p_srcfile varchar2(255) := srcfile;

p_dstdir varchar2(255) := dstdir;

p_dstfile varchar2(255) := dstfile;

begin

if dstdir is null then

p_dstdir := p_srcdir;

end if;

if p_dstfile is null then

p_dstfile := p_srcfile||'$';

end if;

trace('[copy_file] begin copy file: '||srcdir||'\'||srcfile||' => '||dstdir||'\'||dstfile);

bfr := utl_file.fopen(p_srcdir, p_srcfile, 'rb');

bfw := utl_file.fopen(p_dstdir, p_dstfile, 'wb');

while true loop

begin

utl_file.get_raw(bfr, frw, bsz);

exit when frw is null;

utl_file.put_raw(bfw, frw);

utl_file.fflush(bfw);

exception when others then

exit;

end;

end loop;

utl_file.fclose(bfw);

utl_file.fclose(bfr);

trace('[copy_file] completed.');

end;

procedure replace_segmeta_in_file(srcdir varchar2,

srcfile varchar2,

dstdir varchar2,

dstfile varchar2,

tgtobjid number,

newobjid number,

dtail raw,

addpos number,

addinfo raw,

blksz number default 8192,

endianess number default 1)

as

bfr utl_file.file_type;

bfw utl_file.file_type;

hsz number := 24;

objr raw(4);

objn number;

dhead raw(32);

dbody raw(32767);

nbody raw(32767);

p_srcdir varchar2(255) := srcdir;

p_srcfile varchar2(255) := srcfile;

p_dstdir varchar2(255) := dstdir;

p_dstfile varchar2(255) := dstfile;

begin

if p_dstdir is null then

p_dstdir := p_srcdir;

end if;

trace('[replace_objid_in_file] replace object id in '||srcdir||'\'||srcfile||' ['||tgtobjid||' => '||newobjid||']');

bfr := utl_file.fopen(p_srcdir, p_srcfile, 'rb');

bfw := utl_file.fopen(p_dstdir, p_dstfile, 'wb');

while true loop

begin

nbody := '';

utl_file.get_raw(bfr, dhead, hsz);

exit when dhead is null;

utl_file.get_raw(bfr, dbody, blksz-hsz);

--objr := hextoraw(substrb(rawtohex(dbody), 1, 8));

objr := utl_raw.substr(dbody, 1, 4);

if endianess > 0 then

objn := to_number(rawtohex(utl_raw.reverse(objr)), 'xxxxxxxx');

else

objn := to_number(rawtohex(objr), 'xxxxxxxx');

end if;

-- replace data object id with the recover object id

--if objn = tgtobjid and substrb(rawtohex(dhead), 1, 2) = '06' then

if objn = tgtobjid then

if addpos <= hsz then

--utl_file.put_raw(bfw, utl_raw.concat(utl_raw.substr(dhead, 1, addpos-1), addinfo, utl_raw.substr(dhead, addpos+utl_raw.length(addinfo))));

nbody := utl_raw.concat(nbody, utl_raw.substr(dhead, 1, addpos-1), addinfo, utl_raw.substr(dhead, addpos+utl_raw.length(addinfo)));

else

--utl_file.put_raw(bfw, dhead);

nbody := utl_raw.concat(nbody, dhead);

end if;

--utl_file.put_raw(bfw, utl_raw.concat(utl_raw.substr(dhead, 1, 8), addinfo, utl_raw.substr(dhead, 9+utl_raw.length(addinfo))));

--nbody := utl_raw.concat(nbody, utl_raw.substr(dhead, 1, 8), addinfo, utl_raw.substr(dhead, 9+utl_raw.length(addinfo)));

--trace('[replace_objid_in_file] old id in raw: '||rawtohex(objr));

if endianess > 0 then

--trace('[replace_objid_in_file] new id in raw: '||utl_raw.reverse(d2r(newobjid, 8)));

--utl_file.put_raw(bfw, utl_raw.reverse(d2r(newobjid, 8)));

nbody := utl_raw.concat(nbody, utl_raw.reverse(d2r(newobjid, 8)));

else

--trace('[replace_objid_in_file] new id in raw: '||(d2r(newobjid, 8)));

--utl_file.put_raw(bfw, d2r(newobjid, 8));

nbody := utl_raw.concat(nbody, d2r(newobjid, 8));

end if;

-- skip objid

if addpos > hsz+5 and addinfo is not null then

trace('[replace_objid_in_file] old body len: '||utl_raw.length(dbody)||' new = 4 + '||utl_raw.length(utl_raw.substr(dbody, 5, addpos-hsz-5))||' + '||utl_raw.length(addinfo)||' + '||utl_raw.length(utl_raw.substr(dbody, addpos-hsz-4+utl_raw.length(addinfo), blksz-(addpos-1)-utl_raw.length(dtail)-utl_raw.length(addinfo)))||' + 4');

--utl_file.put_raw(bfw, utl_raw.concat(utl_raw.substr(dbody, 5, addpos-hsz-5), addinfo, utl_raw.substr(dbody, addpos-hsz, blksz-(addpos-1)-utl_raw.length(dtail)-utl_raw.length(addinfo))));

nbody := utl_raw.concat(nbody, utl_raw.substr(dbody, 5, addpos-hsz-5), addinfo, utl_raw.substr(dbody, addpos-hsz-4+utl_raw.length(addinfo), blksz-(addpos-1)-utl_raw.length(dtail)-utl_raw.length(addinfo)));

--trace('[replace_objid_in_file] new body len: '||utl_raw.length(nbody));

elsif addpos = hsz+5 and addinfo is not null then

--utl_file.put_raw(bfw, utl_raw.concat(addinfo, utl_raw.substr(dbody, addpos-hsz, blksz-(addpos-1)-utl_raw.length(dtail)-utl_raw.length(addinfo))));

nbody := utl_raw.concat(nbody, addinfo, utl_raw.substr(dbody, addpos-hsz, blksz-(addpos-1)-utl_raw.length(dtail)-utl_raw.length(addinfo)));

else

--utl_file.put_raw(bfw, utl_raw.substr(dbody, 5, blksz-hsz-4-utl_raw.length(dtail)));

nbody := utl_raw.concat(nbody, utl_raw.substr(dbody, 5, blksz-hsz-4-utl_raw.length(dtail)));

end if;

--trace('[replace_objid_in_file] tail in raw: '||dtail||'('||utl_raw.length(dtail)||')');

--utl_file.put_raw(bfw, dtail);

nbody := utl_raw.concat(nbody, dtail);

trace('[replace_objid_in_file] new body length: '||utl_raw.length(nbody));

else

--utl_file.put_raw(bfw, dhead);

--utl_file.put_raw(bfw, dbody);

nbody := utl_raw.concat(nbody, dhead, dbody);

end if;

--if utl_raw.length(nbody) != blksz then

-- trace('[replace_objid_in_file] new body length: '||utl_raw.length(nbody));

--end if;

utl_file.put_raw(bfw, nbody);

utl_file.fflush(bfw);

exception

when no_data_found then

exit;

when others then

trace('[replace_objid_in_file] '||sqlerrm);

trace('[replace_objid_in_file] '||dbms_utility.format_error_backtrace);

exit;

end;

end loop;

utl_file.fclose(bfw);

utl_file.fclose(bfr);

trace('[replace_objid_in_file] completed.');

end;

function gen_table_name(tgttable varchar2,

plus varchar2 default '',

genowner varchar2 default user)

return varchar2

as

gentab varchar2(30);

begin

select upper(tgttable||plus||surfix) into gentab from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_tables where owner = genowner and table_name = upper(tgttable||plus||surfix)) order by surfix nulls first) where rownum<=1;

return gentab;

end;

function gen_file_name( tgtfile varchar2,

plus varchar2 default '')

return varchar2

as

genfile varchar2(30);

begin

select tgtfile||plus||surfix||'.dat' into genfile from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_data_files where file_name like '%\'||tgtfile||plus||surfix||'.dat') order by surfix nulls first) where rownum<=1;

return genfile;

end;

function gen_ts_name( tgtts varchar2,

plus varchar2 default '')

return varchar2

as

gents varchar2(30);

begin

select tgtts||plus||surfix into gents from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_tablespaces where tablespace_name = tgtts||plus||surfix) order by surfix nulls first) where rownum<=1;

return gents;

end;

function get_cols_no_lob( recowner varchar2,

rectab varchar2)

return varchar2

as

cols varchar2(32767);

colno number := 0;

begin

cols := '';

for col_rec in (select column_name, data_type, nullable from dba_tab_cols where owner = recowner and table_name = rectab) loop

if col_rec.data_type not like '%lob' then

if colno > 0 then

cols := cols||',';

end if;

cols := cols||col_rec.column_name;

colno := colno + 1;

end if;

end loop;

return cols;

end;

function restore_table_row_no_lob(recowner varchar2,

rectab varchar2,

rstowner varchar2,

rsttab varchar2,

cols varchar2,

rid rowid)

return number

as

recnum number := 0;

begin

begin

execute immediate 'insert /*+*/ into '||rstowner||'.'||rsttab||'('||cols||') select '||cols||' from '||recowner||'.'||rectab||' where rowid = :rid' using rid;

recnum := recnum + sql%rowcount;

exception when others then

trace('[restore_table_row_no_lob] '||sqlerrm);

trace('[restore_table_row_no_lob] '||dbms_utility.format_error_backtrace);

null;

end;

return recnum;

end;

function restore_table_in_rows( recowner varchar2,

rectab varchar2,

rstowner varchar2,

rsttab varchar2)

return number

as

recnum number := 0;

blk_cur r_cursor;

objid number;

fid number;

blkno number;

rnum number;

gnum number;

cols varchar2(32767);

begin

begin

--trace('[restore_table_in_rows] '||'select dbms_rowid.rowid_object(rowid) objid, dbms_rowid.rowid_relative_fno(rowid) fid, dbms_rowid.rowid_block_number(rowid) blkno, count(1) rnum from '||recowner||'.'||rectab||' group by dbms_rowid.rowid_object(rowid), dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid)');

open blk_cur for 'select dbms_rowid.rowid_object(rowid) objid, dbms_rowid.rowid_relative_fno(rowid) fid, dbms_rowid.rowid_block_number(rowid) blkno, count(1) rnum from '||recowner||'.'||rectab||' group by dbms_rowid.rowid_object(rowid), dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid)';

loop

fetch blk_cur into objid, fid, blkno, rnum;

exit when blk_cur%notfound;

trace('[restore_table_in_rows] expected rows: '||rnum);

gnum := 0;

--trace('[restore_table_in_rows] block: '||blkno);

for i in 1..rnum loop

begin

--trace('[restore_table_in_rows] row: '||i);

--execute immediate 'insert /*+*/into '||rstowner||'.'||rsttab||' select * from '||recowner||'.'||rectab||' where rowid = dbms_rowid.rowid_create(1, :objid, :fid, :blkno, :i)' using objid, fid, blkno, i-1;

execute immediate 'insert /*+*/into '||rstowner||'.'||rsttab||' select * from '||recowner||'.'||rectab||' where dbms_rowid.rowid_relative_fno(rowid)=:fid and dbms_rowid.rowid_block_number(rowid)=:blkno and dbms_rowid.rowid_row_number(rowid)=:i' using fid, blkno, i-1;

recnum := recnum + sql%rowcount;

gnum := gnum + sql%rowcount;

exception when others then

if sqlcode = -22922 then

-- trace('[restore_table_in_rows] warning: unrecoverable lob found!');

if cols is null then

cols := get_cols_no_lob(recowner, rectab);

end if;

recnum := recnum + restore_table_row_no_lob(recowner, rectab, rstowner, rsttab, cols, dbms_rowid.rowid_create(1, objid, fid, blkno, i-1));

else

trace('[restore_table_in_rows] '||sqlerrm);

trace('[restore_table_in_rows] '||dbms_utility.format_error_backtrace);

end if;

null;

end;

end loop;

if gnum != rnum then

log('warning: '||(rnum-gnum)||' records lost!');

end if;

end loop;

exception when others then

trace('[restore_table_in_rows] '||sqlerrm);

trace('[restore_table_in_rows] '||dbms_utility.format_error_backtrace);

null;

end;

return recnum;

end;

function restore_table_in_rows_remote(recowner varchar2,

rectab varchar2,

rstowner varchar2,

rsttab varchar2,

dblink varchar2)

return number

as

recnum number := 0;

blk_cur r_cursor;

objid number;

fid number;

blkno number;

rnum number;

cols varchar2(32767);

begin

begin

--rollback;

open blk_cur for 'select dbms_rowid.rowid_object(rowid) objid, dbms_rowid.rowid_relative_fno(rowid) fid, dbms_rowid.rowid_block_number(rowid) blkno, count(1) rnum from '||recowner||'.'||rectab||' group by dbms_rowid.rowid_object(rowid), dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid)';

loop

fetch blk_cur into objid, fid, blkno, rnum;

exit when blk_cur%notfound;

trace('[restore_table_in_rows_remote] expected rows: '||rnum);

for i in 1..rnum loop

begin

--execute immediate 'insert /*+no_append*/into '||rstowner||'.'||rsttab||' select * from '||recowner||'.'||rectab||'@'||dblink||' where rowid = dbms_rowid.rowid_create(1, :objid, :fid, :blkno, :i)' using objid, fid, blkno, i-1;

execute immediate 'insert /*+*/into '||rstowner||'.'||rsttab||' select * from '||recowner||'.'||rectab||'@'||dblink||' where dbms_rowid.rowid_relative_fno(rowid)=:fid and dbms_rowid.rowid_block_number(rowid)=:blkno and dbms_rowid.rowid_row_number(rowid)=:i' using fid, blkno, i-1;

recnum := recnum + sql%rowcount;

--commit;

exception when others then

if sqlcode = -22922 then

if cols is null then

cols := get_cols_no_lob(recowner, rectab);

end if;

recnum := recnum + restore_table_row_no_lob(recowner, rectab, rstowner, rsttab, cols, dbms_rowid.rowid_create(1, objid, fid, blkno, i-1));

else

trace('[restore_table_in_rows_remote] '||sqlerrm);

trace('[restore_table_in_rows_remote] '||dbms_utility.format_error_backtrace);

--commit;

end if;

null;

end;

end loop;

end loop;

end;

return recnum;

end;

function restore_table_ctas(recowner varchar2,

rectab varchar2,

rstowner varchar2,

rsttab varchar2)

return number

as

recnum number := 0;

tmptab varchar2(30);

begin

tmptab := gen_table_name(rsttab, '', rstowner);

begin

execute immediate 'create table '||rstowner||'.'||tmptab||' as select /*+full(t)*/* from '||recowner||'.'||rectab||' t';

execute immediate 'insert /*+append*/ into '||rstowner||'.'||rsttab||' select /*+full(t)*/* from '||recowner||'.'||tmptab||' t';

recnum := sql%rowcount;

execute immediate 'drop table '||rstowner||'.'||tmptab;

exception when others then

--trace('[restore_table_ctas] '||sqlerrm);

--trace('[restore_table_ctas] '||dbms_utility.format_error_backtrace);

null;

end;

return recnum;

end;

function restore_table_no_lob(recowner varchar2,

rectab varchar2,

rstowner varchar2,

rsttab varchar2)

return number

as

recnum number := 0;

cols varchar2(32767);

begin

cols := get_cols_no_lob(recowner, rectab);

begin

--execute immediate 'alter system flush buffer_cache';

execute immediate 'insert /*+append*/ into '||rstowner||'.'||rsttab||'('||cols||') select /*+full(t)*/'||cols||' from '||recowner||'.'||rectab||' t';

recnum := recnum + sql%rowcount;

exception when others then

--raise;

if sqlcode = -22922 then

null;

else

recnum := recnum + restore_table_in_rows(recowner, rectab, rstowner, rsttab);

end if;

end;

trace('[restore_table_no_lob] '||recnum||' records recovered');

return recnum;

end;

function restore_table( recowner varchar2,

rectab varchar2,

rstowner varchar2,

rsttab varchar2,

selflink varchar2 default '')

return number

as

recnum number := 0;

expnum number := 0;

begin

begin

trace('[restore_table] trying to restore data to '||rstowner||'.'||rsttab);

execute immediate 'alter system flush buffer_cache';

if s_tracing then

execute immediate 'select /*+full(t)*/count(*) from '||recowner||'.'||rectab||' t' into expnum;

trace('[restore_table] expected records in this round: '||expnum);

end if;

execute immediate 'insert /*+append*/ into '||rstowner||'.'||rsttab||' select /*+full(t)*/* from '||recowner||'.'||rectab||' t';

recnum := recnum + sql%rowcount;

if s_tracing and expnum != sql%rowcount then

trace('[restore_table] '||(expnum-sql%rowcount)||' records lost!');

return -1; -- for test

end if;

exception when others then

--raise;

if sqlcode = -22922 then

log('warning: unrecoverable lob found!');

recnum := recnum + restore_table_in_rows_remote(recowner, rectab, rstowner, rsttab, selflink);

--recnum := recnum + restore_table_no_lob(recowner, rectab, rstowner, rsttab);

else

trace(sqlerrm);

trace('[restore_table] '||dbms_utility.format_error_backtrace);

--recnum := recnum + restore_table_in_rows_remote(recowner, rectab, rstowner, rsttab, selflink);

--return -1; -- test

recnum := recnum + restore_table_in_rows(recowner, rectab, rstowner, rsttab);

end if;

end;

execute immediate 'alter system flush buffer_cache';

trace('[restore_table] '||recnum||' records recovered');

return recnum;

end;

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,

fillblks number default 5,

selflink varchar2 default '',

endianess number default 1)

as

-- blk blob;

--vrw raw(32767);

frw raw(32767);

tsz number := 4;

hsz number := 28;

objr raw(4);

objn number;

dtail raw(4);

dhead raw(32);

dbody raw(32767);

--bfr bfile;

bfo utl_file.file_type;

bfr utl_file.file_type;

bfw utl_file.file_type;

fillednum number := 0;

dummyheader number;

dummyblks number;

blkstofill number := fillblks;

recnum number := 0;

rstnum number := 0;

i number := 0;

j number := 0;

truncblks number := 0;

begin

execute immediate 'truncate table '||rstowner||'.'||rsttab;

execute immediate 'alter system set db_block_checking=false scope=memory';

execute immediate 'alter system set db_block_checksum=false scope=memory';

execute immediate 'alter system set "_db_block_check_objtyp"=false scope=memory';

execute immediate 'alter session set events ''10231 trace name context forever, level 10''';

execute immediate 'alter session set events ''10233 trace name context forever, level 10''';

select header_block+1, blocks-3 into dummyheader, dummyblks from dba_segments where owner = recowner and segment_name = rectab;

if blkstofill > dummyblks then

blkstofill := dummyblks;

end if;

bfo := utl_file.fopen(srcdir, srcfile, 'rb');

--utl_file.get_raw(bfo, dbody, blksz-hsz-tsz);

--utl_file.get_raw(bfo, dtail, tsz);

bfr := utl_file.fopen(copydir, copyfile, 'rb');

bfw := utl_file.fopen(recdir, recfile, 'wb');

-- reach to the transaction blocks to be filled

i := 0;

while true loop

begin

utl_file.get_raw(bfr, frw, blksz);

utl_file.put_raw(bfw, frw);

i := i+1;

exit when i=dummyheader+fillednum;

exception when others then

--raise;

--trace('[recover_table] block no.: '||i);

exit;

end;

end loop;

-- go through the data file of truncated table

while true loop

begin

--trace('[recover_table] '||j);

j := j+1;

--objr := substrb(rawtohex(dhead), 49, 8);

utl_file.get_raw(bfo, dhead, hsz);

if hsz <= 24 then

utl_file.get_raw(bfo, dbody, blksz-tsz-hsz);

--objr := substrb(rawtohex(dbody), 49-hsz*2, 8);

objr := utl_raw.substr(dbody, 25-hsz, 4);

else

--objr := substrb(rawtohex(dhead), 49, 8);

objr := utl_raw.substr(dhead, 25, 4);

end if;

if endianess > 0 then

--objn := to_number(utl_raw.reverse(hextoraw(objr)), 'xxxxxxxx');

objn := to_number(rawtohex(utl_raw.reverse(objr)), 'xxxxxxxx');

else

--objn := to_number(hextoraw(objr), 'xxxxxxxx');

objn := to_number(rawtohex(objr), 'xxxxxxxx');

end if;

-- check if block belongs to truncated table

if objn != oriobjid or substrb(rawtohex(dhead), 1, 2) != '06' then

if hsz > 24 then

utl_file.get_raw(bfo, dbody, blksz-hsz);

else

utl_file.get_raw(bfo, dtail, tsz);

end if;

else

--trace('[recover_table] find it.');

truncblks := truncblks + 1;

if hsz > 24 then

utl_file.get_raw(bfo, dbody, blksz-hsz-tsz);

end if;

utl_file.get_raw(bfo, dtail, tsz);

if not utl_file.is_open(bfr) then

bfr := utl_file.fopen(copydir, copyfile, 'rb');

end if;

if not utl_file.is_open(bfw) then

bfw := utl_file.fopen(recdir, recfile, 'wb');

end if;

-- filling the trans block

utl_file.get_raw(bfr, dhead, hsz);

utl_file.put_raw(bfw, dhead); -- put original header

utl_file.put_raw(bfw, dbody); -- replace body

utl_file.get_raw(bfr, dbody, blksz-hsz-tsz); -- forward pointer in original file copy

utl_file.get_raw(bfr, dtail, tsz); -- get original tail

utl_file.put_raw(bfw, dtail); -- put original tail

fillednum := fillednum+1;

i := i+1;

-- no trans data block left, copy recovered data to backup table and fill the left blocks

if fillednum >= blkstofill then

--if fillednum+blkstofill-1 >= dummyblks then

begin

while true loop

begin

utl_file.get_raw(bfr, frw, blksz);

utl_file.put_raw(bfw, frw);

i := i+1;

exception when others then

if utl_file.is_open(bfr) then

utl_file.fclose(bfr);

end if;

if utl_file.is_open(bfw) then

utl_file.fclose(bfw);

end if;

exit;

end;

end loop;

rstnum := restore_table(recowner, rectab, rstowner, rsttab, selflink);

-- for test

exit when rstnum < 0;

recnum := recnum+rstnum;

fillednum := 0;

commit;

bfr := utl_file.fopen(copydir, copyfile, 'rb');

bfw := utl_file.fopen(recdir, recfile, 'wb');

-- go to the transaction blocks again

i := 0;

while true loop

begin

utl_file.get_raw(bfr, frw, blksz);

utl_file.put_raw(bfw, frw);

i := i+1;

exit when i=dummyheader+fillednum;

exception when others then

--raise;

--trace('[recover_table] block no.: '||i);

exit;

end;

end loop;

utl_file.fflush(bfw);

exception when others then

trace('[recover_table 2-1] '||sqlerrm);

trace('[recover_table 2-1] '||dbms_utility.format_error_backtrace);

null;

end;

end if;

end if;

exception

when no_data_found then

exit;

when others then

trace('[recover_table 2-2] '||sqlerrm);

trace('[recover_table 2-2] '||dbms_utility.format_error_backtrace);

exit;

end;

end loop;

-- last blocks not full filled dummy table

--if fillednum+blkstofill-1 < dummyblks then

if fillednum < blkstofill and rstnum>=0 then

begin

while true loop

begin

utl_file.get_raw(bfr, frw, blksz);

utl_file.put_raw(bfw, frw);

i := i+1;

exception when others then

if utl_file.is_open(bfr) then

utl_file.fclose(bfr);

end if;

if utl_file.is_open(bfw) then

utl_file.fclose(bfw);

end if;

exit;

end;

end loop;

recnum := recnum+restore_table(recowner, rectab, rstowner, rsttab, selflink);

--fillednum := 0;

commit;

end;

end if;

if utl_file.is_open(bfr) then

utl_file.fclose(bfr);

end if;

if utl_file.is_open(bfw) then

utl_file.fclose(bfw);

end if;

if utl_file.is_open(bfo) then

utl_file.fclose(bfo);

end if;

utl_file.fclose_all();

execute immediate 'alter session set events ''10233 trace name context off''';

execute immediate 'alter session set events ''10231 trace name context off''';

execute immediate 'alter system set "_db_block_check_objtyp"=true scope=memory';

execute immediate 'alter system set db_block_checksum=true scope=memory';

execute immediate 'alter system set db_block_checking=true scope=memory';

log(truncblks||' truncated data blocks found. ');

log(recnum||' records recovered in backup table '||rstowner||'.'||rsttab);

end;

procedure get_seg_meta( segowner varchar2,

segname varchar2,

srcdir varchar2,

dtail out raw,

addinfo out raw,

blksz number default 8192)

as

frw raw(32767);

firstblk number;

hdfile varchar2(255);

bfo utl_file.file_type;

i number := 0;

begin

select header_block+1 into firstblk from dba_segments where owner = segowner and segment_name = segname;

select substr(file_name,decode(instr(d.file_name, '\', -1), 0, instr(file_name, '/', -1), instr(file_name, '\', -1))+1) into hdfile from dba_data_files d, dba_segments s where s.header_file = d.file_id and s.owner = segowner and s.segment_name = segname;

bfo := utl_file.fopen(srcdir, hdfile, 'rb');

-- reach to the truncated data blocks

i := 0;

while true loop

begin

utl_file.get_raw(bfo, frw, blksz);

i := i+1;

exit when i = firstblk;

exception when others then

exit;

end;

end loop;

utl_file.get_raw(bfo, frw, blksz);

dtail := utl_raw.substr(frw, blksz-3, 4);

addinfo := utl_raw.substr(frw, 39, 2);

utl_file.fclose(bfo);

end;

function get_seg_data_id( segowner varchar2,

segname varchar2,

srcdir varchar2,

blksz number default 8192,

endianess number default 1)

return number

as

frw raw(32767);

hsz number := 28;

firstblk number;

hdfile varchar2(255);

bfo utl_file.file_type;

i number := 0;

objr raw(4);

objn number;

begin

select header_block+1 into firstblk from dba_segments where owner = segowner and segment_name = segname;

select substr(file_name,decode(instr(d.file_name, '\', -1), 0, instr(file_name, '/', -1), instr(file_name, '\', -1))+1) into hdfile from dba_data_files d, dba_segments s where s.header_file = d.file_id and s.owner = segowner and s.segment_name = segname;

bfo := utl_file.fopen(srcdir, hdfile, 'rb');

-- reach to the truncated data blocks

i := 0;

while true loop

begin

utl_file.get_raw(bfo, frw, blksz);

i := i+1;

exit when i = firstblk;

exception when others then

exit;

end;

end loop;

utl_file.get_raw(bfo, frw, hsz);

objr := utl_raw.substr(frw, 25, 4);

if endianess > 0 then

objn := to_number(rawtohex(utl_raw.reverse(objr)), 'xxxxxxxx');

else

objn := to_number(rawtohex(objr), 'xxxxxxxx');

end if;

utl_file.fclose(bfo);

return objn;

end;

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,

fillblks number default 5,

selflink varchar2 default '')

as

tgtobjid number;

recobjid number;

endianess number;

tmpcopyf varchar2(256);

tsname varchar2(30);

readprop varchar2(30);

dtail raw(4);

addinfo raw(32);

begin

select instr(platform_name, 'windows') into endianess from v$database where rownum<=1;

select data_object_id into recobjid from dba_objects where owner = recowner and object_name = rectab and object_type='table' and rownum<=1;

log('begin to recover table '||tgtowner||'.'||tgttable);

tgtobjid := get_seg_data_id(tgtowner, tgttable, srcdir, blksz, endianess);

if s_repobjid then

get_seg_meta(recowner, rectab, srcdir, dtail, addinfo, blksz);

select tablespace_name into tsname from dba_tables where owner = tgtowner and table_name = tgttable and rownum<=1;

select status into readprop from dba_tablespaces where tablespace_name = tsname;

if readprop != 'read only' then

execute immediate 'alter tablespace '||tsname||' read only';

execute immediate 'alter system flush buffer_cache';

end if;

for file_rec in (select substr(file_name,decode(instr(d.file_name, '\', -1), 0, instr(file_name, '/', -1), instr(file_name, '\', -1))+1) as filename from dba_data_files d, dba_tables t where d.tablespace_name = t.tablespace_name and t.owner = tgtowner and t.table_name = tgttable) loop

begin

tmpcopyf := gen_file_name(file_rec.filename, '$');

copy_file(srcdir, file_rec.filename, srcdir, tmpcopyf);

--replace_segmeta_in_file(srcdir, tmpcopyf, srcdir, file_rec.filename, tgtobjid, recobjid, dtail, 39, addinfo, blksz, endianess);

replace_segmeta_in_file(srcdir, tmpcopyf, srcdir, file_rec.filename, tgtobjid, recobjid, dtail, 39, '', blksz, endianess);

recover_table(recobjid, recowner, rectab, rstowner, rsttab, srcdir, file_rec.filename, recdir, recfile, copydir, copyfile, blksz, fillblks, selflink, endianess);

--recover_table(tgtobjid, recowner, rectab, rstowner, rsttab, srcdir, file_rec.filename, recdir, recfile, copydir, copyfile, blksz, fillblks, selflink, endianess);

copy_file(srcdir, tmpcopyf, srcdir, file_rec.filename);

utl_file.fremove(srcdir, tmpcopyf);

trace('[recover_table 1] '||tmpcopyf||' removed.');

exception when others then

trace('[recover_table 1] '||sqlerrm);

trace('[recover_table 1] '||dbms_utility.format_error_backtrace);

end;

end loop;

if readprop != 'read only' then

execute immediate 'alter tablespace '||tsname||' read write';

end if;

else

for file_rec in (select substr(file_name,decode(instr(d.file_name, '\', -1), 0, instr(file_name, '/', -1), instr(file_name, '\', -1))+1) as filename from dba_data_files d, dba_tables t where d.tablespace_name = t.tablespace_name and t.owner = tgtowner and t.table_name = tgttable) loop

begin

recover_table(tgtobjid, recowner, rectab, rstowner, rsttab, srcdir, file_rec.filename, recdir, recfile, copydir, copyfile, blksz, fillblks, selflink, endianess);

exception when others then

trace('[recover_table 1] '||sqlerrm);

trace('[recover_table 1] '||dbms_utility.format_error_backtrace);

end;

end loop;

end if;

log('recovery completed.');

end;

procedure prepare_files(tgtowner varchar2,

tgttable varchar2,

datapath varchar2,

datadir in out varchar2,

rects out varchar2,

recfile out varchar2,

rstts out varchar2,

rstfile out varchar2,

blksz out varchar2,

rectsblks number default 16,

rsttsblks number default 2560)

as

ext_mgmt varchar2(30);

ss_mgmt varchar2(30);

begin

select block_size, extent_management, segment_space_management into blksz, ext_mgmt, ss_mgmt from dba_tablespaces ts, dba_tables t where t.tablespace_name = ts.tablespace_name and t.owner = upper(tgtowner) and t.table_name = upper(tgttable);

select datadir||surfix into datadir from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_directories where directory_name = datadir||surfix) order by surfix nulls first) where rownum<=1;

log('directory name: '||datadir);

execute immediate 'create directory '||datadir||' as '''||datapath||'''';

--select 'fy_rec_data'||surfix into rects from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_tablespaces where tablespace_name = 'fy_rec_data'||surfix) order by surfix nulls first) where rownum<=1;

--select 'fy_rec_data'||surfix||'.dat' into recfile from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_data_files where file_name like '%\fy_rec_data'||surfix||'.dat') order by surfix nulls first) where rownum<=1;

rects := gen_ts_name('fy_rec_data','');

recfile := gen_file_name('fy_rec_data','');

log('recover tablespace: '||rects||'; data file: '||recfile);

execute immediate 'create tablespace '||rects||' datafile '''||rtrim(datapath, '\')||'\'||recfile||''' size '||to_char(blksz*rectsblks/1024)||'k autoextend off extent management '||ext_mgmt||' segment space management '||ss_mgmt;

select 'fy_rst_data'||surfix into rstts from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_tablespaces where tablespace_name = 'fy_rest_data'||surfix) order by surfix nulls first) where rownum<=1;

--select 'fy_rst_data'||surfix||'.dat' into rstfile from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_data_files where file_name like '%\fy_rest_data'||surfix||'.dat') order by surfix nulls first) where rownum<=1;

rstts := gen_ts_name('fy_rst_data','');

rstfile := gen_file_name('fy_rst_data','');

log('restore tablespace: '||rstts||'; data file: '||rstfile);

execute immediate 'create tablespace '||rstts||' datafile '''||rtrim(datapath, '\')||'\'||rstfile||''' size '||to_char(blksz*rsttsblks/1024)||'k autoextend on extent management '||ext_mgmt||' segment space management '||ss_mgmt;

end;

procedure clean_up_ts(rects varchar2,

rstts varchar2 default null)

as

readprop varchar2(30);

begin

select status into readprop from dba_tablespaces where tablespace_name = rects;

if readprop = 'read only' then

execute immediate 'alter tablespace '||rects||' read write';

end if;

for tab_rec in (select owner, table_name from dba_tables where tablespace_name = rects) loop

execute immediate 'drop table '||tab_rec.owner||'.'||tab_rec.table_name;

end loop;

if rstts is not null then

for tab_rec in (select owner, table_name from dba_tables where tablespace_name = rstts) loop

execute immediate 'drop table '||tab_rec.owner||'.'||tab_rec.table_name;

end loop;

end if;

end;

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 in out varchar2,

rsttab in out varchar2,

copyfile out varchar2)

as

blksz number;

blkno number;

cols varchar2(32767);

vals varchar2(32767);

colno number := 0;

begin

if rectab is null then

select block_size into blksz from dba_tablespaces ts, dba_tables t where t.tablespace_name = ts.tablespace_name and t.owner = upper(tgtowner) and t.table_name = upper(tgttable);

-- select upper(tgttable||'$'||surfix) into rectab from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_tables where owner = recowner and table_name = upper(tgttable||'$'||surfix)) order by surfix nulls first) where rownum<=1;

rectab := gen_table_name(tgttable, '$', recowner);

log('recover table: '||recowner||'.'||rectab);

--trace('[fill_blocks] create table '||recowner||'.'||rectab||' tablespace '||rects||' storage(initial '||to_char(blks*blksz/1024)||'k) as select * from '||tgtowner||'.'||tgttable||' where 1=2');

execute immediate 'create table '||recowner||'.'||rectab||' tablespace '||rects||' storage(initial '||to_char(blks*blksz/1024)||'k) as select * from '||tgtowner||'.'||tgttable||' where 1=2';

else

--execute immediate 'truncate table '||recowner||'.'||rectab;

execute immediate 'delete from '||recowner||'.'||rectab;

commit;

end if;

cols := '';

vals := '';

for col_rec in (select column_name, data_type, nullable from dba_tab_cols where owner = recowner and table_name = rectab) loop

if col_rec.nullable = 'n' then

execute immediate 'alter table '||recowner||'.'||rectab||' modify '||col_rec.column_name||' null';

end if;

if colno < 6 then

if col_rec.data_type like '%char%' or col_rec.data_type like '%raw%' then

if colno > 0 then

cols := cols||',';

vals := vals||',';

end if;

cols := cols||col_rec.column_name;

vals := vals||'''a''';

colno := colno + 1;

elsif col_rec.data_type like '%number%' or col_rec.data_type = 'float' then

if colno > 0 then

cols := cols||',';

vals := vals||',';

end if;

cols := cols||col_rec.column_name;

vals := vals||'0';

colno := colno + 1;

elsif col_rec.data_type like '%timestamp%' or col_rec.data_type = 'date' then

if colno > 0 then

cols := cols||',';

vals := vals||',';

end if;

cols := cols||col_rec.column_name;

vals := vals||'sysdate';

colno := colno + 1;

end if;

end if;

end loop;

--select upper(tgttable||'$$'||surfix) into rsttab from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_tables where owner = rstowner and table_name = upper(tgttable||'$$'||surfix)) order by surfix nulls first) where rownum<=1;

if rsttab is null then

rsttab := gen_table_name(tgttable, '$$', rstowner);

log('restore table: '||rstowner||'.'||rsttab);

execute immediate 'create table '||rstowner||'.'||rsttab||' tablespace '||rstts||' as select * from '||recowner||'.'||rectab||' where 1=2';

else

execute immediate 'truncate table '||rstowner||'.'||rsttab;

end if;

--trace('[fill_blocks] insert into '||recowner||'.'||rectab||'('||cols||') values ('||vals||')');

while true loop

execute immediate 'insert into '||recowner||'.'||rectab||'('||cols||') values ('||vals||')';

execute immediate 'select count(unique(dbms_rowid.rowid_block_number( rowid ))) from '||recowner||'.'||rectab into blkno ;

exit when blkno >= blks-3;

end loop;

commit;

execute immediate 'alter system flush buffer_cache';

execute immediate 'delete from '||recowner||'.'||rectab;

commit;

execute immediate 'alter system flush buffer_cache';

trace('[fill_blocks] data blocks formatted.');

execute immediate 'alter tablespace '||rects||' read only';

select 'fy_rec_data_copy'||surfix||'.dat' into copyfile from (select surfix from (select null surfix from dual union all select level surfix from dual connect by level <= 255) where not exists (select 1 from dba_data_files where file_name like '%fy_rec_data_copy'||surfix||'.dat') order by surfix nulls first) where rownum<=1;

copy_file(datadir, recfile, datadir, copyfile);

log('copy file of recover tablespace: '||copyfile);

end;

procedure test_rec1( tow varchar2 default 'sys',

ttb varchar2 default 't_chain',

fbks number default 1,

datapath varchar2 default 'd:\oracle\product\10.2.0\oradata\edgar\datafile\')

as

tgtowner varchar2(30):= upper(tow);

tgttable varchar2(30):= upper(ttb);

datadir varchar2(30);

rects varchar2(30);

recfile varchar2(30);

rstts varchar2(30);

rstfile varchar2(30);

blksz number;

rectab varchar2(30);

rsttab varchar2(30);

copyfile varchar2(30);

begin

datadir := 'fy_data_dir';

prepare_files(tgtowner, tgttable, datapath, datadir, rects, recfile, rstts, rstfile, blksz);

rects := 'fy_rec_data';

rstts := 'fy_rst_data';

recfile := 'fy_rec_data.dat';

fill_blocks(tgtowner, tgttable, datadir, rects, recfile, rstts, 8, tgtowner, tgtowner, rectab, rsttab, copyfile);

recover_table(tgtowner, tgttable, tgtowner, rectab, tgtowner, rsttab, datadir, datadir, recfile, datadir, copyfile, blksz, fbks, 'myself');

end;

procedure test_rec2( tow varchar2 default 'sys',

ttb varchar2 default 't_chain',

fbks number default 1)

as

tgtowner varchar2(30):= upper(tow);

tgttable varchar2(30):= upper(ttb);

datadir varchar2(30);

rects varchar2(30);

recfile varchar2(30);

rstts varchar2(30);

blksz number;

rectab varchar2(30);

rsttab varchar2(30);

copyfile varchar2(30);

begin

datadir := 'fy_data_dir';

rects := 'fy_rec_data';

rstts := 'fy_rst_data';

recfile := 'fy_rec_data.dat';

clean_up_ts(rects, rstts);

select block_size into blksz from dba_tablespaces ts, dba_tables t where ts.tablespace_name = t.tablespace_name and t.owner = tgtowner and t.table_name = tgttable;

fill_blocks(tgtowner, tgttable, datadir, rects, recfile, rstts, 8, tgtowner, tgtowner, rectab, rsttab, copyfile);

recover_table(tgtowner, tgttable, tgtowner, rectab, tgtowner, rsttab, datadir, datadir, recfile, datadir, copyfile, blksz, fbks, 'myself');

end;

begin

null;

end pg_lan_recover_tab_data;

/