cancel
Showing results for 
Search instead for 
Did you mean: 

Lock the Table

Prabhu_7
Frequent Advisor

Lock the Table

i have "table A", whenever i insert records in
"table A" similar record will be inserted into "table B" (Insert trigger in table A)
One of our process does ,

Insert into table c
Select (some calculations) from table B

then ,

delete from table B

I'm not sure when table A will be loaded.
It can be loaded anytime (means table B too)..

But our process starts at 10AM everyday, so it fetches from table B arround 10AM, If during that time table B is getting loaded , then it takes only records loaded at that time. How to make my select statement to wait until all records get loaded into table B ?

Load process is done by someother group, so we cant
change their process.Its impossible.
Our process starts when we start pulling data from
"table B". I can alter the process from this point only.

Is that possible to check whether is there any lock in
table and then start fetching data.

For eg ,

If [ ANY LOCK IN TABLE B ] then
Wait [ DONT FETCH ]
Else
[ LOCK TABLE B , SO THAT NO NEW RECORDS ARE INSERTED ]
Select from "table B"
Delete from "table B"
End
2 REPLIES
James A. Donovan
Honored Contributor

Re: Lock the Table

simple....

insert table D
as select * from table B;

insert table C
as select * from table D;

delete from table B
where D.record = B.record;

delete * from table D;

repeat.....
Remember, wherever you go, there you are...
Brian Crabtree
Honored Contributor

Re: Lock the Table

This should be cleaned up quite a bit (if statements, etc) but this is a basic concept for it:
-------
declare
numcount number := '1';
begin
while numcount > 0 loop
-- xxxxx should be the object id for the
select count(*) into numcount from
v$locked_object where object_id = 'xxxxx';
dbms_lock.sleep('10');
end loop;
end;
/
--------

Hope this helps,

Brian