Gotcha: Materialized Views (MView) in Oracle

My DBA friend forwarded me a message from a user last week! It was essentially saying that some report wasn’t showing updated information. There was some issue with the same in the past, so the user forwarded a message that referred to a table, let’s say temp_data table. It was created couple of years ago and seem to be getting loaded daily.

My friend was essentially handing it over to me, a developer, to find and fix the issue. Having seen some references to the table in past emails, I searched and found a Unix script that seemingly loaded the table. I decided it was probably run as a crontab job in Unix, so handed it back to her, saying they probably did it or need to work with the Unix admin to check the cron tab. Negative! She came back in a jiffy saying, “no crontab entries exist, since we did the Oracle 11g upgrade couple of years ago”. Hmm. This was turning into a mystery and a typical DBA vs Developer duel. If we didn’t do it, how is this table still being loaded?

In situations like these, I often end up digging it up and quite often find hidden gems in our application! This was yet another find! Whenever in doubt, I resort to Oracle dictionary tables (meta data). I did the following query:

SELECT * FROM all_objects WHERE table_name = 'TEMP_DATA';

Boy, was I surprised! I went in expecting to find one object – the table named TEMP_DATA, but there were two objects with the same name, a table and a materialized view (MView). Now the problem is completely different! When I started with the data issue in the morning, I had a shell script and a SQL on my hand, that loaded the table. Now, I see a MView. Who created this MView and how in the world is this getting loaded?

And more than that I got curious as to why there was still a table with the same name! I’ve seen and used Materialized view before in programs, but my understanding of the MView design was patchy. Did someone leave the old table in there and created MView with the same name? Is that even possible? I posted on Stackoverflow, a site I respect for all technical questions. See the below link for the exchange I had, with couple of experts there.

http://stackoverflow.com/questions/33552237/materialized-view-and-table-with-the-same-name

After that post and some more Googling, it started unraveling. Creating an MView in Oracle, actually creates the underlying table with the same name. That’s how it persists the data. And oh yes, it doesn’t refresh itself. There was a scheduled Job in Oracle to refresh the MView daily automatically. To see a list of scheduled jobs in Oracle, you can again go to Meta Data:

SELECT * FROM dba_jobs WHERE schema_user = '<SCHEMA>';

Here are there are last_date and time, next_date and time and interval columns that display when the job is run. And the “What” column shows what is being run:

dbms_refresh.refresh('"​SCHEMA​"."TEMP​_DATA​"');

Got back to the DBA with all the information and that jogged someone’s memory!!!! Apparently, someone in DBA team, replaced the old script/SQL approach to an Oracle MView/Job to load the table and forgot all about it. This is what happens when people rely on their memory and not proper audit trail!! In situations like these, proper Standards and documentation become so important. We developers normally have to jump through hoops to get a script into production, but the DBAs, with the full authority they have on the database, they sometimes, forget to follow their own protocol or simply bypass it! For once, we were able to turn the tables on them :). And the duel continues!

Advertisements

Comments, please?

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

WordPress.com Logo

You are commenting using your WordPress.com 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