Thursday, 21 August 2014

How To Add Auto Increment In Oracle- IDENTITY column in Oracle 12c

Lets create employee table for this purpose.
SQL> CREATE TABLE EMP01
(
EMPID NUMBER,
NAME VARCHAR2(50),
DEPT VARCHAR2(20)
);

Next steps is to create oracle sequence to generated the id values.
SQL> CREATE SEQUENCE EMPID_SEQUENCE
START WITH 1
INCREMENT BY 1;

Next Step is to create Trigger to assign the values from sequence to EMPID column.

CREATE OR REPLACE TRIGGER EMPID_TRIGGER
BEFORE INSERT ON EMP01
FOR EACH ROW
BEGIN
SELECT EMPID_SEQUENCE.nextval INTO :NEW. EMPID  FROM dual;
END;
/
Now we will try insertng few values:

SQL> INSERT INTO EMP01 (NAME, DEPT) VALUES ('RON',’ABC’);
1 row created.
SQL> INSERT INTO EMP01 (NAME, DEPT) VALUES ('VICTORIA',’XYZ’);
SQL> SELECT * FROM EMP01;
EMPID NAME DEPT
---------- ------------------------------
1 RON ABC
2 VICTORIA XYZ


You can find that the EMPID getting incremented by 1.
Now there is a new feature available in on Oracle 12c version:IDENTITY column using which we can implement the same auto increment feature.

Tuesday, 15 July 2014

How to Unlock the Locked Table in ORACLE


Oracle puts locks while performing any DDL or DML operation on oracle tables.When table locks is present on any tables in Oracle we cannot run DDL on those tables.

Some of the locks automatically set by oracle are RS and RX Locks.
SELECT … FOR UPDATE execution results in RS (row share) table lock. When you execute an INSERT, UPDATE or DELETE Oracle puts RX (row exclusive) table lock.

We have to kill the session which holds the lock in order to execute further operations. Follow the below steps to kill the session and forcibly unlock the table.

Let’s assume that 'EMP' table is locked,
Related Posts Plugin for WordPress, Blogger...

ShareThis