TCC Commands
1. COMMIT Command
The COMMIT Statement is used when we made some changes in the current transaction and want them to be permanent. In other words, to apply our changes permanently we use the COMMIT clause. Until the changes are committed we cannot access the modified data.
Syntax
COMMIT [WORK] [COMMENT 'your comment']
Or
INSERT INTO table_name VALUES (Val1,Val2,.....Valn);
COMMIT;
Example
Assume the following Employee table:
|
Emp_id |
Emp_Name |
City |
|
110011 |
ASD |
Delhi |
|
110022 |
GHJ |
Jodhpur |
|
110033 |
RST |
Patiala |
|
110055 |
ABC |
Haridwar |
INSERT INTO Employees VALUES (11044, 'Rahul','Jaipur')
COMMIT;
Result
|
Emp_id |
Emp_Name |
City |
|
110011 |
ASD |
Delhi |
|
110022 |
GHJ |
Jodhpur |
|
110033 |
RST |
Patiala |
|
110044 |
Rahul |
Jaipur |
|
110055 |
ABC |
Haridwar |
2. ROLLBACK Command
In Oracle, in a current transaction, if we made the changes and also commited them, then ROLLBACK is the only command that is used to undo the changes done by us. To undo/rollback your transaction, no privileges are necessary.
Syntax
ROLLBACK [WORK] TO <SAVEPOINT>] identifier;
Or
INSERT INTO table_name VALUES (Val1,Val2,.....Valn);
ROLLBACK;
Example
Assume the following Organization table:
|
Org_id |
Org_Name |
City |
|
123 |
TCS |
Delhi |
|
124 |
HCL |
Noida |
|
126 |
INFOSYS |
Mumbai |
|
128 |
WIPRO |
Gurgoan |
Query
INSERT INTO Organization VALUES (125, 'TECH. MAHINDRA','Noida');
INSERT INTO Organization VALUES (127, 'COMPRO PVT.LTD.','Delhi');
INSERT INTO Organization VALUES (129, 'NEIT','Mumbai');
COMMIT;
Result
|
Org_id |
Org_Name |
City |
|
123 |
TCS |
Delhi |
|
124 |
HCL |
Noida |
|
126 |
INFOSYS |
Mumbai |
|
128 |
WIPRO |
Gurgoan |
|
125 |
TECH. MAHINDRA |
Noida |
|
127 |
COMPRO PVT. LTD. |
Delhi |
|
129 |
NEIT |
Mumbai |
Note: Here we inserted 3 rows but we need to insert only 2 rows so now use rollback and the result is:
Query
INSERT INTO Organization VALUES (125, 'TECH. MAHINDRA','Noida');
INSERT INTO Organization VALUES (127, 'COMPRO PVT.LTD.','Delhi');
INSERT INTO Organization VALUES (129, 'NEIT','Mumbai');
COMMIT;
ROLLBACK;
Result
|
Org_id |
Org_Name |
City |
|
123 |
TCS |
Delhi |
|
124 |
HCL |
Noida |
|
126 |
INFOSYS |
Mumbai |
|
128 |
WIPRO |
Gurgoan |
3. SAVEPOINT Command
SAVEPOINT is used when we don't want to ROLLBACK all the changes during the current transaction, in other words we created savepoints during the changes made so that we can go to the specific SAVEPOINT to ROLLBACK the change.
Syntax
SAVEPOINT <Savepoint_Name>;
Example
Assume the following Order table:
|
Order_id |
Item_Name |
Quantity |
|
1 |
Shampoo |
12 |
|
2 |
Hair Oil |
8 |
|
5 |
Talc |
5 |
|
9 |
Deo Spray |
7 |
Query
INSERT INTO Order VALUES (7, Soap', 10);
SAVEPOINT a;
DELETE FROM Order VALUES (5, 'Talc',5);
SAVEPOINT b;
INSERT INTO Order VALUES (3, 'NEIT', 15);
COMMIT;
Result
|
Order_id |
Item_Name |
Quantity |
|
1 |
Shampoo |
12 |
|
2 |
Hair Oil |
8 |
|
9 |
Deo Spray |
7 |
|
7 |
Soap |
10 |
|
3 |
Detergent |
15 |
Note: Here, in the preceding query we inserted 2 rows and deleted 1 row from the table order and also created SAVEPOINTS, but we inserted two rows instead of one. So, to rollback the insertion of the second row we rollback the transaction using the savepoint with rollback in the following way:
Query
INSERT INTO Order VALUES (7, Soap', 10);
SAVEPOINT a;
DELETE FROM Order VALUES (5, 'Talc',5);
SAVEPOINT b;
INSERT INTO Order VALUES (3, 'NEIT', 15);
ROLLBACK TO SAVEPOINT b;
COMMIT;
Result
|
Order_id |
Item_Name |
Quantity |
|
1 |
Shampoo |
12 |
|
2 |
Hair Oil |
8 |
|
9 |
Deo Spray |
7 |
|
7 |
Soap |
10 |
4. SET TRANSACTION Command
As the name indicates, a SET TRANSACTION is a set of one or more transactions/statements that are executed together as a unit so that either all will be executed or none of them will be executed. It is used to establish the current transaction as read only or read write.
Syntax
SET TRANSACTION
{
{ READ { ONLY | WRITE }
| ISOLATION LEVEL
{ SERIALIZABLE | READ COMMITTED }
| USE ROLLBACK SEGMENT rollback_segment
}
[ NAME 'text' ]
| NAME 'text'
};
Since I have described the transaction levels in my article "Transactions in Oracle database", here I am sharing the Read only level in the following example.
Example
To set a transaction to read only you need to use the SET TRANSACTION READ ONLY Command. In DML the transactions will start automatically, thats why a SET TRANSACTION statement is issued before the DML data. Here the default scott schema is used:
SQL> connect HR/fyicenter
SQL> SET TRANSACTION READ ONLY;
Transaction set.
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
112 oracle.com 07-MAY-06
113 sql.com 07-MAY-06
Keep the "HR" SQL*Plus window as-is and open another window to run another instance of SQL*Plus.
>cd (OracleXE home directory)
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/password
Connected.
SQL> DELETE FROM hr.fyi_links where id = 112;
1 row deleted.
SQL> DELETE FROM hr.fyi_links where id = 113;
1 row deleted.
SQL> COMMIT;
Commit complete.
Go back to the "HR" SQL*Plus window.
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
112 oracle.com 07-MAY-06
113 sql.com 07-MAY-06
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 FYICENTER.COM 07-MAY-06
110 CENTERFYI.COM 07-MAY-06
As you can see, two records were deleted from another session after the HR session started the READ ONLY transaction. The deleted records was not impacting any query statements until the transaction was ended with the COMMIT statement.
You can also read this from here: http://dba.fyicenter.com/faq/oracle/Set-READ-ONLY-Transaction.html
Previous article: Oracle SQL Commands: Part 7
Next Article:
Oracle SQL Commands: Part 9