Is it necessary to run the COMMIT command after a DML operation in SQL Developer? For example, I performed the following UPDATE query:
UPDATE TAB1
SET TBX_TYP='ZX'
WHERE TBX_TYP IN(SELECT TBX_TYP
FROM(
SELECT DISTINCT TBX_TYP
FROM TAB1
ORDER BY 1 )
WHERE ROWNUM=1);
Then when I tried filtering columns, I found that nothing was updated.
The COMMIT instruction is necessary if you want you changes will be available for other users/connections, for example:
Session1:
SQL> conn hr/hr
Connected.
SQL> truncate table ttt;
Table truncated.
SQL> desc ttt;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NOT NULL VARCHAR2(20 CHAR)
SQL> insert into ttt values('one');
1 row created.
SQL> select col1 from ttt;
COL1
--------------------
one
So new data is available in current session.
Session2:
SQL> conn hr/hr
Connected.
SQL> select col1 from ttt;
no rows selected
But another session can't see this data. So let's commit it:
Session1:
SQL> commit;
Commit complete.
Session2:
SQL> /
COL1
--------------------
one
Now this value is available for both sessions.
But commit also is necessary to store data in you data files.
For example let's add a new row to the ttt table but don't commit it:
Session1:
SQL> insert into ttt values('two');
1 row created.
SQL> select col1 from ttt;
COL1
--------------------
one
two
Then let's shutdown the database abnormally and the start it again
Session2:
SQL> conn / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260048 bytes
Variable Size 616563632 bytes
Database Buffers 444596224 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.
SQL>
Then reconnect Session1 and look at the ttt table:
SQL> conn hr/hr
Connected.
SQL> select col1 from ttt;
COL1
--------------------
one
As you can see, the database doesn't store uncommited data in its data files.
COMMIT more often than you really need. The database has to do some complicated work to merge your changes into the rest of the database, and using COMMIT; too often wastes resources. You need to, as a programmer, decide what your "unit of work" is (for example, it might be 3 insert queries, 2 delete queries, then an update, then a commit). Use commit each time your "unit of meaningful work" is completed.Add Commit after every DML(update, delete, insert) command.
whereclause does it return any rows?