Archive for November, 2006

dbms_job, pended and drifting

November 16, 2006

Suppose we have a job submitted, interval is sysdate + 1 / 24 / 60, but the job is going to take 5 minutes, how will Oracle behave?
Looks like the sequence of events will be the following:
T0, Job #1 starts, the next run time is going to be sysdate(T0) + 1 minute.
T0 + 1 minute, The next job #2 starts, but is blocked by a QS lock, id2 in v$lock will tell the job id.
T0 + 5 minute, Job #1 completes, set sys.job$.next_date=greatest(T0 + 1/24/60, sysdate), effectively changing the this_date for job #2 to be sysdate. Job #2 starts.
Another issue is T0 is loosely guarenteed and could be off by couple of seconds, and even if the job completes within interval, the start time could drift if one uses sysdate only. To avoid drifting, use some calculation based on trunc(sysdate) instead of sysdate.


Null object_id in user_objects

November 16, 2006

When will Oracle have a null object_id, even if the doc says it’s NOT NULL?

An invalid database link.

An update: Actually, any database link.
It comes from the second branch of the union-all clause of user_objects:
select, NULL, to_number(null), to_number(null),
l.ctime, to_date(null), NULL, ‘VALID’, ‘N’, ‘N’, ‘N’
from$ l
where l.owner# = userenv(‘SCHEMAID’);

Firefox search plug-in for Oracle

November 8, 2006

Talking about firefox search-plugins for Oracle, you can find some here.
I like the Oracle Docs by Eddie Awad and AskTom by Stefan H.

Oracle SQL Developer v1.1 Evaluation Release goodies

November 8, 2006

Oracle SQL Developer v1.1 Evaluation Release is out. New Feature List here.
Some goodies I like are

  1. NLS preferences, my pet peeve
  2. View CLOB data
  3. Export data to HTML
  4. Search Engines, for OraDoc, AskTom, etc. Could augment firefox search plug-ins
  5. Autotrace

Now it’s time to upgrade and try out.

Ah, LiveHTTPHeaders

November 7, 2006

Ok, I did not abandon this blog, just I don’t have much to blog about, especially in Oracle, as I am now deep in Cognos territory.
Just noticed this cool firefox extension.
I knew such an extension must exist, but just didn’t find it. It would have been night and day difference for me when I was writing up the Cognos black-box test mentioned here. Unfortunately, I did it the hard way. Don’t laugh, but I just have the following Javascript snippet to print all the HTML elements in the form.

var elements = document.forms[0].elements;
var counter = 0;
for (counter=0; counter < elements.length; counter++) {
  alert(elements[counter].name + '=' + elements[counter].value + '\n');