Oracle 中 ROLLBACK 命令的使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16632243/
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
use of ROLLBACK command in Oracle
提问by bibhudash
I created table
我创建了表
SQL>CREATE TABLE Student
(
StudID NUMBER(6),
StudName VARCHAR2(25),
JoinDate DATE
);
Table created.
SQL>INSERT INTO Student
VALUES (123,'JOHN',SYSDATE);
1 row created.
SQL>DELETE Student;
1 row deleted.
How can I get back the row ? If I use
我怎样才能回到这一行？如果我使用
SQL>ROLLBACK;
Rollback complete.
But after that
但在那之后
SQL>SELECT * FROM Student;
no rows selected.
Why is this coming?
为什么会这样？
回答by Justin Cave
ROLLBACKtells Oracle to roll back the entire transaction. In your case, both the INSERTand the DELETEare part of the same transaction so the ROLLBACKreverses both operations. That returns the database to the state it was in immediately following the CREATE TABLEstatement.
ROLLBACK告诉 Oracle 回滚整个事务。在您的情况下， theINSERT和 theDELETE都是同一事务的一部分，因此这ROLLBACK两个操作都相反。这会将数据库返回到紧跟在该CREATE TABLE语句之后的状态。
Some alternatives:
一些替代方案：
- If you were to issue a
COMMITafter theINSERTthen theDELETEstatement would be in a separate transaction and theROLLBACKwould reverse only the effect of theDELETEstatement. - You could also create a savepoint after running the
INSERTstatement and then rollback to that savepoint after theDELETErather than rolling back the entire transaction.
- 如果您在
COMMIT之后发出 aINSERT则该DELETE语句将在一个单独的事务中并且该语句将ROLLBACK仅逆转该DELETE语句的效果。 - 您还可以在运行
INSERT语句后创建一个保存点，然后在执行完DELETE而不是回滚整个事务后回滚到该保存点。
回答by Witold Kaczurba
This might be a bit confusing if you are not doing rollbacks on a regular basis. I put here mistakes that you are likely to make if not using SAVEPOINTS / ROLLBACK on a regular basis.
如果您不定期进行回滚，这可能会有点令人困惑。如果不定期使用 SAVEPOINTS / ROLLBACK，我在这里列出了您可能会犯的错误。
1. This is your case. We rollback all transaction to the beginning, ie. to the last (implicit) commit
1. 这是你的情况。我们将所有事务回滚到开头，即。到最后一次（隐式）提交
2. Here we have SAVEPOINT but it does not matter, as it is not mentioned in ROLLBACK statment. So we are rolling back to the last commit, as before.
2. 这里我们有 SAVEPOINT 但没关系，因为它在 ROLLBACK 语句中没有提到。所以我们像以前一样回滚到最后一次提交。
3. This is tricky. The rollback still goes to the last commit, as the syntax was not correct (but no error reported by the 11g database). Correct syntax is ROLLBACK TO <SAVEPOINT-NAME>:
3. 这很棘手。回滚仍然到最后一次提交，因为语法不正确（但 11g 数据库没有报告错误）。正确的语法是 ROLLBACK TO <SAVEPOINT-NAME>：
4. Finally we have correct SYNTAX: ROLLBACK TO <SAVEPOINT-NAME>
4. 最后我们有了正确的语法：ROLLBACK TO <SAVEPOINT-NAME>
Some readings: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9021.htm
一些阅读：https: //docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9021.htm
回答by Guillermo Nahuel Varelli
Try
尝试
CREATE TABLE your_table
AS SELECT * FROM employees;
ALTER TABLE your_table
ENABLE ROW MOVEMENT;
UPDATE your_table
SET CON_TYPE = 'N';
FLASHBACK TABLE your_table
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);
/*if you want to restore the table*/
FLASHBACK TABLE print_media TO BEFORE DROP;
/*Restore the table with other name*/
FLASHBACK TABLE your_table TO BEFORE DROP RENAME TO your_table;
