模拟数据丢失(truncate table)

来源：互联网发布：黑客帝国动画版知乎编辑：程序博客网时间：2024/05/16 06:14

参考：http://www.hellodba.com/reader.php?ID=217

好好学习，以备不时之需。

模拟数据：
create table scott.truncate_test tablespace users
as
select * from Hr.EMPLOYEES;

insert into scott.truncate_test
select * from scott.truncate_test;
commit;

select count(1) from scott.truncate_test;

SQL> select tablespace_name from dba_tables where table_name='TRUNCATE_TEST';
SQL> select file_name from DBA_DATA_FILES where TABLESPACE_NAME='USERS';
cp /u01/app/awr/users01.dbf /tmp/users01.dat
chown oracle:oinstall /tmp/users01.dat

执行truncate table:
truncate table scott.truncate_test;
select count(1) from scott.truncate_test;

运行FY_Recover_Data
--sys用户
SQL> @/home/oracle/FY_Recover_Data.SQL

--开始恢复
exec fy_recover_data.recover_truncated_table('SCOTT','TRUNCATE_TEST',1,'/tmp','/u01/app/awr/users01.dbf;');
注：执行上的SQL产生2个表空间(2个数据文件)，还有1个copy文件。

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 := 'SCOTT';
tgttable := 'TRUNCATE_TEST';
datapath := '/u01/app/awr/';
datadir := 'DUMP_FILE_DIR';
sys.Fy_Recover_data.prepare_files(tgtowner, tgttable, datapath, datadir, rects, recfile, rstts, rstfile, blksz);
sys.Fy_Recover_data.fill_blocks(tgtowner, tgttable, datadir, rects, recfile, rstts, 8, tgtowner, tgtowner, rectab, rsttab, copyfile);
sys.Fy_Recover_data.recover_table(tgtowner, tgttable, tgtowner, rectab, tgtowner, rsttab, datadir, datadir, recfile,datadir, copyfile, blksz);
end;
/

Fy_Recover_data.prepare_files(tgtowner, tgttable, datapath, datadir, rects, recfile, rstts, rstfile, blksz);
*
ERROR at line 19:
ORA-06550: line 19, column 17:
PLS-00302: component 'PREPARE_FILES' must be declared
ORA-06550: line 19, column 1:
PL/SQL: Statement ignored
ORA-06550: line 20, column 17:
PLS-00302: component 'FILL_BLOCKS' must be declared
ORA-06550: line 20, column 1:
PL/SQL: Statement ignored
ORA-06550: line 21, column 17:
PLS-00302: component 'RECOVER_TABLE' must be declared
ORA-06550: line 21, column 1:
PL/SQL: Statement ignored

数据比较：
select count(1) from scott.TRUNCATE_TEST$$;

COUNT(1)
----------
214
恢复后scott.TRUNCATE_TEST$$数据内容和truncate之前相同，恢复完成。

恢复数据后，把恢复时产生的2个表空间删除，再删除对应数据文件
drop tablespace FY_REC_DATA INCLUDING CONTENTS;
drop tablespace FY_RST_DATA INCLUDING CONTENTS;
$ cd /tmp
$ rm FY_REC_DATA_COPY.DAT FY_REC_DATA.DAT FY_RST_DATA.DAT

0 0