SQL

September 1, 2010 Today’s blog article provides a graphical view of production work areas on a factory floor, providing feedback to indicate when the production area is in use.  The blog article includes a classic ASP web page which uses VBScript to write the web page on the fly to the browser on the client [...]

August 8, 2010 (Modified August 8, 2010, August 10, 2010) I am not much of a supporter of True or False type questions, nor do I much care for multiple choice type questions.  It seems that essay questions are usually the only appropriate type of questions on exams.  Take, for example the following question that [...]

July 29, 2010 Another interesting thread appeared on the OTN forums this week.  The original poster in the thread stated that Oracle Database was performing a full table scan for a SQL statement like this: SELECT COUNT(*) FROM T1 WHERE COL1 IS NULL; While a SQL statement like this following did not perform a full [...]

July 27, 2010 A question in an email from an ERP mailing list combined with Cary Millsap’s latest blog article inspired this blog article.  The question from the ERP mailing list asked the following question: Does anyone have Oracle syntax for the ‘upsert‘ command?  I have found a few examples, but little success yet. Using VB.net, I [...]

July 8, 2010 (Modified July 9, 2010) I thought that I would try something a little different today – build an auto-scaling HTML bar chart using nothing more than a SQL statement.  I mentioned in this book review that I was impressed with the HTML chart that was included in the book, but I felt that [...]

Last week I blogged a piece all about DBA_TAB_MODIFICATIONS and how it captures all normal DML against table. However, a word of warning. This may not be true for multi-table inserts. It appears that inserts via multi-table inserts are not recorded in DBA_TAB_MODIFICATIONS for 10.2.0.3 and a couple of 9.2 versions of oracle.

{My thanks got to my comrade-in-arms Basavaraj Patil for this. He not only noticed the issue but tracked it down to multi-table inserts, came and told me about it and then said I could put it in my blog. This is his work really}.

I will not go into multi-table inserts right now but it is a very nice feature that was introduced with oracle 9 which allows you to insert a row into several tables at a time or into one of different tables. In fact, I think it is a relatively unknown feature. Check out this oracle-base article on new 9i features that mentions it or go pull up the Oracle 10 SQL reference manual.

There is a bug with Oracle up to 10.2.0.3 and 9.2.0.6 and 9.2.0.7.
It is fixed in 9.2.0.8, 10.2.0.4 and 11.1.0.6
Bug number 4572043 it you have metalink.
It impacts direct load merge statements as well as multi-table inserts, but I have not tested and verified the direct load merge statement.

Below is a demonstration of the issue:

I’ve mentioned DBA_TAB_MODIFICATIONS (and there is also of course ALL_TAB_MODIFICATIONS and USER_TAB_MODIFICATIONS) in a couple of posts, but mostly buried in long, rambling posts about other things. So here is an overview and demonstration of DBA_TAB_MODIFICATIONS. Skip to the end if you just want to see an example of how it works.

The below example is from 10.2.0.3 but for V11 it is pretty much the same, as is 9 except where I say otherwise.

SYS.DBA_TAB_MODIFICATIONS is used by oracle internally to track how many inserts, updates and deletes have been done to a table, table partition or table subpartition since the segment last had stats gathered on it with dbms_stats. It also records if the segment has been truncated since the last stats gather. Why since the last stats gather? Because Oracle uses it internally to detect how much a segment has changed and if it is 10% or more oracle will deem it stale for stats gathering purposes. (under 11 you can alter the stale percentage). Note I said SYS.DBA_TAB_MODIFICATIONS – on 10 at least the public synonym for it is missing, so unlike most other DBA_ views you need to state the owner (or create a synonym).

June 4, 2010 Have you ever read about something, or heard about something, and wanted to be able to reproduce it?  Have you ever been warned that doing something is not a good idea because a specific problem is certain to happen, yet you do it anyway just to see if the problem can be [...]

June 4, 2010 I encountered an interesting test case in the “Oracle SQL Recipes” book, but I fear that my degree in mathematics is causing me to fail to fully comprehend the test case.  I developed a parallel test case that possibly answers the questions that are left unanswered.  Here is my test case: CREATE TABLE [...]

June 3, 2010 I came across an interesting SQL statement that is described as identifying blocking transactions: select s1.username blkg_user, s1.machine blkg_ws, s1.sid blkg_sid,        s2.username wait_user, s2.machine wait_ws, s2.sid wait_sid,        lo.object_id blkd_obj_id, do.owner, do.object_name from v$lock l1, v$session s1, v$lock l2, v$session s2,      v$locked_object lo, dba_objects do where s1.sid = l1.sid   [...]