charlie cs wrote:
>>Daniel A. Morgan wrote;
>>Oracle version number?
>
> We are using Oracle 9i
>
>
>>Using rollback or UNDO?
>
> We are using UNDO tablespace
>
>
>>SQL or PL/SQL?
>
> I am not sure which session is the offending session, which is doing the
> commit. Actually my question is, can we get a counting of commit group by
> sid?
>
>
>>Executing what DML statements?In a loop?
>
> of course it is inserting, but I am not sure which session is the quilty
> one, there are so many connections. Some of them in loop, some of them
> using static sql,
> all I want to know is
>
> " who is doing the insert one row, commit once, not insert several thousand
> rows and commit"
>
> How can I find out?
Lets start out with a reality check. An insert can not cause an
ORA-01555. So this is not the cause. If the insert is expecting to find
information in an undo segment that it requires, however, that may cause
an ORA-01555 and that is a situation almost always traceable to a cursor
loop with an incremental commit. So we still need to see the actual
PL/SQL to provide specific help. But in general:
- Get rid of all incremental commits
- Increase the undo_retention parameter in your init.ora to the length
of time this procedure runs and alter the spfile to match.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Sun May 29 2005 - 20:38:40 CDT
