Oracle Table Recovery (TRUNCATE)

First, Analog truncate operation:
Sql> Conn Scott/tiger
Sql> Select COUNT (*) from T;
COUNT (*)
----------
13
sql> truncate TABLE t;
Second, restore the data of the TRUNCATE TABLE
1. Download the HELLODBA Recovery tool to the database server
Use the terminal under Linux, as follows:
Under Windows, enter the following URL:

Http://www.hellodba.com/Download/FY_Recover_Data.zip

2. Unzip the downloaded FY_RECOVER_DATA.PCK
[Email protected] ~]$ Unzip Fy_recover_data.zip
Unzip to get a file Fy_recover_data.sql
The following 3-7 steps are performed with the SYS user
3. Use the SYS user to perform the extracted script (I extracted the file here in/home/oracle/)
Sql> @/home/oracle/fy_recover_data.sql
This script actually creates a package named Fy_recover_data under the SYS user.
4. Use the SYS user to find the path to the data file that holds the TRUNCATE table, and the next step is to use this file path
Sql> select file_name from Dba_data_files F, dba_tables t where t.owner= ' SCOTT ' and t.table_name= ' t ' and t.tablespace_n ame = F.tablespace_name;
Look, the result is/u01/oracle/oradata/orcl/users01.dbf.
5. Use the SYS user to start the recovery by doing the following:
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 '; --table owner
Tgttable: = ' T '; --table Name
DataPath: = '/u01/oracle/oradata/orcl/'; --Must be the same directory as the data file where the table is truncate
DataDir: = ' fy_data_dir '; The name of the directory in the--oracle can be modified
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, CO Pyfile);
Fy_recover_data.recover_table (Tgtowner, tgttable, Tgtowner, Rectab, Tgtowner, Rsttab, DataDir, DataDir, Recfile, DataDir, CopyFile, Blksz);
End
/
Note: execution on SQL generates 2 tablespace fy_rec_data, Fy_rst_data, and 1 copy files.
6. Use the SYS user to plug the recovered data back into the scott.t table from the scott.t$$
Note: scott.t$$ is the data before the SCOTT.T table truncate
sql> INSERT INTO scott.t select * from scott.t$$;
Rows created.
Sql> commit;
Commit complete.
Sql> Select COUNT (*) from T;
COUNT (*)
----------
13
You can see that the data being truncate has been restored.
7.2 tablespace and data files generated when recovering with sys user deletion
Sql> drop tablespace fy_rec_data including contents and datafiles;
Tablespace dropped.
Sql> drop tablespace fy_rst_data including contents and datafiles;
Tablespace dropped.

Oracle Table Recovery (TRUNCATE)