如何回滚我的数据库更改提交后？ [英] How to rollback my DB changes after a commit?

问题描述

我做了一个数据库更新，不使用where子句和提交而不进行任何备份。将更新表中的所有行。是否有任何方法来回滚更改？

DB是Oracle SQL。请帮助。

解决方案

您可以使用的 Flashback 功能。

1.Flashback由SCN

  SELECT column_list中
 FROM table_name的
 AS OF SCN scn_number; 
  

2. TIMESTAMP回滚

  SELECT column_list 
 FROM table_name 
 AS OF TIMESTAMP TO_TIMESTAMP（'the timestamp value'）; 
  

要获取current_scn和systimestamp，请查询：

  SELECT current_scn，SYSTIMESTAMP 
 FROM v $ database; 
  

让我们看一个例子：

将表回闪到旧的scn，使用 FLASHBACK TABLE..TO SCN 子句。

  SQL> DROP TABLE string_ex PURGE; 
 
表丢失。 
 
 SQL> CREATE TABLE string_ex（sl_ps_code VARCHAR2（20））; 
 
创建表。 
 
 SQL> INSERT INTO string_ex（sl_ps_code）VALUES（'AR14ASM0002'）; 
 
创建1行。 
 
 SQL> INSERT INTO string_ex（sl_ps_code）VALUES（'AR14SFT0018'）; 
 
创建1行。 
 
 SQL> INSERT INTO string_ex（sl_ps_code）VALUES（'AR14SFT0019'）; 
 
创建1行。 
 
 SQL> INSERT INTO string_ex（sl_ps_code）VALUES（'AR14SFT0062'）; 
 
创建1行。 
 
 SQL>承诺; 
 
提交完成。 
 
 SQL> SELECT current_scn，SYSTIMESTAMP FROM v $ database; 
 
 CURRENT_SCN SYSTIMESTAMP 
 -------------------- ---------------- ---------------------------- 
 13818123201277 29-OCT-14 03.02.17.419000 PM +05：30 
 
 SQL> SELECT current_scn，SYSTIMESTAMP FROM v $ database; 
 
 CURRENT_SCN SYSTIMESTAMP 
 -------------------- ---------------- ---------------------------- 
 13818123201280 29-OCT-14 03.02.22.785000 PM +05：30 
 
 SQL> SELECT current_scn，SYSTIMESTAMP FROM v $ database; 
 
 CURRENT_SCN SYSTIMESTAMP 
 -------------------- ---------------- ---------------------------- 
 13818123201282 29-OCT-14 03.02.26.781000 PM +05：30 
 
 SQL> SELECT * FROM string_ex; 
 
 SL_PS_CODE 
 --------------- 
 AR14ASM0002 
 AR14SFT0018 
 AR14SFT0019 
 AR14SFT0062 
 
 SQL> 
  

我在表格中有四行。

  SQL> ALTER TABLE string_ex ENABLE ROW运动; 
 
表改变。 
 
 SQL> 
   

需要行移动。

  SQL> DELETE FROM string_ex WHERE ROWNUM = 1; 
 
删除1行。 
 
 SQL> 
 SQL>承诺; 
 
提交完成。 
 
 SQL> 
 SQL> SELECT * FROM string_ex; 
 
 SL_PS_CODE 
 ----------____- 
 AR14SFT0018 
 AR14SFT0019 
 AR14SFT0062 
  

我现在删除了一行，并提交更改。

  SQL> FLASHBACK TABLE string_ex TO SCN 13818123201277; 
 
闪回完成。 
   

闪回完成

$ b b

  SQL> SELECT * FROM string_ex; 
 
 SL_PS_CODE 
 --------------- 
 AR14ASM0002 
 AR14SFT0018 
 AR14SFT0019 
 AR14SFT0062 
 
 SQL> 
  

现在我的表已恢复到旧状态，行已恢复 / p>

I did a DB update without using a where clause and commit without taking any backup. All the rows in the table are updated. Is there any way to rollback the changes?

The DB is a Oracle SQL one. Please help.

解决方案

You could do it using Flashback feature.

1.Flashback by SCN

SELECT column_list
FROM table_name
AS OF SCN scn_number;

2.Flashback by TIMESTAMP

SELECT column_list
FROM table_name
AS OF TIMESTAMP TO_TIMESTAMP('the timestamp value');

To get current_scn and systimestamp, query :

SELECT current_scn, SYSTIMESTAMP
FROM v$database;

Let's see an example :

To flashback the table to the old scn, use FLASHBACK TABLE..TO SCN clause.

SQL> DROP TABLE string_ex PURGE;

Table dropped.

SQL> CREATE TABLE string_ex (sl_ps_code VARCHAR2(20) );

Table created.

SQL> INSERT INTO string_ex (sl_ps_code) VALUES ('AR14ASM0002');

1 row created.

SQL> INSERT INTO string_ex (sl_ps_code) VALUES ('AR14SFT0018');

1 row created.

SQL> INSERT INTO string_ex (sl_ps_code) VALUES ('AR14SFT0019');

1 row created.

SQL> INSERT INTO string_ex (sl_ps_code) VALUES ('AR14SFT0062');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database;

         CURRENT_SCN SYSTIMESTAMP
-------------------- --------------------------------------------
      13818123201277 29-OCT-14 03.02.17.419000 PM +05:30

SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database;

         CURRENT_SCN SYSTIMESTAMP
-------------------- --------------------------------------------
      13818123201280 29-OCT-14 03.02.22.785000 PM +05:30

SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database;

         CURRENT_SCN SYSTIMESTAMP
-------------------- --------------------------------------------
      13818123201282 29-OCT-14 03.02.26.781000 PM +05:30

SQL> SELECT * FROM string_ex;

SL_PS_CODE
---------------
AR14ASM0002
AR14SFT0018
AR14SFT0019
AR14SFT0062

SQL>

I have four rows in the table.

SQL> ALTER TABLE string_ex ENABLE ROW MOVEMENT;

Table altered.

SQL>

Row movement is required.

SQL> DELETE FROM string_ex WHERE ROWNUM =1;

1 row deleted.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT * FROM string_ex;

SL_PS_CODE
---------------
AR14SFT0018
AR14SFT0019
AR14SFT0062

I deleted a row now and committed the changes.

SQL> FLASHBACK TABLE string_ex TO SCN 13818123201277;

Flashback complete.

Flashback is complete

SQL> SELECT * FROM string_ex;

SL_PS_CODE
---------------
AR14ASM0002
AR14SFT0018
AR14SFT0019
AR14SFT0062

SQL>

I now have my table to old state and the row is back

这篇关于如何回滚我的数据库更改提交后？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！