|
Replies:
41
-
Pages:
3
[
1
2
3
| Next
]
-
Last Post:
Feb 24, 2010 8:40 AM
by: lynn.olpin
|
|
|
Posts:
7
Registered:
12/1/09
|
|
|
|
Strange Behavior in Toad 9.7.2.5
Posted:
Feb 19, 2010 12:22 PM
|
|
|
I just had a coworker call me over to her desk and show me an insert script she
had tried to run (10000 lines). When ran it in Toad 9.7.2.5, the insert script
modified itself and added the following code between each insert statement.
BEGIN DBMS_LOCK.SLEEP(1); END;
Has anyone else seen this or possibly know why this happened? I have never seen
this before.
Thanks,
Lynn Olpin
E-Mail: Lynn.Olpin@hill.af.mil
|
|
|
Posts:
356
Registered:
10/30/09
|
|
|
|
RE: Strange Behavior in Toad 9.7.2.5
Posted:
Feb 19, 2010 12:37 PM
in response to: lynn.olpin
|
|
|
We don’t have that in our code anywhere. You don’t have that in a
code template or something like that do you?
|
|
|
Posts:
7
Registered:
12/1/09
|
|
|
|
RE: RE: Strange Behavior in Toad 9.7.2.5
Posted:
Feb 19, 2010 1:25 PM
in response to: jdorlon
|
|
|
No. That what is so weird.
I don't think that Toad did it, something else must have happened.
Thanks,
Lynn
|
|
|
Posts:
291
Registered:
5/19/06
|
|
|
|
RE: Strange Behavior in Toad 9.7.2.5
Posted:
Feb 19, 2010 1:44 PM
in response to: jdorlon
|
|
|
Maybe someone added that line to the login.sql or glogin.sql and that’s
how it’s getting added (see toad options)
|
|
|
Posts:
68
Registered:
2/5/09
|
|
|
|
RE: RE: Strange Behavior in Toad 9.7.2.5
Posted:
Feb 19, 2010 4:11 PM
in response to: lynn.olpin
|
|
|
waiting (a minimum of) 1 second for a DML lock is'nt really all that unusual
(unless of course you're bulk-inserting which apparently is not the case here)
Norm/Roger or anyone know why this would be the case?
Martin
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.
|
|
|
Posts:
323
Registered:
2/4/09
|
|
|
|
RE: RE: Strange Behavior in Toad 9.7.2.5
Posted:
Feb 22, 2010 1:46 AM
in response to: MGainty
|
|
|
Morning all,
>> waiting (a minimum of) 1 second for a DML lock isn't really
>> all that unusual (unless of course you're bulk-inserting which
apparently is
>> not the case here)
>>
>> Norm/Roger or anyone know why this would be the case?
Calling DBMS_LOCK.SLEEP(1) is not waiting one second for a lock, it is
inserting a pause of one second between INSERTs.
Strangely enough, I came across something like this very 'problem' just
last week. It made me so angry!
I had a release on a system which had (random) commits all the way
through it, or so it appeared. IN light of our SQL scripting standards,
I advised the people responsible and removed the commits. The script
failed with duplicate PK values (in a trigger), so I abandoned the
release, rolled back and advised the developers of the problem.
Here's the laugh:
Updating the table resulted in the firing of a trigger to create a
history record in another table. The PK on the history table is the
updated table's PK plus a DATE. (Who can see the problem yet?)
So, when we run on a s-l-o-w server all is well. When we get onto a fast
one, the resolution of the DATE column is too large and allows duplicate
values. The solution from the Duh-veloper(s) in this case was not "lets
investigate and fix it" but "lets bung in a few commits to slow it down"
- so the actual transaction control went out the window. Sensible or
what?
I was told (dangerous!) to put the commit's back into the script exactly
as they were and run it again. I refused. 
I was told that the script was running too fast and they commits helped
slow it down so that there were no duplicate values. Put the commits
back in!
I refused and pointed them at DBMS_LOCK.SLEEP() and lo, we were all
happy! Well, I was happy-ish as I also told them about a TIMESTAMP data
type which has micro-second resolution and even running in a tight loop,
can't generate duplicate values.
Anyway, the use of DBMS_LOCK.SLEEP(1) could be because the rows are
being inserted into a table that has a DATE column as part of its PK (or
some unique constraint) and the sleep is to ensure that no two rows are
inserted with the same SYSDATE value.
Maybe?
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
|
|
|
Posts:
451
Registered:
8/10/06
|
|
|
|
RE: RE: Strange Behavior in Toad 9.7.2.5
Posted:
Feb 22, 2010 5:32 AM
in response to: Norm [TeamT]
|
|
|
The lunatics are running the asylum again.
Why wouldn’t they consult a DBA for help when these types of design
challenges come up?
|
Follow me on twitter @hillbillyToad
Check out my blog on ToadWorld
|
|
Posts:
323
Registered:
2/4/09
|
|
|
|
RE: RE: Strange Behavior in Toad 9.7.2.5
Posted:
Feb 22, 2010 7:17 AM
in response to: HillbillyToad
|
|
|
Hi Jeff,
>> The lunatics are running the asylum again.
As ever!
>> Why wouldn't they consult a DBA for help when these types of
>> design challenges come up?
Consulting a DBA is a task that is only (ok, usually only) carried out
when all else fails.
We have "architects" who decide on how an application will be built etc
etc, and they "know it all" and don't need to talk to the people who
will have to clean up the mess when it hits the fan at go live time!
(Ask me how I know!)
The problem is that in testing the app, they never made frequent
(enough) changes to the same row to be able to cause the duplicates, but
on running a maintenance script in plain SQL, they did.
So, rather than think, the simply added commits. It does make me wonder
what other "goodies" are hidden away waiting to bite someone.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
|
|
|
|
|
|
|
RE: RE: Strange Behavior in Toad 9.7.2.5
Posted:
Feb 22, 2010 7:22 AM
in response to: Norm [TeamT]
|
|
|
Norm!
> I refused and pointed them at DBMS_LOCK.SLEEP() and lo, we were all
> happy! Well, I was happy-ish as I also told them about a TIMESTAMP data
> type which has micro-second resolution and even running in a tight loop,
> can't generate duplicate values.
Then you need newer hardware! 
Rich -- [TeamT]
Disclaimer: [Carefully insert TeamUSA reference here]
|
|
|
|
|
|
|
RE: Strange Behavior in Toad 9.7.2.5
Posted:
Feb 22, 2010 8:44 AM
in response to: lynn.olpin
|
|
|
Greetings Lynn;
>> BEGIN DBMS_LOCK.SLEEP(1); END;
Sorry this response is a bit late, I was on holidays all last week.
Question of curiosity: what do you mean by “the insert script modified
itself”?
Do you mean:
|
|
|
|
|
|
|
RE: RE: Strange Behavior in Toad 9.7.2.5
Posted:
Feb 22, 2010 9:12 AM
in response to: Norm [TeamT]
|
|
|
They've never heard of using a Sequence for that part of the history
key??? (Or use a Sequence as the entire PK of the history table, with a
non-unique index on other-key-plus-date.)
Nate Schroeder
IT Commercial Technical Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. G3WI - Saint Louis, MO - 63167
314-694-2592
|
|
|
Posts:
7
Registered:
12/1/09
|
|
|
|
RE: RE: Strange Behavior in Toad 9.7.2.5
Posted:
Feb 22, 2010 10:24 AM
in response to: HillbillyToad
|
|
|
Yes, the DBMS_LOCK.SLEEP(1) does work well. Our problem was that the
script did not have the "Sleep" function in it. My coworker loaded the
script (without the DBMS_LOCK.sleep(1)) function, ran it and the
DBMS_LOCK.sleep(1) appeared between each insert. Must be gremlins.
Thanks for the find and replace functionality. I just had her find the
string and replace it with nothing to put the script back.
My coworker is not here today so I have not been able to see if it has
happened again.
Lynn
|
|
|
Posts:
7
Registered:
12/1/09
|
|
|
|
RE: Strange Behavior in Toad 9.7.2.5
Posted:
Feb 22, 2010 10:31 AM
in response to: Simoneau, Roger
|
|
|
Sorry for the confusion.
Here are the steps.
My coworker opened the script in the SQL Editor and verified the script (no
“BEGIN DBMS_LOCK.SLEEP(1); END;” statements in the script.)
She then ran the script. The “BEGIN DBMS_LOCK.SLEEP(1); END;”
appeared in the script between each insert statement.
She noticed the changed script and called me over. I have verified that the
“BEGIN DBMS_LOCK.SLEEP(1); END;” did not exist in the original
script.
Somehow, the script was modified while running.
|
|
|
Posts:
82
Registered:
10/30/09
|
|
|
|
RE: RE: Strange Behavior in Toad 9.7.2.5
Posted:
Feb 22, 2010 10:38 AM
in response to: lynn.olpin
|
|
|
In addition to what John noted earlier that DBMS_LOCK.SLEEP doesn't occur anywhere in the source, the Editor will prompt when a file has been modified outside of Toad. So, if the script were loaded from a shared resource and if it were edited by someone else the Editor will prompt to reload the modified file. It would be very easy to click yes or hit return and get whatever modifications were made elsewhere.
Michael
|
|
|
Posts:
291
Registered:
5/19/06
|
|
|
|
RE: Strange Behavior in Toad 9.7.2.5
Posted:
Feb 22, 2010 11:03 AM
in response to: lynn.olpin
|
|
|
Did you check the login.sql and glogin.sql files pointed to by toad options and
those files contents???
I asked earlier and no reply….
|
|
|
|
Legend
|
|
Guru: 2001
+
pts
|
|
Expert: 751
- 2000
pts
|
|
Enthusiast: 31
- 750
pts
|
|
Novice: 0
- 30
pts
|
|
Moderators
|
|
Helpful answer
(5 pts)
|
|
Answered
(10 pts)
|
|
