Home » SQL & PL/SQL » SQL & PL/SQL » Monitoring transactions (10g)
Monitoring transactions [message #411230] Thu, 02 July 2009 10:52 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
Just want to ask any work-around for this scenario. They have a proC programs being called from shell scripts thru cron, this proC scripts just loads/updates tables. Now they want to have like a log table to query if that proC code is already done modifying a table, problem is they can't touch the proC code. I have this initial code, but as I do it, I think it won't work completely like updating the status of the table to be last modified. Is there any other oracle technology I could use on this? Thanks.


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;
/

Re: Monitoring transactions [message #411232 is a reply to message #411230] Thu, 02 July 2009 11:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, but I might be misreading your requirements, but how would you ever tell that the pro*c program is done with a table?!
Re: Monitoring transactions [message #411233 is a reply to message #411230] Thu, 02 July 2009 11:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Depends on what information is needed when and why.

In general, I would probably only INSERT start/end times and end status in the log table if that is neede, and make all "runtime" information on what is going on "right now" available in v$session with DBMS_APPLICATION_INFO.
Re: Monitoring transactions [message #411234 is a reply to message #411232] Thu, 02 July 2009 11:26 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thank you for the replies. Actually, this is not my problem =) I was just trying to help out a friend. He told me that his boss told him they cant edit the proC for some reason.

@Frank - yep, that's also what I told them, looks like only basis is if it's already modifying a different table. that's why my approach was to monitor which table is being modified based on the trigger.

@ThomasG - If I understand it correctly that can be done with the process actually modifying the tables right? not the process monitoring it?

I just got interested as I might learn something new. Looking on *_tab_modifications and dbms_stats.flush_database_monitoring_info if this will help.
Re: Monitoring transactions [message #411236 is a reply to message #411234] Thu, 02 July 2009 11:32 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes.

You could basically create a trigger on the tables that are modified, that sets the CLIENT_INFO of the session to "Modifying Table XXXXX".

That way, if you query v$session from another session, you could see which table the session where PROGRAM is the ProC application is currently modifying.

That way you could see what it is doing "now" at least.

[Updated on: Thu, 02 July 2009 11:33]

Report message to a moderator

Re: Monitoring transactions [message #411244 is a reply to message #411236] Thu, 02 July 2009 13:02 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks. Now if I would to capture those client_info so that I would be able to log the tables being modified, is it possible to put some kind of "trigger" on v$session to notify that the client_info has changed? Or do I just need to create a loop on the monitoring session to check like every seconds?
Re: Monitoring transactions [message #411248 is a reply to message #411244] Thu, 02 July 2009 14:45 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Remember that if any connection pooling is used, then the last status you set for the session will remain. You can look at before and after triggers, but I think you only really want the status to change on commit (that comes some time later than the last trigger).

Do a google on DBMS_ALERT & DBMS_PIPE examples.

Of interest is a way to list locked records, but needs to be used with caution...
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;
/


From an old Quest pipilines article...
http://space.itpub.net/?uid-308563-action-viewspace-itemid-171965
Re: Monitoring transactions [message #411307 is a reply to message #411230] Fri, 03 July 2009 01:22 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Oracle fires DDL triggers on each object registered within SQL dictionary after it's internal parser had checked syntax and compiled the code.How about using this!!
Re: Monitoring transactions [message #411437 is a reply to message #411230] Fri, 03 July 2009 10:24 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> They have a proC programs being called from shell scripts thru cron
KISS
Modify the script such that immediately after call to Pro*C program an INSERT into LOG_TABLE is done.
Re: Monitoring transactions [message #411802 is a reply to message #411248] Mon, 06 July 2009 19:57 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks for the replies.

@andrew - If I do create a before trigger on those tables, then send a signal thru dbms_alert, saying 'inserting on this table', then on after trigger sending again a signal of 'done inserting'. I'm just thinking how about this case:

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

commit;
end loop;

Then with those 'complete insert' signal be wrong for the moment of "commit", the other user would see that the loading for that table is complete, but in fact it would be again set to 'on-going' once the next set of records on the next loop will be executed. Hope I've explained it clearly. Thanks again.
Re: Monitoring transactions [message #411804 is a reply to message #411230] Mon, 06 July 2009 20:03 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
loop
fetch bulk collect limit 1000...
insert into tableA...
insert into tableB

commit;
end loop;


COMMIT inside loop leaves you vulnerable to ORA-01555; snapshot too old, error
Re: Monitoring transactions [message #411805 is a reply to message #411804] Mon, 06 July 2009 20:20 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Yes, but they can't do anything about it. They can't modify the proC scripts.
Re: Monitoring transactions [message #411807 is a reply to message #411230] Mon, 06 July 2009 20:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> They can't modify the proC scripts.
They really can change ProC code but choose to let others solve their problem for them.

Nothing is impossible for the person who does not have to actually do it!
Re: Monitoring transactions [message #411808 is a reply to message #411807] Mon, 06 July 2009 20:36 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Quote:
They really can change ProC code but choose to let others solve their problem for them.


Exactly!
Previous Topic: Circled processing
Next Topic: Better way of writing SQL ( with same output ofcourse)
Goto Forum:
  


Current Time: Sat Dec 10 10:55:46 CST 2016

Total time taken to generate the page: 0.09708 seconds