DML Statements - Oracle

DML operations:

insert into tablename(101,'anand',DEFAULT);
insert into tablename select * from tablename;

delete from t100 where doj=(select doj from t100 where name='JOHN');

MERGE command:

insert command can perform only insert and update command

MERGE into d
USING emp e ON (d.code=e.code)
WHEN MATCHED THEN
UPDATE SET
d.name=e.name
d.sal=e.sal
WHEN NOT MATCHED
INSERT VALUES(e.code,e.name,e.salary)

WITH CHECK OPTION:
* both values in select list and values list should be same. Here in below example we are talking about 1002.

INSERT INTO
(SELECT code,name,age,salary,manager_id FROM ex_emp_dup
WHERE manager_id=1002 WITH CHECK OPTION)
VALUES (1008,'DAS',30,3000,1002);


locking mechanism:
>DDL Lock (Automatically Acquring lock and Automatically releasing the locK - Human intervention is not required)

>DML Lock
1.ROW EXCLUSIVE (TX)
2.SHARED TABLE LOCK (TM)
TX:
Row level locking
TM:
Table level Locking

* when your are performing UPDATE operation both TX and TM is applied.

Examples on locking mechanism:

> select username,sid from v$session; //how many users are connected, v$session is a performance view, its a data dictionary view
> select sid,type,v$lock where type in('TX','TM');
> select sid,type,v$lock where type in('TX','TM') AND sid IN(43,55);

When we are performing DDL or DCL transactions there is an AUTOCOMMIT;

DDL&DCL operation cannot be ROLLBACK.
DCL operation, we can REVOKE.
When there is a abnormal termination, auto roll back happens.