lock and get next n records from table in oracle I have a oracle table PHONE_NUMBER which has 2 columns PHONE and STATUS
PHONE_NUMBER 
PHONE STATUS
1     U
2     O
3     U

U stands for used , O is open/UnUSED .
in my input I will get n as input number , which signifies the number of phones I want to reserve(update status to U from O )
I want a query that will lock(something like SELECT FOR UPDATE) the any n available row (rows that are not locked yet and having status as O )
Constraints: Table has millions of Used and unsed phones
comments:
problem is i Want n (input) rows to be locked with a particular status . i.e i have to work with rownum
I will be getting multiple concurrent calls in my service with sqli_n as input number of records to select :
so
SELECT * FROM PHONE_NUMBER WHERE STATUS='O' and rownum&lt;=:sqli_n FOR UPDATE ;

this query wont work for me it will select only first n rows and if the first n rows are locked in 1 call the next/concurrent call will be stuck /wait until the first call updates the status to Used .
for update no wait will fail the above query
for update skip lock will also not work with row num
So, you have discovered that you can't do what you want; so you may want to do what you can by redesigning your process. One approach might be to introduce a new column, e.g. session_id, to indicate what application session, if any, is processing the row.
Each session will then start by atomically reserving a set of rows:
UPDATE phone_number SET session_id='&lt;whatever&gt;' 
WHERE status='O' AND session_id IS NULL AND ROWNUM&lt;=:sqli_n;
COMMIT;

It will then do whatever it needs to do and finally release the reserved rows:
UPDATE phone_number SET session_id=NULL 
WHERE session_id='&lt;whatever&gt;';
COMMIT;

You'll likely need to revise indexes on this table.