oracle 提交后如何回滚我的数据库更改？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27309730/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to rollback my DB changes after a commit?
提问by Niketh Kovin
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?
我在不使用 where 子句的情况下进行了数据库更新，并在不进行任何备份的情况下进行了提交。表中的所有行都被更新。有什么办法可以回滚更改吗？
The DB is a Oracle SQL one. Please help.
DB 是一个 Oracle SQL 数据库。请帮忙。
回答by Lalit Kumar B
You could do it using Flashbackfeature.
您可以使用闪回功能来做到这一点。
1.Flashback by SCN
1.SCN闪回
SELECT column_list
FROM table_name
AS OF SCN scn_number;
2.Flashback by TIMESTAMP
2.TIMESTAMP闪回
SELECT column_list
FROM table_name
AS OF TIMESTAMP TO_TIMESTAMP('the timestamp value');
To get current_scn and systimestamp, query :
要获取 current_scn 和 systimestamp，请查询：
SELECT current_scn, SYSTIMESTAMP
FROM v$database;
Let's see an example :
让我们看一个例子：
To flashback the table to the old scn, use FLASHBACK TABLE..TO SCNclause.
要将表闪回到旧的 scn，请使用FLASHBACK TABLE..TO SCN子句。
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
我现在有我的表到旧状态，行又回来了
