Re: automatic undo mysteries

From: fairlie rego <fairlie_r_at_yahoo.com>
Date: Tue, 29 Nov 2005 18:01:07 -0800 (PST)
Message-ID: <20051130020108.77772.qmail@web31909.mail.mud.yahoo.com>

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_at_yale.edu wrote:
  Hi,

Please look at the alert log error I got

It shows ORA-01555 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 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.
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 29 2005 - 20:03:40 CST