Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually.
Related Hot Network Questions. Question feed. Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise.
Collectives on Stack Overflow. Learn more. Asked 10 years, 9 months ago. Active 10 years, 9 months ago.
Viewed 2k times. Is there a good way how experienced programmer would accomplish this? That function will be called from. Improve this question. Add a comment. Active Oldest Votes.
Improve this answer. Mat Mat k 39 39 gold badges silver badges bronze badges. A reader, November 22, - pm UTC. Will, December 06, - am UTC. Hi Tom, I've learned a lot about locking from reading your books. BookingPK; Does that result in an update of the primary key? It certainly looks like it. Would you consider this to be an unsound way to use rowtype? How could I improve on it? December 06, - am UTC. If you have an unindexed foreign key, this'll be an issue concurrency wise.
Dynamic cursor using a db link ht, November 03, - pm UTC. Tom, Is it possible to pass a db link into a stored proc to return usernames in that instance? Hi TOM, I want to delete from one table which is taking too much time. Its primary key is used in many tables as foriegn key and all table are indexed. Please tell me how to reduce time. November 11, - pm UTC. You don't tell me why it needs to go faster is it blocking something?
If not, why do you care? Dear Tom, The query you posted in this link for finding the unindexed foreign keys is working fine in 10g but failing in 9i with the following error "ORA not a group by expression. December 14, - am UTC. HI Tom. I have noted that the contents of the instead-of-trigger itself is not material to the loss of locking.
As such the following trigger is sufficient to demonstrate the situation. Said another way, is it thus so that select for update will not acquire row locks when the select is issued against a view with instead of triggers, or have I made a mistake somewhere in my work? January 04, - am UTC. I can't argue with your example. I'll check my test cases and see what I was doing wrong. Must have been a real stupid error on my part what other kind are there? Thanks, this makes me feel way better.
Tom, Your script above lists the foreign key columns even though they are part of a composite index. Isn't it suffice to just have the column part of some composite index instead of an index just for itself. March 18, - pm UTC. March 26, - pm UTC. If you have two sessions that are both updating the same rows, and they haven't even read the data out, don't you also have a serious issue with lost updates? This is a transaction which calls itself recursively when it fails, and is in a deadlock state with another transaction which also calls itself recursively when it fails.
That is, is there a limit when 2 such deadlocked procedures will give up? Is this because of recursive calls or deadlocks? What I am seeing from the tests below is that On Oracle 10g the number of ORA errors recorded before these procedures abort completely with ORA is significantly less than the number of errors recorded in Oracle 11g. For Oracle 11g with K ORA errors in the logs For Oracle 10g with 5K ORA errors in the logs What inspired me to investigate this, is failures I got in batch jobs after running the same recursive procedure in 10 different sessions simultaneously updating the same table kind of DIY parallelism which caused deadlocks.
Normally these 10 procedures, would complete the batch job and update the table even when producing deadlocks. When I look at the logs I see that they fail when they produce excessive amount of deadlocks? Is there a limit in Oracle on the amount of ORA errors that it could generate, before giving up? Thank you very much for all your help.
June 09, - am UTC. Where are your rollbacks? If you get the deadlock on the second update, the first update will have "worked" and won't have rolled back at all. You'll be doing the same exact work over and over and over again. I'm always confounded by this sort of a question - basically "an error happens, we know it is because of a design flaw in our application, but how can we make Oracle ignore this error so our flawed thing - which we admit is flawed - can run to completion a few hours or days from now" and recursion doesn't seem to be the way to go here for i in Potentially infinite recursion such as you have is bad because: o it is potentially infinite.
You have programmed an infinite loop without using a loop Your logic needs to be reworked as it quite simply has zero chance of working in real life. Tom: Is this an internal bug or something with script? Any workarounds? February 15, - pm UTC. Chandu, June 15, - am UTC.
Tom ,I'm a rookie in terms of posting if there is any mistake in my post that is not intentional. Below is my stored procedure which is call by an applivation.
June 17, - pm UTC. Where A. The two records it gets will match the where clause - but they WON'T be the oldest records except by pure accident. I'm not surprised this gets deadlocks. Each process would get some random set of records and then start processing them - they are processing similar data - some of the same rows. You don't think that the R2 and R1 cursors contain the same result set do you?
Please say no Tell us, in english - not code that has some questionable things in it, exactly and precisely what the specification of this routine is to be.
Then we can tell you how to do it.
0コメント