Let's see how that can be done. That information can be used to great advantage when monitoring the progress of a rollback. The queries used are shown below:.
The first query returns the rollback status of the transaction; the second returns the used UNDO blocks being generated by the transaction or read to undo the work that has already been done.
From the second session, we can now monitor the progress of the rollback using the same queries as before. This time the first query reports a rollback is in progress:. The used UNDO block count eventually goes away since the transaction is neither active nor rolling back which signals the end of the rollback phase.
Now other work can proceed against the table or tables involved in the previous transaction. The reason the process is still around is because it is waiting on "SQLNet message from client". If it does ever get a message, it will then respond with an ORA "Your session has been killed" error number. At that point the session should go away.
The dedicated server process may remain alive until the client disconnects or exits. PMON may take ownership of the session while it is cleaning up any resources held by the session at the time it was killed. If it cannot clean everything up immediately it will leave the session under the PSEUDO process while performing other tasks. By finding the spid you can then force the process to be killed. What can be the problem with the Oracle database query on wordpress status ? Therefore, again, no rolling back transactions were captured by any query.
Thank you very much,. April 22, - pm UTC. Would you like to have more information? Gabriel, April 23, - am UTC. Hello Tom, Would you like to have more information? If yes, what else would you need to know? And what do you mean by '???? April 23, - pm UTC. I'm basically saying with the????? Hello Tom And thank you for your quick answers. I am after the sql text of transactions that are performing rollback. The application developpers use a tool to generate and manage the sql text develloped, therefore they don't see the sql text.
It is my task as a DBA to find the text of the transactions that are rolling back. Either this method doesn't work or my implementation is wrong. I intend to run another test. This seems to be a very labour intensive way of solving my problem of finding the text of sql that is rolling back.
Additional information: The application opens several sessions and connects using multiple users to complete the work. My question is: Can you think of any other way of capturing the text of sql that is rolling back? April 30, - am UTC. You cannot get the sql they used in that "transaction".
I love those tools that "protect" those poor developers from the dreaded SQL thing -- i understand it is really far too complex I feel sorry for you -- really I do. Why is it your task as DBA? It might be your task to alert the developers to a performance issue rolling back but it would be the development staffs job to fix their BUG wouldn't it.
A reader, May 01, - am UTC. Hi Is there a way to know how much rollback do I need for a transaction? If we must run it in order to know it, is it possible to extrapolate? Or even better create an empty copy of original table, insert rows there and run the update totally and extrapolate from there?
Or is this totally out of question :? May 01, - pm UTC. Extrapolation is a "maybe" idea. If the table has no indexes or the columns being modified are not indexed , extrapolation should be workable.
Indexes are the unknown variant here. How about a null taddr? Doug, June 12, - pm UTC. I'm trying to drop a schema that is "currently connected" even though all the sessions connected to it are marked for "KILL".
Why would that be? June 12, - pm UTC. I have a flag which shows a statement which is a huge select for update statement. The session, marked for kill, is holding about 13 different locks, many of them TOs on temp tables.
If this transaction is not rolling back, what is it doing? An online index creation on the same table appears to blocked on an enqueue waiting for it. June 28, - pm UTC. Well - as you mentioned - this situation has resolved itself. I took the nasty route of killing the dedicated server process to speed things along. I know you hate that. The java app was doing absolutely nothing, and there were only 3 connections to the entire database.
Mine, the one that was killed, and the one trying to build the index. I didn't think to look at session event. What would you suspect?
What about a session killed? So, how to follow the rollback make for this session? In addition, who executes this rollback? The server process dedicated or shared or a background process? Thanks Michel. July 21, - am UTC. If not set explicitly, the default value for this parameter is low. This article is an English version of an article which is originally in the Chinese language on aliyun. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof.
If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact alibabacloud.
A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately. The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud.
If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to: info-contact alibabacloud.
0コメント