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
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
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.