Skip to content
Home » Oracle » How Oracle Revert INSERT INTO

How Oracle Revert INSERT INTO

Rollback INSERT INTO

If you have not committed INSERT INTO statements, then you have a chance to roll data back to the original state. Just issue a rollback command.

SQL> rollback;

Rollback complete.

Revert INSERT INTO

To revert committed INSERT INTO statements, you need more skills to do it. Suppose that you have created two rows by INSERT INTO statements like this:

SQL> INSERT INTO HR.EMPLOYEES (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID) VALUES (207, 'Rowe', '[email protected]', '25-APR-19', 'SA_REP');

1 row created.

SQL> INSERT INTO HR.EMPLOYEES (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID) VALUES (208, 'Smit', '[email protected]', '25-APR-19', 'FI_MGR');

1 row created.

If you are pretty sure that the transaction is not committed, you can always rollback changes of Data Manipulation Language (DML) on the table any time. Otherwise, you have to find some ways to revert it. In this post, I introduce two ways to revert inserted rows by DELETE FROM statements.

Flashback Transaction Query

If those rows are newly inserted, you can use Flashback Transaction Query to retrieve the undo statements against inserted rows.

SQL> select undo_sql from flashback_transaction_query where operation = 'INSERT' and table_owner = 'HR' and table_name = 'EMPLOYEES' and start_timestamp > to_date('2019-04-25 19:03:00', 'yyyy-mm-dd hh24:mi:ss');

UNDO_SQL
--------------------------------------------------------------------------------
delete from "HR"."EMPLOYEES" where ROWID = 'AAAVTFAAFAAAADLAAK';
delete from "HR"."EMPLOYEES" where ROWID = 'AAAVTFAAFAAAADLAAJ';

Further Reading: Oracle TO_DATE Function Examples.

Then use these statements to undo INSERT INTO operations like the followings.

SQL> delete from "HR"."EMPLOYEES" where ROWID = 'AAAVTFAAFAAAADLAAK';

1 row deleted.

SQL> delete from "HR"."EMPLOYEES" where ROWID = 'AAAVTFAAFAAAADLAAJ';

1 row deleted.

Flashback Transaction Query is good at reverting committed transactions, but sometimes, you might get nothing from it because the transaction time is beyond UNDO_RETENTION. In such moment, you need another way to do it.

Regular Expressions

Here I used Notepad++ editor as the main tool to convert INSERT INTO Statements into DELETE FROM. In the Replace Dialog, we should do it like this:

Notepad++ Replace Dialog for INSERT INTO
Notepad++ Replace Dialog for INSERT INTO

In Find what: ^INSERT INTO ([^,]+)\ \(([^,]+),\ ([^,]+),\ ([^,]+),\ ([^,]+),\ ([^,]+)\)\ VALUES\ \(([^,]+),\ ([^,]+),\ ([^,]+),\ ([^,]+),\ ([^,]+)\);$

In Replace with: DELETE FROM $1 WHERE $2=$7 AND $3=$8 AND $4=$9 AND $5=$10 AND $6=$11;
with Regular expression mode. Then click Replace All.

In the expressions, we use an inverted pattern against dots, ([^,]+) to match table names, column names and values.

The original INSERT INTO statements:

INSERT INTO HR.EMPLOYEES (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID) VALUES (207, 'Rowe', '[email protected]', '25-APR-19', 'SA_REP');
INSERT INTO HR.EMPLOYEES (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID) VALUES (208, 'Smit', '[email protected]', '25-APR-19', 'FI_MGR');

Turn into DELETE FROM like this:

DELETE FROM HR.EMPLOYEES WHERE EMPLOYEE_ID=207 AND LAST_NAME='Rowe' AND EMAIL='[email protected]' AND HIRE_DATE='25-APR-19' AND JOB_ID='SA_REP';
DELETE FROM HR.EMPLOYEES WHERE EMPLOYEE_ID=208 AND LAST_NAME='Smit' AND EMAIL='[email protected]' AND HIRE_DATE='25-APR-19' AND JOB_ID='FI_MGR';

Now we can use the statements to delete rows, just don't forget to commit your result.

Please note that, the above regular expressions of replace aim for 5 column-value pairs. If you have different number of column-value pairs, you can modify the repeated components by yourself.

Leave a Reply

Your email address will not be published. Required fields are marked *