How to Lock Table in Oracle

While working on a requirement, we have to raise the ReadTimeOut condition from one environment to another environment. We have followed the Table-Level lock operation in Oracle and hence achieved the functionality accordingly. Following is the brief information on the same:-

PL/SQL Developer Session-1:-

-- Creating Data-Set
create table emp (emp_id number, emp_name varchar2(20));
insert into emp values (1,'vipin’);
insert into emp values (2,'vinay’);
select * from emp;
-- Obtaining Table-Level Lock
lock table emp in exclusive mode nowait;
rollback; -- A locked table remains locked until we either commit our transaction or roll it back, either entirely or to a savepoint before you locked the table.

PL/SQL Developer Session-2:-

---- Verifying and Creating the Data-Set
select * from emp;
insert into emp values (3,'ananya'); -- insert statement will not get completed until or unless the lock is not released from another session.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s