1、 表是否能成功恢复，取决于被truncate了的表所占用的数据块是不是被新的段(表、索引等)所重用，如果被重用就无法完成这样的恢复了，看个人运气了 
。
。2、需用到牛人写的恢复包，FY_Recover_Data。
3、示例：
[oracle@databak ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 19 11:19:10 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @FY_Recover_Data.SQL
Package created.
Package body created.
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> truncate table emp;
Table truncated.
SQL> select count(*) from emp;
COUNT(*)
----------
0
SQL> conn / as sysdba
Connected.
SQL> set serveroutput on
SQL> set timing on
SQL> 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'; --------- 表所在schema
tgttable := 'EMP'; ---------需恢复的表
datapath := '/data/oracle10g/oradata/ccod/'; ---------数据文件存放路径
datadir := 'FY_DATA_DIR'; ---------可保持不变
Fy_Recover_data.prepare_files(tgtowner, tgttable, datapath, datadir, rects, recfile, rstts, rstfile, blksz);
Fy_Recover_data.fill_blocks(tgtowner, tgttable, datadir, rects, recfile, rstts, 8, tgtowner, tgtowner, rectab, rsttab, copyfile);
Fy_Recover_data.recover_table(tgtowner, tgttable, tgtowner, rectab, tgtowner, rsttab, datadir, datadir, recfile,datadir, copyfile, blksz);
end;
/
以上脚本执行后，显示如下：
11:26:48: Directory Name: FY_DATA_DIR
11:26:48: Recover Tablespace: FY_REC_DATA; Data File: FY_REC_DATA.DAT
11:26:48: Restore Tablespace: FY_RST_DATA; Data File: FY_RST_DATA.DAT
11:26:49: Recover Table: SCOTT.EMP$
11:26:50: Restore Table: SCOTT.EMP$$
11:26:57: [fill_blocks] Data Blocks formatted.
11:26:57: [copy_file] begin copy file: FY_DATA_DIR\FY_REC_DATA.DAT =>
FY_DATA_DIR\FY_REC_DATA_COPY.DAT
11:26:57: [copy_file] completed.
11:26:57: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT
11:26:57: begin to recover table SCOTT.EMP
11:26:59: [restore_table] Trying to restore data to SCOTT.EMP$$
11:26:59: [restore_table] Expected Records in this round: 14
11:26:59: [restore_table] 14 records recovered
11:26:59: 1 truncated data blocks found.
11:26:59: 14 records recovered in backup table SCOTT.EMP$$
11:26:59: Recovery completed.
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.51
查看恢复出来的表的数据
SQL> select * from scott.emp$$;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
Elapsed: 00:00:00.01
通过回滚出来的表，恢复被truncate掉的表
SQL> insert into scott.emp select * from scott.emp$$;
14 rows created.
Elapsed: 00:00:00.01
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
Elapsed: 00:00:00.00
祝好运！
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20553601/viewspace-749457/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
【审计】FGA细粒度审计
下一篇：
FY_Recover_Data
请登录后发表评论
登录
全部评论
