Oracle Tips and Tricks — David Fitzjarrell

May 20, 2015

That Depends

Filed under: General — dfitzjarrell @ 11:00

“They all looked very much like the residents of any small valley to which you've never been.” 
― Norton Juster, The Phantom Tollbooth 

In every universe there are questions that either:

Have no answer 
Have an answer that is situation-dependent

and that includes the Oracle universe. Sometimes it’s difficult to get that concept across to the less knowledgeable. Let’s go back to a simple example from a previous post to illustrate this.

In Resurrecting The Dead flashback operations were discussed as an option to restore table data to before an errant update. The post started with this:

Occasionally an end-user does the unthinkable, and changes data permanently (or so it appears) without checking the results before issuing a commit. Ooops. 
It used to be that to restore that data one had basically two choices:

A) Restore the data from a known good copy made before the changes were enacted
B) Restore the table from the last known good export

Yes, there was a third choice, an incomplete tablespace recovery but that presumed the database to be in ARCHIVELOG mode and, gasp, there are databases 
running in NOARCHIVELOG mode where this wouldn't work.

We have a situation where data was updated and now needs to be reverted back with the question being “How do I do that?” And, this is one of those situation-dependent answers, the situation being the version of Oracle in use. If the user was using any version prior to 9.0.x there wasn’t much choice except to follow the path laid out at the start of the post. If that situation changed, and the version was either 9.0.x or 9.2.x, another solution presented itself:

So, what is a DBA to do? If you're running 9i you can enable flashback query by using the dbms_flashback package, which was nice but rather cumbersome 
in implementation, as it only allowed you to query the flashback data while enabled so updates to existing tables (to restore known good data) were 
somewhat convoluted in execution; it was necesary to enable flashback mode, open a cursor, then disable flashback mode to effect the updates:
  -- Cursor to fetch the required data
  cursor c1 is
  select flerg, snerm, anguplast
  from snangpiester;

  -- A place to stash each row of the returned data
  c1_rec c1%rowtype;


  -- We flashback the data image to before the problem began
  dbms_flashback.enable_at_time(to_timestamp('16-SEP-2007 06:53:00 AM','DD-MON-RRRR HH:MI:SS AM'));

  -- Open the cursor while in flashback mode
  open c1;

  -- Disable flashback mode so we can update the problem table

  -- Go fetch the good data and update the problem table
          fetch c1 into c1_rec;
          exit when c1%notfound;  -- say goodbye when there's no more data
          update snangpiester
          set anguplast = c1_rec.anguplast
          where flerg = c1_rec.flerg
          and snerm = c1_rec.snerm;
  end loop;

  -- Commit the changes



So another possible solution presents itself. And there is a third solution, for those using version 10.1.x or greater, that was even more convenient. Let’s leave it to you to follow the provided link and read the original post. The point to be made is that some seemingly simple questions aren’t so simple to answer, since they depend on any number of conditions. This is why it is so important to provide the Oracle version in use as it may change a seemingly stock answer. Providing incomplete information can generate the dreaded response: “It depends.”

Questions that fall into this category include, but are not limited to, those on backup and recovery, databasec cloning, data replication and tuning. Each release of Oracle improves on the previous release, including how the optimizer works. Upgrades can improve or degrade performance based on the changes made to the optimizer so questions like “how can I make this query faster?” have no hard-and-fast answer, even though on the face of it the question seems simple.

I can’t count how many times I’ve seen or given the “It depends.” answer and I’m fairly certain that the person asking the question believes that response is forum-speak for “I don’t know.” Nothing could be further from the truth since “It depends.” is usually followed by the responder requesting more information so he or she has a fighting chance of giving a correct answer. Providing relevant details ‘up front’ makes it easier for forum participants to assess the situation and provide a usable response. “It depends.” isn’t a ‘cop-out’ (to use an ancient colloquialism coined before the dawn of time), it’s an indication that the question needs more information before it can be answered. It also means the forum participants care enough to give you an accurate answer, one you can actually use. Remember that they are taking time to participate in the forum; you should also take time to think through your question and provide as much detail as possible. Every little bit helps.

“How do I …” Well, it depends …


1 Comment »

  1. […] enough to generate a usable answer. Another response you might encounter for ambiguous questions is “That depends…”, and that isn’t a red flag of ignorance, it’s the volunteers trying to get you to be […]

    Pingback by “It Doesn’t Work” | Oracle Tips and Tricks -- David Fitzjarrell — June 14, 2016 @ 17:09 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at

%d bloggers like this: