Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.
Solved
Finding when Rollbacks occur in Oracle
Posted on 2013-11-04
We have an application we suspect is not properly hadnling rollbacks. The symptom is occasionally (maybe 1 out of 1000 times) a scenario occurs one of the tables gets out of sync such that it appears a transaction is partially committed.
When this occurs our dba cannot find any errors in the Oracle log, but I am not sure we have logging levels at the highest setting.
When the problem occurs next, we can idenify the approximate time that it occured. Is there any Oracle tool that can show us if any rollbacks occurred at that time?
Thanks!
When this occurs our dba cannot find any errors in the Oracle log, but I am not sure we have logging levels at the highest setting.
When the problem occurs next, we can idenify the approximate time that it occured. Is there any Oracle tool that can show us if any rollbacks occurred at that time?
Thanks!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
2
- +1
6 Comments
Active today
You've defined the problem, but since I am not an oracle person, not sure whether a non-committed transaction is recorded and even if it is, not sure whether you can track it back given that you may have many transactions started and some committed while other rolled back.
You have to review your code that deals with this table combination and if you don't have it already look at the transactions/process.
You may have depended transactions that are setup independently.
You have to review your code that deals with this table combination and if you don't have it already look at the transactions/process.
You may have depended transactions that are setup independently.
Active today
have you tried adding a
begin exception >>log the exeception end
block around the rollback to catch any possible error ?
i came across a nesting of savepoints once which wasn't correct
something like this:
begin exception >>log the exeception end
block around the rollback to catch any possible error ?
i came across a nesting of savepoints once which wasn't correct
something like this:
savepoint a;
if x > 1 then
savepoint b;
if Y > 1 then
if Z > 1 then
rollback to a;
end if;
commit b;
end if;
end if;
commit a;
Active today
I think the asker is looking for an oracle DB level tool to discover this.
I suspect at this time, the code where this is an issue is yet to be located/identified.
I suspect at this time, the code where this is an issue is yet to be located/identified.
Active 5 days ago
Hi,
You can have statistics such as the number of rommits/rollbacks in a session.
But if you need more information, only logminer can help. It is an Oracle tool to read the redo logs and they have information about each updates and each commit/rollback.
Regards,
Franck.
You can have statistics such as the number of rommits/rollbacks in a session.
But if you need more information, only logminer can help. It is an Oracle tool to read the redo logs and they have information about each updates and each commit/rollback.
Regards,
Franck.
I am trying to collect evidence of production problem that can be given to development group to help them isolate cause of problem. If I can prove a rollback occured at the time that problem occurred, it will help them isolate the problem
Is Logminer the best tool for examining the redo logs? Is there any place other than the Redo logs that Rollbacks are logged?
Is Logminer the best tool for examining the redo logs? Is there any place other than the Redo logs that Rollbacks are logged?
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
How to Create User-Defined Aggregates in Oracle
Before we begin creating these things, what are user-defined aggregates? They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query
Introduction
If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them. …
This video shows syntax for various backup options while discussing how the different basic backup types work. It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month8 days, 17 hours left to enroll
604 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.