Thanks for the question, Ajeet.

Asked: October 12, 2004 - 9:42 am UTC

Last updated: April 26, 2005 - 4:35 pm UTC

Version: 9.2.0.5

Viewed 1000+ times

You Asked

I have a table

SQL> desc rdo.rdo_sea_alert_ref ;
Name Null? Type
----------------------------------------- -------- -----------------
FLEET VARCHAR2(10)
AIRCRAFT_ID VARCHAR2(10)
ENGINE_ID VARCHAR2(8)
FLIGHT_PHASE VARCHAR2(16)
INSTALL_DATETIME DATE
FLIGHT_DATETIME DATE
SAGE_CREATION_DATE DATE
CREATION_DATE DATE

Now I need to run a Procedure on this table which should take data of last 1 minute (based on the field sage_creation_date which store data upto seconds level -- that is mm/dd/yyyy hh24:mi:ss) and do some processing on it --
then finally insert the results into another table. (I have completed this part).

Then this procedure should run at every 1 minute using dbms_job.

Followings are my questiones or things which I am not sure how to do :

1)How to ensure that I am not missing any data -- currently I store the run time of procedure in a table.and at the end
I update this as set last_run_time = last_run_time+1/1440..is it is the correct way to do it.

And In my procedure i use a where clause

sage_creation_date > last_run_time ; --will this ensures that I am taking data of last 1 minute ( ?).

2)How to ensure that using dbms_job if my job (i.e procedure) is not completed within 1 minute it should wait for it to complete..rather than start it again exactly after 1 minute. (I am not sure how to do it in a clean way).

Please suggest a correct way.

Thanks
Ajeet

and Tom said...

uh oh.

I see multi-versioning confusion in your eyes.
You need to read this:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:16998677475837 <code>

from start to finish.

before you start your "refresh", you need to save this time:

select nvl( min(to_date(start_time,'mm/dd/rr hh24:mi:ss')),sysdate)
from v$transaction;

You must also be willing and ready and able to process a record MORE THAN ONCE (it happens -- you cannot avoid it, make sure you can handle it -- else you have to mark the records "processed" as you process them)

As for the 1 minute thing -- dbms_job is never going to be that precise. But -- dbms_job will never run an already running job either (so you will not have 2, 3, 4 or more copies of your stored procedure running).

"Exactly 1 minute" will not be achievable using any scheduler in the database. If you want "exactly one minute", you will write a stored procedure that never ends

begin
loop
call_process;
commit;
dbms_lock.sleep(60);
end loop;
end;
/

and run that in the background.

dbms_job will be "about 1 minute or a little longer -- as long as the job queues are not full running other jobs already in which case it could be lots longer than one minute"

Now, if you ask me -- someone should be putting these records as they are inserted INTO A QUEUE for processing. Your job would run upon receipt of a queued message (instead of polling over and over and over and over and over) and process the newly queued message(s).

Rating

(19 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How to get correct values from v$transaction.

Ajeet, October 20, 2004 - 5:32 am UTC

Tom,
After reading your response I coded my procedure like this.

create or replace procedure rdo_gen_alert_prc2 is
--
n1 number(9):= 0 ;
n2 number(9):= 0 ;
n3 number(9):= 0 ;
n4 number(9):= 0 ;
l_run_id number(9) := 0 ;
l_date date ;
begin
n1 := dbms_utility.get_time ;
--
begin
--Here i am trying to store last run time...using --v$transaction.
insert into rdo_sea2
select nvl( min(to_date(start_time,'mm/dd/rr hh24:mi:ss')),sysdate)
from v$transaction;
commit ;
--
--change in code as JOSP will give alerting value now...10/15.
insert into t3 --shd be changed
select b.alert_rules_seq_id,'N',b.fleet,
JOSPs.detStatsJOSP(cursor(select parameter_value
from ( select fleet,dim_seq_id,param_seq_id,flight_datetime ,parameter_value,
dense_rank() over ( partition by fleet,dim_seq_id,param_seq_id order by flight_datetime desc) rank
from rdo.rdo_param_v )
where rank <= b.persistence_y
and fleet = b.fleet and param_seq_id = b.parameter_seq_id and dim_seq_id = b.dim_seq_id ) ,
b.persistence_x ,b.persistence_y ,b.test_name,
b.statistical_method_name, b.upper_threshold,b.lower_threshold,
b.sigma_limit, b.above_or_below,nvl(b.min_shift_value,0), nvl(b.center_line_num,0),
b.std_dev ) alerting_value ,
b.aircraft_id,
b.upper_control_limit,
b.engine_id ,
nvl(b.center_line_num,0),b.parameter_seq_id,b.flight_datetime,
b.lower_control_limit,sysdate,b.flight_phase,sysdate,b.install_datetime,null,null,null,
'N','N',sysdate,sysdate,null,-99
from
rdo.rdo_sea_alert_v2 b where b.sage_creation_date > (select max(Last_run_time) from
rdo_sea2)
;
commit ;
--
exception
when others then
null ;
--dbms_output.put_line('Error in insert-'||sqlcode) ;
end ;
n2 := dbms_utility.get_time ;
select run_seq_id.nextval into l_run_id from dual ;
insert into rdo_sea_perf_data values(l_run_id,'Josp-result insert complete in...mseconds' ,n2-n1 ,sysdate,'josp') ;
commit ;
--dbms_output.put_line('at the end'||l_date) ;
exception
when others then
dbms_output.put_line(sqlcode) ;
end ;
/
(please ignore poorly handeled exceptions as i am writing a quick test case --will handle them appropriately).
Here I am trying to save values from v$transaction in a table and then I use that value in next run..

My problem is i do'nt get the correct start_time from v$transaction..

On the other link you provided a query which joins the v$transaction with v$lock -- that does seems to give me correct time but that query takes almost 4 minutes to run and I have complete all these things (that is execute the procedure in around 1 minute).

I am not very well aware with v$transaction --so please let me know what i am doing wrong.

The query which you gave at other link is

select nvl(min(start_time),to_char(sysdate,'mm/dd/yy
hh24:mi:ss'))
from v$transaction
where addr in
(select taddr
from v$session
where sid in
(select sid
from v$lock
where type = 'TM'
and id1 in
(select object_id
from dba_objects
where object_name in ('T3' )
and owner = 'OPS$RDETL')
)
)
/
Thanks
Ajeet

October 20, 2004 - 7:27 am UTC

Your logic has to be:

o read out the LAST SAVED TIME into "x"
o save the new last saved time into rdo_sea2
o use X in your refresh query.

you are using the "new last saved time", that would never work.

How to run PL/SQL procedure in the background

Shyam Prabhudesai, October 20, 2004 - 8:27 am UTC

Tom,

In your last followup you had mentioned running PL/SQL block in background .
begin
loop
call_process;
commit;
dbms_lock.sleep(60);
end loop;
end;
/

Can you plz elaborate on this ?

Thanx in anticipation

October 20, 2004 - 11:20 am UTC

search for

dbms_job

I get an old date from v$transaction

Ajeet, October 20, 2004 - 10:01 am UTC

Tom:
I changed the procedure the way you suggested.
Every time when I run this procedure I insert a new row in my table rdo_sea_run --which has just one column -- 
Last_run_time...and at the end I was trying to get value from v$transaction and insert it into rdo_sea_run table as row..I was hoping that I will get this record as the latest one and which I will use in my next run to get the data --but it returns a 2 days old date -- 

Here is the way I changed the proc --

create or replace procedure rdo_gen_alert_prc3 is
--
n1  number(9):= 0 ;
n2  number(9):= 0 ;
n3  number(9):= 0 ;
n4  number(9):= 0 ;
l_run_id number(9) := 0 ;
l_date date ;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -1400);
begin
n1 := dbms_utility.get_time ;
--
begin
select run2.nextval into l_run_id from dual ;
--
--
l_date := sysdate ;
select max(last_run_time) into l_date from rdo.rdo_sea_run ;
dbms_output.put_line(to_char(l_date,'dd/mm/yyyy hh24:mi:ss') ) ;
--
for x in 
(select b.alert_rules_seq_id  alert_seq_id,
b.fleet fleet ,
JOSPs.detStatsJOSP(cursor(select  parameter_value
from ( select fleet,dim_seq_id,param_seq_id,flight_datetime ,parameter_value,
dense_rank() over ( partition by fleet,dim_seq_id,param_seq_id order by flight_datetime desc) rank
from rdo.rdo_param_v )
where rank <= b.persistence_y
and fleet = b.fleet and param_seq_id = b.parameter_seq_id and dim_seq_id = b.dim_seq_id ) ,
b.persistence_x ,b.persistence_y ,b.test_name,
b.statistical_method_name, b.upper_threshold,b.lower_threshold,
b.sigma_limit, b.above_or_below,nvl(b.min_shift_value,0), nvl(b.center_line_num,0),
nvl(b.std_dev,1) ) alerting_value ,
b.aircraft_id  aircraft_id,
b.upper_control_limit ucl,
b.engine_id  engine_id,
nvl(b.center_line_num,0) cl ,
b.parameter_seq_id param_seq_id ,
b.flight_datetime flight_datetime,
b.lower_control_limit lcl  ,
b.flight_phase flight_phase,
b.install_datetime install_datetime
from
rdo.rdo_sea_alert_v2 b where b.sage_creation_date > l_date 
) loop
begin
insert into rdo.rdo_sea_alert(alert_rules_seq_id,
fleet_id,
alerting_value,
aircraft_id,
ucl_at_alert,
engine_id,
cl_at_alert,
parameter_seq_id,
flight_datetime,
lcl_at_alert,
airinc_recd_datetime,
flight_phase,
alert_generation_datetime,
install_datetime,
creation_date,last_update_date,run_id)
values(x.alert_seq_id,x.fleet,x.alerting_value,x.aircraft_id,x.ucl,x.engine_id,
x.cl,x.param_seq_id,x.flight_datetime,x.lcl,trunc(sysdate),x.flight_phase,trunc(sysdate),
x.install_datetime,trunc(sysdate),trunc(sysdate),l_run_id) ;
--
--
exception
when dml_errors then
null ;
when others then
dbms_output.put_line('insert error '||sqlcode) ;
end ;  --
end loop ;
dbms_output.put_line('i-2') ; --end loop reached.
--
select nvl( min(to_date(start_time,'mm/dd/rr hh24:mi:ss')),sysdate)  into l_date 
  from v$transaction;
--
insert into rdo.rdo_sea_run values (l_date) ;
dbms_output.put_line(to_char(l_date,'dd/mm/yyyy hh24:mi:ss') ) ;
commit ;
dbms_output.put_line('i-3'||l_date) ;
end ;
n2 := dbms_utility.get_time ;
insert into rdo.rdo_sea_perf_data values(l_run_id,'Josp-result insert complete in...mseconds' ,n2-n1 ,sysdate,'josp') ;
commit ;
dbms_output.put_line('at the end'||l_date) ;
exception
when others then
dbms_output.put_line(sqlcode) ;
end ;
/

---

so In the Last I did save the new last run time..which I intend to use in my next run..

But Here is what I get -- 

Last_run_time : 20/10/2004 09:29:15
new_last_run_time :18/10/2004 05:07:28  --this comes from V$transaction.

I have been running this proc manualy and updating last_run_time so that my rdo_sea_run table has following data after I run this procedure (after making the changes).

 SQL> select to_char(last_run_time,'dd/mm/yy hh24:mi:ss') from rdo.rdo_sea_run ;

TO_CHAR(LAST_RUN_
-----------------
20/10/04 05:29:51
20/10/04 05:32:40
20/10/04 05:37:08
20/10/04 05:45:28
20/10/04 06:05:41
20/10/04 07:52:46
20/10/04 05:04:50
20/10/04 05:21:04
20/10/04 07:54:48
20/10/04 07:58:06
20/10/04 08:08:24

TO_CHAR(LAST_RUN_
-----------------
20/10/04 08:10:26
20/10/04 09:02:36
20/10/04 09:04:37
20/10/04 09:13:48
18/10/04 05:07:28
20/10/04 09:29:15
18/10/04 05:07:28

18 rows selected.

Elapsed: 00:00:00.01

Thanks 
Ajeet

October 20, 2004 - 11:34 am UTC

you have a transaction that has been open for two days.

you have NEVER seen its changes

you need to PULL its changes when it completes.

until it completes, it'll always be that day/time

I have a trigger which will always run -- it gets data almost all the time...from another database.

Ajeet, October 20, 2004 - 11:48 am UTC

Tom,
I do have a trigger which is on a remote database table and which inserts records in one of my database tables...where I am trying to run the above proc..

other than this i could not find anything else.
I had a job which was running since last 2 days --I killed that this morning.

is there any solution of this.I know this is the only correct way to get the data as I read it on another link but i am not able to get it working so far.

Thanks
Ajeet

October 20, 2004 - 11:52 am UTC

the job is still rolling back -- you might have killed it but if it took TWO DAYS to get where it was, it will take -- well, a bit of time -- to undo its work.

It is still a transaction, you can monitor it rolling back by looking at the used_ublk in v$transaction to watch it get smaller and smaller.

David Aldridge, October 20, 2004 - 12:06 pm UTC

>> But -- dbms_job will never run an already running job either (so you will not have 2, 3, 4 or more copies of your stored procedure running).

Seems prudent to use DBMS_LOCK again in the procedure to prevent multiple copies running, even if the procedure is generally only started through DBMS_JOB

I tried this -- could not see anything like abnormal...

Ajeet, October 20, 2004 - 12:11 pm UTC

SQL> select
   substr(s.username,1,18) username,
   substr(s.program,1,15) program,
   decode(s.command,
       0,'No Command',
       1,'Create Table',
       2,'Insert',
       3,'Select',
       6,'Update',
       7,'Delete',
       9,'Create Index',
      15,'Alter Table',
      21,'Create View',
      23,'Validate Index',
      35,'Alter Database',
      39,'Create Tablespace',
      41,'Drop Tablespace',
      40,'Alter Tablespace',
      53,'Drop User',
      62,'Analyze Table',
      63,'Analyze Index',
      s.command||': Other') command
from
   v$session     s,
   v$process     p,
   v$transaction t,
   v$rollstat    r,
   v$rollname    n
where s.paddr = p.addr
and   s.taddr = t.addr (+)
and   t.xidusn = r.usn (+)
and   r.usn = n.usn (+)
order by 1
;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17
  18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33
  34

USERNAME        PROGRAM         COMMAND
--------------- --------------- -----------------------------------------------
FLT_WEB         JDBC Thin Clien No Command
FLT_WEB         JDBC Thin Clien No Command
FLT_WEB         JDBC Thin Clien No Command
FLT_WEB         JDBC Thin Clien No Command
FLT_WEB         JDBC Thin Clien No Command
OPS$A26547T     jre@dael002 (TN No Command
OPS$ABKKRST                     No Command
OPS$NGNF1IT     C:\Documents an Select
OPS$RDETL       sqlnav3.exe     No Command
OPS$RDETL                       No Command
OPS$RDETL       PLSQLDev.exe    No Command

USERNAME        PROGRAM         COMMAND
--------------- --------------- -----------------------------------------------
OPS$RDETL       PLSQLDev.exe    Select
OPS$RDETL       oracle@bsedb102 No Command
OPS$RDETL       PLSQLDev.exe    No Command
OPS$RDETL       sqlplus@bsedb10 Select
RDO             PLSQLDev.exe    No Command
RDO             PLSQLDev.exe    No Command
RDO             PLSQLDev.exe    No Command
RDO             PLSQLDev.exe    No Command
RDO             PLSQLDev.exe    No Command
RDO             PLSQLDev.exe    No Command
RDO             PLSQLDev.exe    No Command

USERNAME        PROGRAM         COMMAND
--------------- --------------- -----------------------------------------------
RDO             PLSQLDev.exe    No Command
RDO             PLSQLDev.exe    No Command
RDO_APP         PLSQLDev.exe    No Command
RMD_WEB         oracle@bseqa100 No Command
RMD_WEB         oracle@bseqa100 No Command
RMD_WEB         oracle@bseqa100 No Command
SGE1_RDFT01     Compression@bse No Command
SGE1_RDFT01     ExtractControl_ No Command
SGE1_RDFT01     AlertControl@bs No Command
SGE1_RDFT01     OwpProcessing_M No Command
SGE1_RDFT01     ExtractControl_ No Command

USERNAME        PROGRAM         COMMAND
--------------- --------------- -----------------------------------------------
SGE1_RDFT01     ExtractControl_ No Command
SGE1_RDFT01     OwpProcessing_M No Command
SGE1_RDFT01     OwpProcessing_M No Command
SGE1_RDFT01     ExtractControl_ No Command
SGE1_RDFT01     OwpProcessing_M No Command
SGE1_RDFT01     ExtractControl_ No Command
SGE1_RDFT01     OwpProcessing_M No Command
SGE2_RDFT02     Reportgen@bseq2 No Command
SGE2_RDFT02     Compression@bse No Command
SGE2_RDFT02     AlertControl@bs No Command
SGE2_RDFT02     oracle@bseqa100 No Command

USERNAME        PROGRAM         COMMAND
--------------- --------------- -----------------------------------------------
SGE2_RDFT02     OwpProcessing_M No Command
SGE2_RDFT02     ExtractControl_ No Command
SGE2_RDFT02     ExtractControl_ No Command
SGE2_RDFT02     OwpProcessing_M No Command
SGE2_RDFT02     OwpProcessing_M No Command
SGE2_RDFT02     ExtractControl_ No Command
SGE2_RDFT02     OwpProcessing_M No Command
SGE2_RDFT02     ExtractControl_ No Command
SGE2_RDFT02     OwpProcessing_M No Command
SGE2_RDFT02     ExtractControl_ No Command
SGE2_RDFT02     extract.exe@bse No Command

USERNAME        PROGRAM         COMMAND
--------------- --------------- -----------------------------------------------
SGE2_RDFT02     LoadDataSets@bs No Command
SGE2_RDFT02     extract.exe@bse No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command

USERNAME        PROGRAM         COMMAND
--------------- --------------- -----------------------------------------------
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command

USERNAME        PROGRAM         COMMAND
--------------- --------------- -----------------------------------------------
                oracle@bseqa103 No Command
                oracle@bseqa103 No Command
Elapsed: 00:00:00.12
SQL> select used_ublk,start_time from v$transaction
where start_time = (Select min(start_time) from v$transaction ) ;  2

 USED_UBLK START_TIME
---------- --------------------
         1 10/18/04 05:07:28
Elapsed: 00:00:00.04

October 20, 2004 - 12:18 pm UTC

that transaction is still there, something, someone has a transaction open, active , going -- for two days.

why you didn't get the transaction start time in your query so you could see who....

You are always correct at least for us.

Ajeet, October 20, 2004 - 12:30 pm UTC

Yes -- when I selected started_time -- I found the query which was running a query since 2 days..I killed that session and now I get the correct time -that is of today..

SQL> select used_ublk,start_time from v$transaction
where start_time = (Select min(start_time) from v$transaction ) ;  2

 USED_UBLK START_TIME
---------- --------------------
         1 10/20/04 10:08:22

want to ask just one last question --as I am not very clear about the concept here -- 

Question is -- Now if I start running my procedure which i was trying to run -- and storing the min(start_time) from v$transaction before the refresh in a table will ensure that I will not loose any data ...

your answer give us confidence so want to confirm it.I will do my home work --will try to learn more about this transaction stuff...

Thanks
Ajeet

do i need to join with V$lock...

Ajeet, October 20, 2004 - 12:44 pm UTC

Tom -- How can I ensure that i am taking min(start_time) of the transactions which are related with the tables used in the above procedure..as it seems I have to do it.as there can be so many other transactions in my database so min(start_time) is never correct..

and i see a query to do this but it takes 3-4 minutes to give me the result but i do run this proc at every 1 minute...

Thanks again

October 20, 2004 - 4:40 pm UTC

so, which part of the query takes 3-4 minutes, did you try to tune it at all? or even figure out which layer of subquery runs slow.

do i need to join with V$lock...

Ajeet, October 20, 2004 - 1:04 pm UTC

Additional question regarding "run infinite loop in background"

j., October 21, 2004 - 2:57 am UTC

in track "A Custom Error Handling System"
</code> http://asktom.oracle.com/pls/ask/f?p=4950:61:2542378341719727020::::P61_ID:4684560825338 <code>
i asked how to implement an infinite loop running in background. it looks very similar to what you 've described (recommended?) in your first reply here.

if one assumes that "run in the background" means "start it via DBMS-job", this answer seems to be somehow contradictory to some replies given in the abovementioned track (otherwise please ignore this entry).

on june 22, 2004 the first reply stated: "it is a really bad idea, it basically defeats the job queue interface, leads to a whole set of problems. doesn't work in the long run."

the next reply emphasised that: "the job queues just were not designed to run a job forever."

in addition i got the following answer to my question "what 's wrong with "neverending-dbms-jobs?" from another source (ORACLE-employee from development):

<quote>
But, the main problem with infinite loops in PL/SQL is that you may hit an ORA-600 [4454] since the internal variable used to store the savepoint numbers is only 4 bytes, so when this reaches its max value (0xffffffff) then an ORA -
600 [4454] is raised.
An enhancement request exists in bug:2939416 (non-published) to have this variable changed to 8 bytes - this will not prevent the problem, but allow the code to run for much longer time before hitting the limit.
</quote>

can you please explain that "run in the background"?

and could you please add some more details about that "savepoint numbers" mentioned in the last excerpt (e.g. when/how this applies to a infinite loop which does NO DML at all)?

October 21, 2004 - 6:54 am UTC

i don't see any connection between this and running an infinite loop?

running in background means that --

echo exec do_procedure | sqlplus / &

for example. not using jobs.

Select from V$transaction takes time

Ajeet, October 21, 2004 - 6:16 am UTC

Tom,
In the query below -- 

SQL> select nvl(min(start_time),'x' )
from v$transaction
where addr in
(select taddr
  from v$session
   where sid in
 (select sid
     from v$lock
     where type = 'TM'
       and id1 in
  (select object_id
      from dba_objects
    where object_name in ('RDO_SEA_ALERT_REF' )
       and owner = 'RDO')
)
)
/  2    3    4    5    6    7    8    9   10   11   12

The outermost query that is the select .....from v$transaction where addr in....takes almost 99% of total time.

If I replace IN with =  ....which is good enough in my case 
it takes 3 miliseconds.which is very good for me.

I am trying to tune it --can not explain plan it as i don't have direct select on the underlying objects of these views.
but can do autotrace and I can see a huge difference in 
db block reads -- 28764 vs 176 ..

But I am still confused about the way v$transaction can be applied in the above case -- (in the original problem I posted );


I will re-state the problem I am facing -- or what i want to do here..

I have a table rdo_sea_alert_ref which is getting data from another table using a triggger.(so trigger is on another table and whenever a new rows comes in the source table it does insert the data -- in the table rdo_sea_alert_ref).

I need to run a procedure on rdo_sea_alert_ref(i have a view on this table which i use in the procedure) at every one minute and get data from this table which have arrived in last 1 minute -- i have a field called sage_creation_date  in the table rdo_sea_alert_ref ,which i will use to get this..

Now i wanted to use v$transaction so that i do not losse any data which has arrived when i was running my procedure.

but seems like inserts in the table rdo_sea_alert_ref through a trigger will not be reflected in v$transaction as it is being done by trigger..I ran the sql to understand this..

declare
v_date1 varchar2(30) ;
v_date  varchar2(30) ;
v_date2 varchar2(30) ;
v_date3 varchar2(30) ;
v_date4 varchar2(30) ;
v_date5 varchar2(30) ;
v_date6 varchar2(30) ;
begin
select nvl(min(start_time),'x' ) into v_date 
from v$transaction
where addr =
(select taddr
  from v$session
   where sid in
 (select sid
     from v$lock
     where type = 'TM'
       and id1 in
  (select object_id
      from dba_objects
    where object_name in ('RDO_SEA_ALERT_REF' )
       and owner = 'RDO')
)
) ;
--
for x in
(select b.alert_rules_seq_id  alert_seq_id,
b.fleet fleet ,
JOSPs.detStatsJOSP(cursor(select  parameter_value
from ( select fleet,dim_seq_id,param_seq_id,flight_datetime ,parameter_value,
dense_rank() over ( partition by fleet,dim_seq_id,param_seq_id order by flight_datetime desc) rank
from rdo.rdo_param_v )
where rank <= b.persistence_y
and fleet = b.fleet and param_seq_id = b.parameter_seq_id and dim_seq_id = b.dim_seq_id ) ,
b.persistence_x ,b.persistence_y ,b.test_name,
b.statistical_method_name, b.upper_threshold,b.lower_threshold,
b.sigma_limit, b.above_or_below,nvl(b.min_shift_value,0), nvl(b.center_line_num,0),
nvl(b.std_dev,1) ) alerting_value ,
b.aircraft_id  aircraft_id,
b.upper_control_limit ucl,
b.engine_id  engine_id,
nvl(b.center_line_num,0) cl ,
b.parameter_seq_id param_seq_id ,
b.flight_datetime flight_datetime,
b.lower_control_limit lcl  ,
b.flight_phase flight_phase,
b.install_datetime install_datetime
from
rdo.rdo_sea_alert_v2 b where b.sage_creation_date > sysdate-1/48
) loop
dbms_output.put_line(x.fleet||'-'||x.alerting_value) ;
end loop ;
select nvl(min(start_time),'x' ) into v_date1 
from v$transaction
where addr =
(select taddr
  from v$session
   where sid in
 (select sid
     from v$lock
     where type = 'TM'
       and id1 in
  (select object_id
      from dba_objects
    where object_name in ('RDO_SEA_ALERT_REF' )
       and owner = 'RDO')
)
) ;
dbms_output.put_line('before i started :'||v_date) ;
dbms_output.put_line('after loop ended :'||v_date1) ;
begin
delete from rdo_sea_alert_ref ;
select nvl(min(start_time),'x' ) into v_date2 
from v$transaction
where addr =
(select taddr
  from v$session
   where sid in
 (select sid
     from v$lock
     where type = 'TM'
       and id1 in
  (select object_id
      from dba_objects
    where object_name in ('RDO_SEA_ALERT_REF' )
       and owner = 'RDO')
)
) ;
--dbms_output.put_line('before i rolled back :'||v_date2) ;
end ;
dbms_output.put_line('before i rolled back :'||v_date2) ;
rollback ;
select nvl(min(start_time),'x' ) into v_date4 
from v$transaction
where addr =
(select taddr
  from v$session
   where sid in
 (select sid
     from v$lock
     where type = 'TM'
       and id1 in
  (select object_id
      from dba_objects
    where object_name in ('RDO_SEA_ALERT_REF' )
       and owner = 'RDO')
)
) ;
dbms_output.put_line('after i rolled back :'||v_date4) ;
insert into rdo_sea_alert_ref select * from rdo_sea_alert_ref where rownum <= 1 ;
select nvl(min(start_time),'x' ) into v_date5 
from v$transaction
where addr =
(select taddr
  from v$session
   where sid in
 (select sid
     from v$lock
     where type = 'TM'
       and id1 in
  (select object_id
      from dba_objects
    where object_name in ('RDO_SEA_ALERT_REF' )
       and owner = 'RDO')
)
) ;
dbms_output.put_line('after i inserted :'||v_date5) ;
commit ;
select nvl(min(start_time),'x' ) into v_date6 
from v$transaction
where addr =
(select taddr
  from v$session
   where sid in
 (select sid
     from v$lock
     where type = 'TM'
       and id1 in
  (select object_id
      from dba_objects
    where object_name in ('RDO_SEA_ALERT_REF' )
       and owner = 'RDO')
)
) ;
dbms_output.put_line('after i commit the insert :'||v_date6) ;
end ;
/
--
declare
v_date1 varchar2(30) ;
v_date7  varchar2(30) ;
v_date2 varchar2(30) ;
v_date3 varchar2(30) ;
v_date4 varchar2(30) ;
v_date5 varchar2(30) ;
v_date6 varchar2(30) ;
v_cnt1  number ;
v_cnt2  number ;
v_cnt3  number ;
v_cnt4  number ;
v_cnt5  number ;
v_cnt6  number ;
v_cnt7  number ;
begin
select nvl(min(start_time),'x' ) into v_date1 
from v$transaction
where addr =
(select taddr
  from v$session
   where sid in
 (select sid
     from v$lock
     where type = 'TM'
       and id1 in
  (select object_id
      from dba_objects
    where object_name in ('RDO_SEA_ALERT_REF' )
       and owner = 'RDO')
)
) ;
select count(*) into v_cnt1 from rdo_sea_alert_ref ;
dbms_lock.sleep(60);
select nvl(min(start_time),'x' ) into v_date2 
from v$transaction
where addr =
(select taddr
  from v$session
   where sid in
 (select sid
     from v$lock
     where type = 'TM'
       and id1 in
  (select object_id
      from dba_objects
    where object_name in ('RDO_SEA_ALERT_REF' )
       and owner = 'RDO')
)
) ;
select count(*) into v_cnt2 from rdo_sea_alert_ref ;
dbms_lock.sleep(60);
select nvl(min(start_time),'x' ) into v_date3 
from v$transaction
where addr =
(select taddr
  from v$session
   where sid in
 (select sid
     from v$lock
     where type = 'TM'
       and id1 in
  (select object_id
      from dba_objects
    where object_name in ('RDO_SEA_ALERT_REF' )
       and owner = 'RDO')
)
) ;
select count(*) into v_cnt3 from rdo_sea_alert_ref ;
dbms_lock.sleep(60);
select nvl(min(start_time),'x' ) into v_date4 
from v$transaction
where addr =
(select taddr
  from v$session
   where sid in
 (select sid
     from v$lock
     where type = 'TM'
       and id1 in
  (select object_id
      from dba_objects
    where object_name in ('RDO_SEA_ALERT_REF' )
       and owner = 'RDO')
)
) ;
select count(*) into v_cnt4 from rdo_sea_alert_ref ;
dbms_lock.sleep(60);
select nvl(min(start_time),'x' ) into v_date5 
from v$transaction
where addr =
(select taddr
  from v$session
   where sid in
 (select sid
     from v$lock
     where type = 'TM'
       and id1 in
  (select object_id
      from dba_objects
    where object_name in ('RDO_SEA_ALERT_REF' )
       and owner = 'RDO')
)
) ;
select count(*) into v_cnt5 from rdo_sea_alert_ref ;
select nvl(min(start_time),'x' ) into v_date6 
from v$transaction
where addr =
(select taddr
  from v$session
   where sid in
 (select sid
     from v$lock
     where type = 'TM'
       and id1 in
  (select object_id
      from dba_objects
    where object_name in ('RDO_SEA_ALERT_REF' )
       and owner = 'RDO')
)
) ;
dbms_output.put_line(v_cnt1||'-'||v_date2) ;
dbms_output.put_line(v_cnt2||'-'||v_date3) ;
dbms_output.put_line(v_cnt3||'-'||v_date4) ;
dbms_output.put_line(v_cnt4||'-'||v_date5) ;
dbms_output.put_line(v_cnt5||'-'||v_date6) ;
end ;
/

and output was


59093-x
59197-x
59306-x
59382-x
59472-x

PL/SQL procedure successfully completed.

so you can see new rows are coming into rdo_sea_alert_ref but v$transaction does not capture them.

my question is there a way to do so..that is get the start_time when i using a trigger..meanwhile i will try to use autonomus transaction in my trigger code..
--
please correct me if i am doing it wrong.
--
thanks
Ajeet

small correction

Ajeet, October 21, 2004 - 6:47 am UTC

Tom,

In my earlier post i have give 2 pl/sql block --
Please look at the second one -- that is the last block in the post --as I have provided output only for this one.

for first block -- i was trying something else.and pasted it by mistake here.sorry about confusion.

Thanks
Ajeet

October 21, 2004 - 7:07 am UTC

there is too much code there for me to read this morning.

you cannot change "in" to "=", you will get "subquery returns too many rows" at some point in the near future.

You'll need to find another way to do that query, it won't work for you in real life.

making it short

Ajeet, October 21, 2004 - 7:22 am UTC

Tom,
sorry for too many line of code.But all I was trying to say is

I have a table rdo_sea_alert_ref which is getting data from another table using
a triggger.(so trigger is on another table and whenever a new rows comes in the
source table it does insert the data -- in the table rdo_sea_alert_ref).

I need to run a procedure on rdo_sea_alert_ref(i have a view on this table which
i use in the procedure) at every one minute and get data from this table which
have arrived in last 1 minute -- i have a field called sage_creation_date in
the table rdo_sea_alert_ref ,which i will use to get this..

Now i wanted to use v$transaction so that i do not losse any data which has
arrived when i was running my procedure.

but seems like inserts in the table rdo_sea_alert_ref through a trigger will not
be reflected in v$transaction as it is being done by trigger..

Then I ran the code which i posted above to actually show this.

i will find a way to use v$transaction query so you can leave that..anything you can suggest on my above question
would be good enough for me to get going on this problem.

Thanks

October 21, 2004 - 7:53 am UTC

everything is reflected in v$transaction -- the very INSTANT you start a transaction, it is reflected. triggers are not special in that regard.

Then how would you ever know about it...

Ajeet, October 21, 2004 - 8:11 am UTC

Tom,

so in my case i will never be able to get min(start_time) as trigger will insert a row and right after that start_time entry for this trigger will disappear from v$transaction as this transaction is complete and comitted.
I checked this much..all the day today.and i gave the proof above..that is why even record count keep increasing in my table due to trigger insert but v$transaction never shows an entry for min(strar_time)..

seems like i can not use v$transaction in my case..and use some alternate approach.but you always have some solution so still don't want to be disappointed.

thanks
Ajeet

October 21, 2004 - 2:20 pm UTC

not following you (lots of stuff up there, i'm traveling -- hard to digest everything).

Your trigger and query v$transaction
your trigger can find the oldest transaction.

not sure what else needs to be happening?

?

j., October 21, 2004 - 9:15 am UTC

tom, would you mind to have a look at my question, too (the very first today)?

sorry for that: i just didn't see your reply ...

j., October 21, 2004 - 9:23 am UTC

Use STATUS instead of timestamp

Edgar, November 03, 2004 - 9:20 am UTC

Just in case,

May be, do not use timestamps (dates) on this processing at all.
Just add the column named PROCESSED (or STATUS) which has default value 'N' if record is not jet processed by your batch job, NULL otherwise (bach job should set it to NULL after successful processing, in the same processing transaction).
Optionally, create index on this column.

It is usual way to organize "queues"...

November 03, 2004 - 10:31 am UTC

AQ is the way to "optimize queues" -- the code is already written.

.. locking

Edgar, November 03, 2004 - 9:27 am UTC

.. in addition, consider using "select for update nowait" or use "user defined locks" to not allow your batches to overlap.

i agree with Edgar from Latvia ..do not use Timestamp for this

pasko, April 26, 2005 - 4:14 pm UTC

Hi Tom,

your solution for using v$transactions works well,

i have done something similar to this before , and i didn't use Timestamp columns as the basis for my next-pull Date.

i used the method similar to the one suggested by user Edgar from Latvia ..

add a status column to the Table ( a number or some varchar2)
to show that the row is already processed or not.
To give order , you can add another Column , say last_update as a Date Column , and this will be used only as an Order By to simulate FIFO behavior.
If there is a chance that last_update could be duplicated , then you can even assign another column , say priority as NUMBER , to break the tie for rows having the same last_update

so the dbms_job Query will be doing :

select c1,c2,...
from my_table
where status = 'not_processed'
order by last_update asc nulls last
for update ;
---
--do processing
--
update my_table
set status = 'processed'
for all rows gotten from above Query.

I think this method will never re-pull a record more
than once.

What is your take on this Method Tom?

April 26, 2005 - 4:35 pm UTC

I see the puller blocking the pushers here.

The timestamp method is generally used in a transactional system to feed a data warehouse. The pull doesn't need to ever lock anything in the system it is pulling from. This would.

If that is not a problem, AND you have only one system pulling changes, AND the additional load of updating every record that gets updated twice is OK, this works.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

How to get correct values from v$transaction.

How to run PL/SQL procedure in the background

I get an old date from v$transaction

I have a trigger which will always run -- it gets data almost all the time...from another database.

I tried this -- could not see anything like abnormal...

You are always correct at least for us.

do i need to join with V$lock...

do i need to join with V$lock...

Additional question regarding "run infinite loop in background"

Select from V$transaction takes time

small correction

making it short

Then how would you ever know about it...

?

sorry for that: i just didn't see your reply ...

Use STATUS instead of timestamp

.. locking

i agree with Edgar from Latvia ..do not use Timestamp for this

More to Explore

PL/SQL demos

PL/SQL docs

How to schdule a job at every one minute and ensure it is completed .

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

How to get correct values from v$transaction.

How to run PL/SQL procedure in the background

I get an old date from v$transaction

I have a trigger which will always run -- it gets data almost all the time...from another database.

I tried this -- could not see anything like abnormal...

You are always correct at least for us.

do i need to join with V$lock...

do i need to join with V$lock...

Additional question regarding "run infinite loop in background"

Select from V$transaction takes time

small correction

making it short

Then how would you ever know about it...

?

sorry for that: i just didn't see your reply ...

Use STATUS instead of timestamp

.. locking

i agree with Edgar from Latvia ..do not use Timestamp for this

More to Explore

PL/SQL demos

PL/SQL docs