Protection method for oracle database truncate operation

Abstract

The invention discloses a protection method for Oracle database Truncate operation, comprising the following steps: S1) when a Truncate operation occurs in the Oracle database, a trigger is set to record the name of the table deleted by Truncate and stored in the beforetruncate table; S2) Regularly scan the beforetruncate table, if there is a newly deleted table, then query to find the section header block of the table; S3) modify the relevant id of the table to the value before Truncate, and generate a reverse script for modifying back at the same time; S4) when reaching the predetermined After setting the protection threshold, run the reverse script to restore the identification to the situation after Truncate, and clean up the data that needs to be recycled through the Oracle database. The invention makes the restoration of the Truncate simple and fast without affecting normal data deletion.

Description

technical field [0001] The invention relates to an Oracle database processing method, in particular to a protection method for Oracle database Truncate operation. Background technique [0002] During the operation of the database system, the following situations often occur: [0003] 1. The amount of data suddenly increased, and the customer had no storage space. Now the storage space needs to be cleared. After communication, it was found that the data of a table can be completely deleted, so the Truncate deletion method was used (delete in Oracle is slower and will generate a large number of archive). After the deletion, it is found that the data is still useful and needs to be retrieved. [0004] 2. The operation and maintenance personnel accidentally executed the Truncate command and made a wrong operation, resulting in the complete deletion of data. [0005] At present, there is generally no way in the industry to avoid this kind of situation. The only way is to creat...

AI Technical Summary

Problems solved by technology

[0004] 2. The operation and maintenance personnel accidentally executed the Truncate command and made a wrong operation, resulting in the complete deletion of data

If the amount of data is very large, this recovery process may take several days, which will seriously affect the normal operation of some customers

[0007] 2. There is no full backup or archive. If this kind of data is truncated and you want to retrieve it, you can only use the underlying recovery tools: such as DUL, ODU, AUL and other tools

very complex operation

[0008] It can be seen from the above that there is currently no effective protection for truncate. Once a truncate operation occurs, the data may be completely lost. Even if it can be restored, it will be extremely time-consuming and labor-intensive.

Images