2

I apologize if this is too vague, but it is a random issue that occurs with many types of statements. Google and Stack Overflow searches have failed me. Here is what I am experiencing, I hope that someone out there has seen or at least heard of this happening and possibly knows of a solution.

From time to time, with no apparent rhyme or reason, statements that I run through PL/SQL Developer against our Oracle databases do not "stick". Last week I ran an update on table A, a commit for the update statement, then a truncate on table B and an insert to table B followed by another commit. Everything seemed to work fine, as in I received no errors. I was, of course, able to query the changes and see that they were made. However, upon logging out and then back in, the changes had not been committed. Even the truncate command had not worked "stuck" - and truncates do not need a commit performed.

Some details that may be helpful: I am logging into the database server through PL/SQL on a shared account that is used by my team only to gain access to the schema (multiple schemas on each server, each schema has one shared login/PW). Of the 12 people on my team, I am the only one experiencing this issue. I have asked our database administration team to investigate my profile setup and have been told that my profile looks the same as my teammates' profiles. We are forced to go through Citrix to connect to our production database servers. I can only have one instance of PL/SQL open at any time through Citrix, so I typically have PL/SQL connected to several schemas, but I have never been running SQL on more than one schema simultaneously. I'm not even sure if that's possible, but I thought I would mention it. I typically have 3-4 windows open within PL/SQL, each connected to a different schema.

My manager was directly involved in a case where something similar to this happened. I ran four update commands, and committed each one in between; then he ran a select statement only to find that my updates had not actually committed.

I hope that one of my fellow Overflowers' has seen or heard of this issue, or at least may be able to provide me with a direction to follow to attempt to get to the bottom of this.

MKowalski
  • 37
  • 2
  • 7
  • Share some examples of your problem. – XING Aug 30 '16 at 15:19
  • @MKowalski: Why dont you try with sqlplus using command prompt before we can blame PL/SQL Developer? – Gaurav Soni Aug 30 '16 at 16:01
  • If you've blamed PL/SQL Developer (rightly or wrongly) yet continue to use it, you are ultimately to blame. The top 2 Oracle development environments (imo) are [Toad](https://software.dell.com/products/toad-for-oracle/software-downloads.aspx) and Oracle's [SQL Developer](http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html) . I'd start with the free SQL Developer. – tbone Aug 30 '16 at 16:26
  • 3
    @tbone - we don't always have the option to choose which IDE to use. For what it's worth, I've found PL/SQL Dev to be a highly fine tool for PL/SQL programming. – APC Aug 30 '16 at 16:37
  • 1
    Just curious: Are you executing these statements as a script? Or as a single command, then hitting the commit button? You may want to setup your updates as a script with explicit commits. Also, hopefully your table schemas have modified_date and modified_by fields which you are updating. If so, make sure nobody else is stepping on your changes. – tbone Aug 30 '16 at 16:41
  • @APC true, I'm spoiled I guess ;-) But if I was really having an issue with a tool (again, not blaming pl/sql developer, but as a general rule), AND there was a FREE option from Oracle itself, I would at least try to push for it (don't see why this would cause an issue really). – tbone Aug 30 '16 at 16:44
  • 1
    The statement that really sticks out to me is when you say that you truncate a table, see it take effect, then log out and back in, and the changes are undone?!? I really don't see how that could happen. – sstan Aug 30 '16 at 17:52
  • 2
    Do you use the PL/SQL Developer feature for multiple connections? I used it briefly but stopped because I feared issues like this might happen. It's hard to keep track of multiple connections where each tab could be connected to a different database, and those tabs can move around. Instead, I disabled the feature and just have one connection per window. PL/SQL Developer is lightweight so it's easy to run several instances of it at the same time. Then the correct database name is always at the top of the window and in the notification bar. – Jon Heller Aug 30 '16 at 21:16
  • @tbone I work for a multi-national corporation. I have no choice which tools I have access to. To answer your other question, I do not write the SQL, it is written my the developers who pass it on to my team (Ops) who run it under a shared admin-type account. We do have modified dates, and we track ticket numbers as well. My team is small enough that we would know if anyone or anything was also accessing the same tables. – MKowalski Aug 31 '16 at 13:13
  • @GauravSoni - see my previous comment. I have no other options for tools. They have limited our access to our servers and can only use PL/SQL to access the databases. – MKowalski Aug 31 '16 at 13:14
  • @JonHeller - Due to the fact that my company is forcing us to use Citrix to connect to our production databases, and they have Citrix set up to not accept multiple instances I have no choice but to use one PL/SQL instance to connect to multiple databases. However, as I stated above we do not have to run this type of SQL terribly often. I could close out of PL/SQL and log back into a new instance each time I need to change DBs. – MKowalski Aug 31 '16 at 13:18
  • MKowalski: i am asking you to open your command prompt in windows,then type `sqlplus /nolog` ,after that type `conn @` and then your into sql prompt and then type your query – Gaurav Soni Aug 31 '16 at 13:27
  • @GauravSoni - I understand what you are asking, however I cannot do that. We are required to use Citrix to connect to our production databases. PL/SQL Developer is not even installed on my local PC, so attempting to run that command produces the error that the command "sqlplus" does not exist. Even if it did, there is no way for my PC to connect to the database. – MKowalski Aug 31 '16 at 13:54
  • @MKowalski You might want to edit the question and add some details about Citrix. It could be a problem with Citrix - maybe it's somehow sharing configuration files when it shouldn't? – Jon Heller Aug 31 '16 at 14:37
  • @MKowalski, what was the answer to JonHeller's question about the setting for "Tools > Connection > Allow multiple connections"? – William Robertson Sep 01 '16 at 09:21
  • 2
    You're connecting to **Production** databases? Surely not. I don't know which is the most messed up: using an IDE to manipulate data in live systems or the fact that nobody else in your organisation can be bothered to investigate deep weirdness in their live systems. – APC Sep 01 '16 at 13:06
  • I think this is probably the cause of the intermittent problem: *"I typically have PL/SQL connected to several schemas, ... I typically have 3-4 windows open within PL/SQL, each connected to a different schema."*. This situation is ripe for occasional slipups. Tighten up your deployment steps so that you can verify, log and document that you are connecting to the right schema on the right server every time. I wouldn't trust myself to run various scripts and commands with multiple windows and sessions open on **production** databases! – Jeffrey Kemp Sep 02 '16 at 17:22

4 Answers4

4

"it has begun to reflect poorly on me and damage my reputation in the company."

What would really reflect poorly on you would be you believing that an Oracle RDBMS is a magical or random device, or, even worse, sentient and conducting a personal vendetta against you. Computers may seem vindictive but that is always us projecting onto them ;-)

The way to burnish your reputation would be through an informed investigation of the situation. Databases do not randomly lose transactions. So, what is going on?

Possible culprits:

  1. Triggers: does table A have an UPDATE trigger which suppresses some of your SQL?
  2. Synonyms: are tables A and B really the tables you think they are?
  3. Ownership: are these tables in another schema which has row level security enabled (although that should through an error message if you violate a policy)?
  4. PL/SQL Developer configuration: is the IDE hiding error messages or are you not spotting them?
  5. Object types: are tables A and B really tables? Could they be views with INSTEAD OF triggers suppressing some of your SQL?
  6. Object types: or could A and B be materialized views and your session has QUERY_REWRITE_INTEGRITY=stale_tolerated?

If that last one seems a bit of a stretch there other similarly esoteric explanations, involving data flashback, pipelined functions and other malarky. This a category of explanation which indicates a colleague is pranking you.

How to proceed:

  1. Try different tools. SQL*Plus (or the new SQL Command Line) may produce a different outcome. Rule out PL/SQL Developer.
  2. Write some test cases. Strive to establish reproducible test cases: given a certain set-up this SQL statement always leads to a given outcome (SQL always sticks or always does not).
  3. Eliminate bugs or "funnies" in the queries you use to check the results.
  4. Use the data dictionary to understand the characteristics and associated objects of the troublesome tables. You need to understand what causes the different outcomes. What distinguishes a row where the UPDATE holds compared to one where it does not?
APC
  • 144,005
  • 19
  • 170
  • 281
  • nice list of possibilities, esp the triggers – tbone Aug 30 '16 at 17:03
  • Thank you for the detailed write up. The SQL that we run is run against the same tables all the time, no matter which one of us runs it. This issue is only affecting me and is very random. I can run the SQL one day and it works fine. The next day, the same SQL will apparently run properly but the changes do not stick. I have been unsuccessful in reproducing the issue. I have had our development team check my IDE setup, it is the same as theirs. The reputation affect has to do with me marking things as complete, but the commands not sticking. "They" think I am willfully not doing my job. – MKowalski Aug 31 '16 at 13:28
  • Our databases are administered by the DBAs at our parent company so I'm not even sure I could find out how they are set up. I am not, by any stretch of the imagination, a SQL developer. I just run the code that is given to me by those who know how to write it. The objects are most definitely tables. The biggest problem with figuring out why this is happening is that I never know when it is going to happen. SQL that I run right now might not work, but if I run the same SQL five minutes later from the same window it works. Highly frustrating. – MKowalski Aug 31 '16 at 13:50
1

I have used PL/SQL Developer for over a decade and I have never known it silently undo successful truncate operations. If it can do that, AA should add it as a menu item. It seems more likely that you ran the commands against the wrong database connection.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
  • Everyone here has been using PL/SQL for years. No one has ever seen this happen before. The only reason I blame PL/SQL is because I do not know what else it could be. We have already reset my SQL profile, we have reset PL/SQL, we have even reset my Citrix profile despite the fact that this issue predated our parent company forcing Citrix on us. I wish it were as simple as just being logged into the wrong database. – MKowalski Aug 31 '16 at 13:35
  • Sorry - I should say "Everyone at my company has been using PL/SQL for years." – MKowalski Aug 31 '16 at 13:47
  • 1
    Or even, "Everyone at my company has been using _PL/SQL Developer_ for years." – William Robertson Aug 31 '16 at 14:03
  • My point is, there is no such thing as "sticking", and `truncate` operations can't be undone by a bug in a desktop tool after you have confirmed that they worked initially. – William Robertson Aug 31 '16 at 14:05
  • I know that. You know that. It has happened to me twice. Can you begin to see why I'm so frustrated? – MKowalski Aug 31 '16 at 17:09
1

I can feel your frustration, sorry you're going through this. I am surprised, however, that at a large company, your change control process is like this. I don't work for a large multi-national company, but any changes done to a production database are first approved by management and run by the DBAs (or in your case, your team). Every script that is run does a few things:

  1. Lists the database instance information its connecting to. For example:

    select host_name, instance_name, version, startup_time from v$instance;

  2. Spools the output to a file (the DBAs typically use sqlplus, but I'm sure PL/SQL Developer can do the same)

  3. Shows the current date and time (in the beginning and end of the script)

  4. The output file is saved to a change control server (the directory structure makes it easy to pull any changes for a given instance and/or given timeframe)

  5. Exits on any errors:

    WHENEVER SQLERROR EXIT SQL.SQLCODE

  6. Any additional checks that need to be run post script (select counts, etc)

  7. Shows each command that is being run (set echo on), including the commits!

All of this would allow you to not only verify that the script was run successfully, but would allow you to CYOA. Perhaps you can talk with your team about putting some of this in place in your own environment. Hope that helps.

tbone
  • 15,107
  • 3
  • 33
  • 40
0

I have no way of knowing if my issue is fixed or not, but here is what I've done: 1. I contacted our company's Citrix team to request that they give my team the ability to have several instances of PL/SQL open. This has been done and so will eliminate the need for one instance with multiple DB connections. 2. I contacted the DBA's and had them remove my old profile, then create a new one with a new username.

So far, all SQL I've run under these new conditions has been just fine. However, I have no way of recreating the issue I'm experiencing so I am just continuing on about my business and hoping for the best.

Should I find a few months from now that I have not experienced this issue again I will update this post in case anyone else experiences it.

Thank you all for the accusations of operator error (screenshots prove that this is not operator error but why should you believe me when my own co-workers have accused me of faking the screenshots) and for the moral support.

MKowalski
  • 37
  • 2
  • 7