How to retrieve only unlocked rows?

How to retrieve only those records from a table that are not locked by other users? One way of doing it is by writing a PL/SQL code based on locking error (-54). Is there any other way of viewing unlocked records in SQL only?


Sql*plus session 1:
A user locks records with update emp set sal = sal*1.2
where deptno = 40;

Sql*plus session 2:
Another user likes to view all unlocked records from
table emp and is not aware of what is locked.
What should he/she do?

SKIP LOCKED option of select for update will list unlocked records. This is an undocumented feature.
select empno, ename, job, sal
from emp
for update skip locked;

Tip taken from :