Oracle未开归档情况下误操作truncate 表后的抢救办法

一、准备数据

SQL> conn scott/tiger
Connected.

SQL> create table t as select \* from dba\_objects;

Table created.

SQL> truncate table t;

Table truncated.

SQL> select count(\*) from t;

## COUNT(\*)

         0	 	 
复制

####二、方法一、闪回查询

SQL> create table t as select * from dba_objects;

Table created.

SQL> truncate table t;

Table truncated.

SQL> select count(*) from t;

  COUNT(*)
----------
         0

SQL> insert into t select * from t as of timestamp sysdate-2/1440;

72679 rows created.
复制

三、方法二、FY_Recover_Data

[oracle\@19c01 \~]\$ sqlplus / as sysdba

SQL\*Plus: Release 19.0.0.0.0 - Production on Mon Nov 14 20:12:47 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> @FY\_Recover\_Data.pck

Package created.

Package body created.

SQL> set serveroutput on
SQL> set timing on
SQL>
SQL>
SQL> exec fy\_recover\_data.recover\_truncated\_table('scott','t')
20:36:45: Use existing Directory Name: FY\_DATA\_DIR
20:36:48: Recover Table: SCOTT.T1\$
20:36:48: Restore Table: SCOTT.T1`$
20:37:20: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT
20:37:20: begin to recover table SCOTT.T1
20:37:21: Use existing Directory Name: TMP_HF_DIR
20:37:21: Recovering data in datafile
/u01/app/oracle/oradata/PROD4/datafile/test01.dbf
20:37:21: Use existing Directory Name: TMP_HF_DIR
20:37:55: 1411 truncated data blocks found.
20:37:55: 72679 records recovered in backup table SCOTT.T1$`
20:37:55: Total: 1411 truncated data blocks found.
20:37:55: Total: 72679 records recovered in backup table SCOTT.T1`$
20:37:55: Recovery completed.
20:37:55: Data has been recovered to SCOTT.T1$`

PL/SQL procedure successfully completed.

Elapsed: 00:01:10.01

SQL> select count(\*) from scott.t1\$\$;

## COUNT(\*)

     72679

Elapsed: 00:00:00.03

SQL> insert into /\* +append \*/ scott.t` select * from scott.t`\$\$;

72679 rows created.

Elapsed: 00:00:00.19
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01

SQL> select name from v\$tablespace;

## NAME

SYSAUX
SYSTEM
UNDOTBS1
USERS
TEMP
TEST
FY\_REC\_DATA
EXAMPLE
FY\_RST\_DATA

9 rows selected.

SQL> drop tablespace FY\_REC\_DATA including contents and datafiles;

Tablespace dropped.

Elapsed: 00:00:04.81
SQL> drop tablespace FY\_RST\_DATA including contents and datafiles;

Tablespace dropped.

Elapsed: 00:00:02.73

复制

以上两种方法针对未开归档情况，开归档的情况下，办法就会有很多。后续测试总结