Query to return all Oracle Deadlocks in 12c
Is there an object I can query in 12c to return all ORA-60 Deadlock alerts?
I just want to do a weekly report that outputs the number of deadlocks that have occurred and what schema they occurred on.
My Oracle knowledge is not great, and I have drawn a blank when searching for resources on this online.
Thanks
oracle oracle-12c deadlock
edited Dec 13 '18 at 9:11
Balazs Papp
25.5k2930
asked Dec 13 '18 at 8:49
MolenpadMolenpad
1,212425
add a comment |
Is there an object I can query in 12c to return all ORA-60 Deadlock alerts?
I just want to do a weekly report that outputs the number of deadlocks that have occurred and what schema they occurred on.
My Oracle knowledge is not great, and I have drawn a blank when searching for resources on this online.
Thanks
oracle oracle-12c deadlock
edited Dec 13 '18 at 9:11
Balazs Papp
25.5k2930
asked Dec 13 '18 at 8:49
MolenpadMolenpad
1,212425
add a comment |
Is there an object I can query in 12c to return all ORA-60 Deadlock alerts?
I just want to do a weekly report that outputs the number of deadlocks that have occurred and what schema they occurred on.
My Oracle knowledge is not great, and I have drawn a blank when searching for resources on this online.
Thanks
oracle oracle-12c deadlock
edited Dec 13 '18 at 9:11
Balazs Papp
25.5k2930
asked Dec 13 '18 at 8:49
MolenpadMolenpad
1,212425
Is there an object I can query in 12c to return all ORA-60 Deadlock alerts?
I just want to do a weekly report that outputs the number of deadlocks that have occurred and what schema they occurred on.
My Oracle knowledge is not great, and I have drawn a blank when searching for resources on this online.
Thanks
oracle oracle-12c deadlock
oracle oracle-12c deadlock
edited Dec 13 '18 at 9:11
Balazs Papp
25.5k2930
asked Dec 13 '18 at 8:49
MolenpadMolenpad
1,212425
edited Dec 13 '18 at 9:11
Balazs Papp
25.5k2930
asked Dec 13 '18 at 8:49
MolenpadMolenpad
1,212425
edited Dec 13 '18 at 9:11
Balazs Papp
25.5k2930
edited Dec 13 '18 at 9:11
Balazs Papp
25.5k2930
edited Dec 13 '18 at 9:11
Balazs Papp
25.5k2930
25.5k2930
asked Dec 13 '18 at 8:49
MolenpadMolenpad
1,212425
asked Dec 13 '18 at 8:49
MolenpadMolenpad
1,212425
asked Dec 13 '18 at 8:49
MolenpadMolenpad
1,212425
1,212425
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Sure:
select
ss.value
from
v$sysstat ss
join v$statname sn using (statistic#)
where
sn.name = 'enqueue deadlocks'
;
I did not test it extensively though.
answered Dec 13 '18 at 9:10
Balazs PappBalazs Papp
25.5k2930
This seems to give me a number, but I'm not sure what time period it is for, and I'd like to break it down by schema if possible
– Molenpad
Dec 13 '18 at 9:33
@Molenpad It is the number of deadlocks since instance startup. I am not aware of any semi-reliable method that gives this information by schemas other than analyzing all the trace files created on deadlock occurences.
– Balazs Papp
Dec 13 '18 at 10:00
add a comment |
The deadlocks are reported to the alert log with the error ora-00060 if I remember well, and with X$DBGALERTEXT you should be able to "query" the alert log, based on that you should be able to create a query where you can get the deadlocks occurred during some window (days, weeks, etc) as long as you have the info in the alert log.
answered Dec 13 '18 at 17:30
dbamexdbamex
716
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f224862%2fquery-to-return-all-oracle-deadlocks-in-12c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sure:
select
ss.value
from
v$sysstat ss
join v$statname sn using (statistic#)
where
sn.name = 'enqueue deadlocks'
;
I did not test it extensively though.
answered Dec 13 '18 at 9:10
Balazs PappBalazs Papp
25.5k2930
This seems to give me a number, but I'm not sure what time period it is for, and I'd like to break it down by schema if possible
– Molenpad
Dec 13 '18 at 9:33
@Molenpad It is the number of deadlocks since instance startup. I am not aware of any semi-reliable method that gives this information by schemas other than analyzing all the trace files created on deadlock occurences.
– Balazs Papp
Dec 13 '18 at 10:00
add a comment |
Sure:
select
ss.value
from
v$sysstat ss
join v$statname sn using (statistic#)
where
sn.name = 'enqueue deadlocks'
;
I did not test it extensively though.
answered Dec 13 '18 at 9:10
Balazs PappBalazs Papp
25.5k2930
This seems to give me a number, but I'm not sure what time period it is for, and I'd like to break it down by schema if possible
– Molenpad
Dec 13 '18 at 9:33
@Molenpad It is the number of deadlocks since instance startup. I am not aware of any semi-reliable method that gives this information by schemas other than analyzing all the trace files created on deadlock occurences.
– Balazs Papp
Dec 13 '18 at 10:00
add a comment |
Sure:
select
ss.value
from
v$sysstat ss
join v$statname sn using (statistic#)
where
sn.name = 'enqueue deadlocks'
;
I did not test it extensively though.
answered Dec 13 '18 at 9:10
Balazs PappBalazs Papp
25.5k2930
Sure:
select
ss.value
from
v$sysstat ss
join v$statname sn using (statistic#)
where
sn.name = 'enqueue deadlocks'
;
I did not test it extensively though.
answered Dec 13 '18 at 9:10
Balazs PappBalazs Papp
25.5k2930
answered Dec 13 '18 at 9:10
Balazs PappBalazs Papp
25.5k2930
answered Dec 13 '18 at 9:10
Balazs PappBalazs Papp
25.5k2930
answered Dec 13 '18 at 9:10
Balazs PappBalazs Papp
25.5k2930
25.5k2930
This seems to give me a number, but I'm not sure what time period it is for, and I'd like to break it down by schema if possible
– Molenpad
Dec 13 '18 at 9:33
@Molenpad It is the number of deadlocks since instance startup. I am not aware of any semi-reliable method that gives this information by schemas other than analyzing all the trace files created on deadlock occurences.
– Balazs Papp
Dec 13 '18 at 10:00
add a comment |
This seems to give me a number, but I'm not sure what time period it is for, and I'd like to break it down by schema if possible
– Molenpad
Dec 13 '18 at 9:33
@Molenpad It is the number of deadlocks since instance startup. I am not aware of any semi-reliable method that gives this information by schemas other than analyzing all the trace files created on deadlock occurences.
– Balazs Papp
Dec 13 '18 at 10:00
This seems to give me a number, but I'm not sure what time period it is for, and I'd like to break it down by schema if possible
– Molenpad
Dec 13 '18 at 9:33
This seems to give me a number, but I'm not sure what time period it is for, and I'd like to break it down by schema if possible
– Molenpad
Dec 13 '18 at 9:33
@Molenpad It is the number of deadlocks since instance startup. I am not aware of any semi-reliable method that gives this information by schemas other than analyzing all the trace files created on deadlock occurences.
– Balazs Papp
Dec 13 '18 at 10:00
@Molenpad It is the number of deadlocks since instance startup. I am not aware of any semi-reliable method that gives this information by schemas other than analyzing all the trace files created on deadlock occurences.
– Balazs Papp
Dec 13 '18 at 10:00
add a comment |
The deadlocks are reported to the alert log with the error ora-00060 if I remember well, and with X$DBGALERTEXT you should be able to "query" the alert log, based on that you should be able to create a query where you can get the deadlocks occurred during some window (days, weeks, etc) as long as you have the info in the alert log.
answered Dec 13 '18 at 17:30
dbamexdbamex
716
add a comment |
The deadlocks are reported to the alert log with the error ora-00060 if I remember well, and with X$DBGALERTEXT you should be able to "query" the alert log, based on that you should be able to create a query where you can get the deadlocks occurred during some window (days, weeks, etc) as long as you have the info in the alert log.
answered Dec 13 '18 at 17:30
dbamexdbamex
716
add a comment |
The deadlocks are reported to the alert log with the error ora-00060 if I remember well, and with X$DBGALERTEXT you should be able to "query" the alert log, based on that you should be able to create a query where you can get the deadlocks occurred during some window (days, weeks, etc) as long as you have the info in the alert log.
answered Dec 13 '18 at 17:30
dbamexdbamex
716
The deadlocks are reported to the alert log with the error ora-00060 if I remember well, and with X$DBGALERTEXT you should be able to "query" the alert log, based on that you should be able to create a query where you can get the deadlocks occurred during some window (days, weeks, etc) as long as you have the info in the alert log.
answered Dec 13 '18 at 17:30
dbamexdbamex
716
answered Dec 13 '18 at 17:30
dbamexdbamex
716
answered Dec 13 '18 at 17:30
dbamexdbamex
716
answered Dec 13 '18 at 17:30
dbamexdbamex
716
716
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f224862%2fquery-to-return-all-oracle-deadlocks-in-12c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
