automatic undo mysteries 2005-11-29 - By fairlie rego
Hi,
I believe this is bug 3130630 which is closed as a dup of 3060261
To confirm the above please do the following
In one session
create a table called test and insert some data
SQL> begin
for i in 1..10000
loop
insert into test values(i);
end loop;
end;
/
In another session check the start_time for the above txn.
SQL> select start_time,to_char(sysdate,'mm/dd/yy hh24:mi:ss')
from v$transaction
where to_date(start_time,'mm/dd/yy hh24:mi:ss') > sysdate;
SQL> /
START_TIME TO_CHAR(SYSDATE,'
-- ---- ---- ---- -- -- ---- ---- ----
11/24/05 08:52:02 11/24/05 08:51:31
If the start_time of the txn is > than the sysdate it is the same issue.
Regards,
Fairlie Rego
amit.poddar@(protected) wrote:
Hi,
Please look at the alert log error I got
It shows ORA-01555 (See ORA-01555.ora-code.com) but the query duration is 0 seconds. How is that
possible that query goe the error just after starting
this is Oracle 9.2.0.5 running with automatic undo with undo_retention
= 36000
ORA-01555 (See ORA-01555.ora-code.com) caused by SQL statement below (Query Duration=0 sec, SCN:
0x000c.46f0197a):
Sat Nov 26 01:01:32 2005
SELECT
"EFFECTIVE_START_DATE","EFFECTIVE_END_DATE","POSITION_ID","ASSIGNMENT_STATUS
_TYPE_ID","PERSON_ID","ORGANIZATION_ID","ASS_ATTRIBUTE9","A
SS_ATTRIBUTE16" FROM "HR"."PER_ALL_ASSIGNMENTS_F" "AL1" WHERE
"EFFECTIVE_END_DATE">=:1 AND "EFFECTIVE_START_DATE"<=:2 AND
"ASS_ATTRIBUTE9" LIK
E 'CRN%' AND
(TO_DATE(SUBSTR("ASS_ATTRIBUTE16",1,10),'YYYY/MM/DD')>TO_DATE('16-FEB-'||TO
_CHAR(:3,'YYYY'),'DD-MON-YYYY')
AND TO_DATE(SUBSTR("AS
S_ATTRIBUTE16",1,10),'YYYY/MM/DD')<=TO_DATE('31-MAY-'||TO_CHAR(:4,'YYYY'),'DD
-MON-YYYY')
OR TO_DATE(SUBSTR("ASS_ATTRIBUTE16",1,10),'YYYY/MM/DD
')>TO_DATE('01-OCT-'||TO_CHAR(:5,'YYYY'),'DD-MON-YYYY') AND
TO_DATE(SUBSTR("ASS_ATTRIBUTE16",1,10),'YYYY/MM/DD')<=TO_DATE('15-JAN-'||TO
_CHAR(T
O_NUMBER(TO_CHAR(:6,'YYYY'))+1),'DD-MON-YYYY'))
Sat Nov 26 01:31:30 2005
I also checked stats$undostat that shows ssolderrcnt as 1 for the time
interval
but it shows 0 for both for all others block resue and steal counts.
So why is the query getting snapshot tool old.
Could any out guys could clarify my confustion
thanks
amit
--
http://www.freelists.org/webpage/oracle-l
Fairlie Rego
Senior Oracle Consultant
Optus Telecommunications
www.optus.com.au
Mobile: +61 4 02 792 405
Home: +61 2 8920 0273
When I read about the evils of drinking, I gave up reading.
-- ---- ---- ---- ---- ---- -----
Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
<DIV>Hi,</DIV> <DIV> </DIV> <DIV>I believe this is bug 3130630 which is
closed as a dup of 3060261<BR></DIV> <DIV>To confirm the above please do
the following</DIV> <DIV> </DIV> <DIV>In one session</DIV> <DIV> <
/DIV> <DIV>create a table called test and insert some data<BR><BR>SQL> begin
<BR>for i in 1..10000<BR>loop<BR>insert into test values(i);<BR>end loop;<BR>end
; </DIV> <DIV>/</DIV> <DIV> </DIV> <DIV>In another session check the
start_time for the above txn.</DIV> <DIV><BR>SQL> select start_time,to_char
(sysdate,'mm/dd/yy hh24:mi:ss')<BR>from v$transaction<BR>where to_date(start
_time,'mm/dd/yy hh24:mi:ss') > sysdate;<BR><BR>SQL> /<BR>START_TIME TO
_CHAR(SYSDATE,'<BR>-- ---- ---- ---- -- -- ---- ---- ----<BR><STRONG>11/24/05 08
:52:02 11/24/05 08:51:31</STRONG></DIV> <div><STRONG></STRONG> </div>
<div>If the start_time of the txn is > than the sysdate it is the same issue.
</div> <div> </div> <div>Regards,</div>
<div>Fairlie Rego</div> <DIV><BR><BR><BR><B><I>amit.poddar@(protected)</I></B>
wrote:</DIV> <BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT:
5px; BORDER-LEFT: #1010ff 2px solid">Hi,<BR><BR>Please look at the alert log
error I got<BR><BR>It shows ORA-01555 (See ORA-01555.ora-code.com) but the query duration is 0 seconds. How
is that<BR>possible that query goe the error just after starting<BR><BR>this is
Oracle 9.2.0.5 running with automatic undo with undo_retention<BR>= 36000<BR>
<BR>ORA-01555 (See ORA-01555.ora-code.com) caused by SQL statement below (Query Duration=0 sec, SCN:<BR
>0x000c.46f0197a):<BR>Sat Nov 26 01:01:32 2005<BR>SELECT<BR>"EFFECTIVE_START
_DATE","EFFECTIVE_END_DATE","POSITION_ID","ASSIGNMENT_STATUS_TYPE_ID","PERSON_ID
","ORGANIZATION_ID","ASS_ATTRIBUTE9","A<BR>SS_ATTRIBUTE16" FROM "HR"."PER_ALL
_ASSIGNMENTS_F" "AL1" WHERE<BR>"EFFECTIVE_END_DATE">=:1 AND "EFFECTIVE_START
_DATE"<=:2 AND<BR>"ASS_ATTRIBUTE9" LIK<BR>E 'CRN%'
AND<BR>(TO_DATE(SUBSTR("ASS_ATTRIBUTE16",1,10),'YYYY/MM/DD')>TO_DATE('16
-FEB-'||TO_CHAR(:3,'YYYY'),'DD-MON-YYYY')<BR>AND TO_DATE(SUBSTR("AS<BR>S
_ATTRIBUTE16",1,10),'YYYY/MM/DD')<=TO_DATE('31-MAY-'||TO_CHAR(:4,'YYYY'),'DD
-MON-YYYY')<BR>OR TO_DATE(SUBSTR("ASS_ATTRIBUTE16",1,10),'YYYY/MM/DD<BR>')>TO
_DATE('01-OCT-'||TO_CHAR(:5,'YYYY'),'DD-MON-YYYY') AND<BR>TO_DATE(SUBSTR("ASS
_ATTRIBUTE16",1,10),'YYYY/MM/DD')<=TO_DATE('15-JAN-'||TO_CHAR(T<BR>O_NUMBER
(TO_CHAR(:6,'YYYY'))+1),'DD-MON-YYYY'))<BR>Sat Nov 26 01:31:30 2005<BR><BR>I
also checked stats$undostat that shows ssolderrcnt as 1 for the time<BR
>interval<BR><BR>but it shows 0 for both for all others block resue and steal
counts.<BR><BR>So why is the query getting snapshot tool old.<BR><BR>Could any
out guys could clarify my confustion<BR><BR>thanks<BR>amit<BR>--<BR>http://www
.freelists.org/webpage/oracle-l<BR><BR><BR></BLOCKQUOTE> <DIV><BR></DIV><BR><BR
><DIV>
<DIV>
<DIV>
<DIV>
<DIV>
<DIV><STRONG>Fairlie Rego<BR></STRONG>Senior Oracle Consultant</DIV>
<DIV>Optus Telecommunications</DIV>
<DIV><A href="http://www.optus.com.au/">www.optus.com.au</A></DIV>
<DIV>Mobile: +61 4 02 792 405</DIV>
<DIV>Home: +61 2 8920 0273</DIV>
<DIV><BR> </DIV>
<DIV><FONT face="comic sans ms">When I read about the evils of drinking, I gave
up reading</FONT>.</DIV></DIV></DIV></DIV></DIV></DIV><p>
<hr size=1> <a href="http://pa.yahoo.com/*http://us.rd.yahoo.com/evt=36035/
*http://music.yahoo.com/unlimited/">Yahoo! Music Unlimited - Access over 1
million songs. Try it free.</a>
|
|
