EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Oracle ROLLBACK
Secondary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Oracle ROLLBACK

By Priya PedamkarPriya Pedamkar

Oracle ROLLBACK

Introduction to Oracle ROLLBACK

Oracle Rollback statement asks the Oracle database to rollback the entire transaction which means that it will undo or omit any work or changes that may be done by the current transaction (INSERT UPDATE OR DELETE) and the database will be brought to the previous state (which means the state of the database before the current transaction was created or started) and also it can be used to manually force any corrupt or in-doubt transaction to omit its changes and restore the database to the previous state.

Syntax

We will now discuss the syntax of the ROLLBACK statement in Oracle below. The syntax is simple

ROLLBACK [WORK ] [ TO savepoint| FORCE 'string' ];

Let us now get to know the various parameters used in the ROLLBACK statement.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Parameters used in the ROLLBACK Statement

Below are some parameters:

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,408 ratings)
  • Work: It is an optional parameter. The statement with or without WORK clause makes no difference in the output. It was just added by Oracle to be SQL compliant.
  • Savepoint: It is also optional parameter. If we use savepoint then the oracle omits all the changes done by the current session to the savepoint time provided by the clause. In case this clause is not provided then the database undoes all the changes done.
  • Force ‘string’: It is also an optional parameter. It is used to rollback or omits in-doubt and pending transaction. We need to specify the transaction id in string with this clause. The transaction id can be seen in system view DBA_2PC_PENDING for corrupt or in-doubt transactions. The points to note is that the user should have DBA privileges to access system views like DBA_2PC_PENDING.

How Does Oracle ROLLBACK work?

ROLLBACK in Oracle works as it asks the Oracle to rollback or omits the changes done by the current transaction if no savepoint clause is provided otherwise it rollbacks the changes done only till the savepoint. In other words it restores the database to the savepoint mentioned in the clause. Suppose there is insert and update statement in the current transaction and they are executed. If we do not need the changes made by the update statement then in that case we need to use ROLLBACK statement with a savepoint clause with it which would tell the database to omit the changes of the update statement and bring the database to the state which it was before UPDATE statement was executed but the changes made due to INSERT statement will be present. In case we do not use any savepoint and simply want to omit all the changes done by INSERT and DELETE statement then we simply use ROLLBACK statement without any clause and all the changes are omitted and database returns to the state which it was before the INSERT and UPDATE statement was executed.

Examples to Implement Oracle ROLLBACK

Below are the examples mentioned:

Example #1

ROLLBACK WITHOUT CLAUSE: In this case we are going to use ROLLBACK to just omit the changes done by current transaction. In this case we are going to insert a new row in the table EMPLOYEE and then we will use ROLLBACK statement to omit all the changes done by the insert statement. Let us look at the SQL statements for the same.

Code:

insert into employee values('Sunil','TATA','AD014','VH010','Kolkata','34','9878207095','AD008','65000');
ROLLBACK;

The first SQL statement inserts the values in the SQL statement into the employees table and then we will use the ROLLBACK statement to rollback the changes and make the database go to the state which was before the insert statement was applied.

Output:

Rollback without clause1

As we can see in the above screenshot that after the insert query was executed, the ROLLBACK was successfully completed.

Now let us run the select query and check if the values are present in the Employee table.

Rollback without clause2

As we can see that the Employee table does not have the values which we had inserted using the INSERT statement. Hence ROLLBACK statement successfully restores the database to its previous state before the current transaction.

Example #2

ROLLBACK WITH SAVEPOINT: In this case, we will not ROLLBACK the entire transaction but we will only rollback to a certain savepoint which we will declare in between the transaction using the SAVEPOINT statement. So, in this example we will first insert values in the wrong columns of the employee table using the INSERT statement and then create the insert statement as a SAVEPOINT and then update the wrong inserted values using the UPDATE statement. Let us look at the query.

Code:

insert into employee values('Sunil','TATA','AD014','VH010','Kolkata','34','9878207095','AD008','65000');
savepoint  trans_1;

In the above query as we can see there are two SQL statements one is the insert statement and the next one sets the savepoint.

Output:

Insert statement

As, we can see in the screenshot that the insert statement has been successfully executed and the savepoint has also being created.

Now let us run the SELECT statement to see the EMPLOYEE table.

Select * from employee;

Oracle ROLLBACK4

If we see the row number 13 in the screenshot then we can see that the row has been inserted.

Let us now run the UPDATE statement to UPDATE the contents and then the ROLLBACK statement to bring the database to the savepoint trans_1.

UPDATE EMPLOYEE SET NAME ='SUNIL' WHERE VEHICLE_ID ='VH010';
ROLLBACK TO SAVEPOINT trans_1;

In this query, we are updating the name and then using ROLLBACK with the SAVEPOINT clause.

Let us run the query in SQL developer and check the result.

Oracle ROLLBACK5

As we can see the ROLLBACK to SAVEPOINT has been completed successfully. Now let us run the SELECT statement to check the EMPLOYEE table.

Employee Table

As we can see the content of the EMPLOYEE table is same as it was in SAVEPOINT trans_1.

Conclusion

In this article, we discussed about the definition of ROLLBACK statement and the syntax. Later on in the article the working of ROLLBACK in Oracle and the various cases we used ROLLBACK along with appropriate examples were discussed for better understanding of the concept.

Recommended Articles

This is a guide to Oracle ROLLBACK. Here we discuss an introduction to Oracle ROLLBACK, syntax, parameters, how does it work, examples with code and output. You can also go through our other related articles to learn more –

  1. Oracle vs SQL Server
  2. Career in Oracle
  3. Oracle vs MSSQL
  4. Oracle Window Functions
  5. How to Works Oracle While Loop?
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more