oracle sleep or lock to check the processing

Question

I just want to see whether the data is getting inserted on the table or not..

So i have written like this:

select count(*) from emp;
dbms_lock.sleep(1);
select count(*) from emp;

So that it will sleep for 1 min . Even after sleep if the 1st count and 2nd count are different then the data is getting inserted into the table.

Otherwise the insertions are not happening.

But i have a small doubt regarding this, whether this instance will hang for 1 sec or the whole Database will hangon for 1 sec.

And if it wrong how to implement this.

Answer 1

Only your PL/SQL-Block will be put to sleep. If you want to sleep for a minute, pass 60 (seconds) to sleep.

Answer 2

To be more clear, you don't know whether or not the insertions are not happening; all you know is that the count of committed records didn't change. If you have access to V$TRANSACTION, you can look at USED_UBLK and USED_UREC to verify that a transaction in flight is generating changes.

Answer 3

How are you inserting into the table? Is it batch? OLTP? I don't think what you proposed is pratical, but cannot suggest another way until more information is provided. Give us a little more information on the process.

Answer 4

You can use USER_LOCK.SLEEP if you don't want to grant EXECUTE to DBMS_LOCK which can be more destructive. The arguments are different however you can achieve the same thing.

USER_LOCK.SLEEP PROCEDURE SLEEP Argument Name Type In/Out Default?

---

TENS_OF_MILLISECS NUMBER IN

DBMS_LOCK.SLEEP PROCEDURE SLEEP Argument Name Type In/Out Default?

---

SECONDS NUMBER IN

Answer 5

First of all, sorry for my english :-D AFAIK select count(*) is wasting resource. I suggest you create a trigger to increment number of rows inserted somewhere else. Your scheduled job then check the number of inserted rows and reset it to zero prior to exiting. In this way you will know whether any rows inserted between runs and how many.

create table emp_stat(inserted int);

insert into emp_stat values(0);
commit;

create trigger emp_trigger 
before insert on emp for each row 
begin 
    if ( inserting ) then 
        update emp_stat set inserted = inserted + 1; 
    end if; 
end;