drop table sys_log;

create table sys_log 
( table_name  varchar2(50) constraint tn_pk primary key,
  status	  varchar2(10),
  start_time  date,
  end_time    date,
  current_table    varchar2(1) default 'N'
);

create or replace package pkg_sys_log as
	procedure set_status( p_table_name sys_log.table_name%type );
end;
/

create or replace package body pkg_sys_log as
	procedure set_status( p_table_name sys_log.table_name%type ) is
		      pragma autonomous_transaction;
		
		      cursor c_sys_log( p_table sys_log.table_name%type ) is
		      select table_name, current_table
		      from sys_log
		      where table_name = p_table;
  
		      r_sys_log c_sys_log%rowtype;
        l_current_table sys_log.current_table%type;
	begin	    
		open  c_sys_log( p_table_name );
		fetch c_sys_log into r_sys_log;
				
		if c_sys_log%found then        			                      
       begin
           select table_name
           into l_current_table
           from sys_log
           where current_table = 'Y';                   
       exception
           when no_data_found then
              l_current_table := null;       
       end;
       
       if l_current_table is not null and 
          r_sys_log.table_name != l_current_table then
          update sys_log
          set current_table = 'N',
              status = 'FINISHED'
              end_time = sysdate
          where table_name = l_current_table;
          
          update sys_log
          set current_table = 'Y'
          where table_name = r_sys_log.table_name;
       end if;                   
		else
   				insert into sys_log( table_name, status, start_time, current_table )
   				values ( p_table_name, 'ON-GOING', sysdate, 'Y' );
		end if;
		
		close c_sys_log;
		commit;
	exception
		when others then
			rollback;
			raise;
	end;
end;
/

drop table test;

create table test
as
select * from all_objects
where 1=2;

create or replace trigger bi_test_trg 
before insert or update or delete on test
begin
	pkg_sys_log.set_status( 'TEST' );
end;
/

DECLARE
   resource_busy_nowait   EXCEPTION;
   PRAGMA EXCEPTION_INIT (resource_busy_nowait, -00054);
   FOUND   BOOLEAN := FALSE;
BEGIN
   FOR i IN  (SELECT ROWID, col1, col2 FROM my_table)
   LOOP
      BEGIN
         FOR j IN  (SELECT 1 FROM my_table  WHERE ROWID = i.ROWID FOR UPDATE NOWAIT)
         LOOP
            COMMIT;
         END LOOP;
      EXCEPTION
         WHEN resource_busy_nowait
         THEN
            FOUND := TRUE;
            DBMS_OUTPUT.put_line ('Locked record for :  '||rpad(i.col1, 20, ' ')
			                   ||' Seq # '||i.col2||' '||sqlerrm);
      END;
   END LOOP;

   IF NOT FOUND
   THEN
      DBMS_OUTPUT.put_line ('No records locked in MY_TABLE');
   END IF;
END;
/

loop
fetch bulk collect limit 1000...
insert into tableA...
insert into tableB

commit;
end loop;