Query to return all Oracle Deadlocks in 12c
up vote
2
down vote
favorite
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 at 9:11
Balazs Papp
25k2930
asked Dec 13 at 8:49
Molenpad
1,200423
add a comment |
up vote
2
down vote
favorite
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 at 9:11
Balazs Papp
25k2930
asked Dec 13 at 8:49
Molenpad
1,200423
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
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 at 9:11
Balazs Papp
25k2930
asked Dec 13 at 8:49
Molenpad
1,200423
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 at 9:11
Balazs Papp
25k2930
asked Dec 13 at 8:49
Molenpad
1,200423
edited Dec 13 at 9:11
Balazs Papp
25k2930
asked Dec 13 at 8:49
Molenpad
1,200423
edited Dec 13 at 9:11
Balazs Papp
25k2930
edited Dec 13 at 9:11
Balazs Papp
25k2930
edited Dec 13 at 9:11
Balazs Papp
25k2930
25k2930
asked Dec 13 at 8:49
Molenpad
1,200423
asked Dec 13 at 8:49
Molenpad
1,200423
asked Dec 13 at 8:49
Molenpad
1,200423
1,200423
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
up vote
2
down vote
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 at 9:10
Balazs Papp
25k2930
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 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 at 10:00
add a comment |
up vote
1
down vote
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 at 17:30
dbamex
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
up vote
2
down vote
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 at 9:10
Balazs Papp
25k2930
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 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 at 10:00
add a comment |
up vote
2
down vote
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 at 9:10
Balazs Papp
25k2930
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 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 at 10:00
add a comment |
up vote
2
down vote
up vote
2
down vote
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 at 9:10
Balazs Papp
25k2930
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 at 9:10
Balazs Papp
25k2930
answered Dec 13 at 9:10
Balazs Papp
25k2930
answered Dec 13 at 9:10
Balazs Papp
25k2930
answered Dec 13 at 9:10
Balazs Papp
25k2930
25k2930
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 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 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 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 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 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 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 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 at 10:00
add a comment |
up vote
1
down vote
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 at 17:30
dbamex
716
add a comment |
up vote
1
down vote
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 at 17:30
dbamex
716
add a comment |
up vote
1
down vote
up vote
1
down vote
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 at 17:30
dbamex
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 at 17:30
dbamex
716
answered Dec 13 at 17:30
dbamex
716
answered Dec 13 at 17:30
dbamex
716
answered Dec 13 at 17:30
dbamex
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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
