Core dump of a DBA's mind: May 2014

Saturday, May 17, 2014

Writing INTERVAL expression of DBMS_JOB for exactly timed schedules

On heavily loaded database with lot of jobs scheduled, sometimes we need to time jobs to run at exactly specific intervals in a day. Examples
1. Running a job at every 30 minutes, and timing exactly at 00:10, 00:40, 1:10, 1:40...
2. Running a job at every 6 hours, and timing exactly at 3:00, 9:00, 15:00 ...

Even if a specific instance of job runs at an random time, the next run should fall into timed schedule. It means, for a job that runs at every 6 hours starting with 3:00 (3:00, 9:00, 15:00..), if it's run at 4:30, next run should be at scheduled time 9:00 (not at 4:30+6 i.e 10:30).

Here is how we can derive interval property of a job to get next scheduled date and time.

 
date_time_of_next_scheduled_run = today_date + time_of_next_scheduled_run

time_of_next_scheduled_run = time_of_last_scheduled_run + interval_between_runs

time_of_last_scheduled_run = time_of_first_scheduled_run + (number_of_times_run_completed_today * interval_between_runs) 

number_of_times_run_completed_today = floor((present_time_of_day - time_of_first_scheduled_run)/interval_between_runs)

By substituting all the above, we can get an expression for interval

 
date_time_of_next_scheduled_run =   today_date 
                                  + time_of_first_scheduled_run 
                                  + floor(
                                          (present_time_of_day - time_of_first_scheduled_run)
                                          /interval_between_runs
                                         ) 
                                       * interval_between_runs 
                                   + interval_between_runs

For example, a schedule for every 30 minutes, and timing at 00:10, 00:40, 1:10, 1:40...,

 
today_date = trunc(sysdate)
time_of_first_scheduled_run (in seconds) = 00:10 = 10*60
interval_between_runs (in seconds) = 30*60
present_time_of_day (in seconds) = to_char(d,'SSSSS')

date_time_of_next_scheduled_run = 
trunc(sysdate) +                             
  (   10*60                                  
    + floor(                                 
            (to_char(d,'SSSSS')-(10*60))     
            /(30*60)                         
            )                                
         *(30*60)                            
     + (30*60)                               
   )/(24*60*60)

We can test if this working by sample PL/SQL program which prints next scheduled time for every minute.

 
declare 
  d date; 
begin 
  for i in 0 .. 150
  loop 
    d := to_date('2014-05-15 00:00', 'YYYY-MM-DD HH24:MI') + i/(24*60); 
    dbms_output.put(to_char(d, 'YYYY-MM-DD HH24:MI') || ' - '); 
    
    dbms_output.put_line( to_char(
          trunc(d) + ( 10*60 + floor( (to_char(d,'SSSSS')-(10*60)) /(30*60) ) *(30*60) + (30*60) )/(24*60*60)  
     , 'YYYY-MM-DD HH24:MI')); 
    
  end loop; 
end;
/

2014-05-15 00:00 - 2014-05-15 00:10
2014-05-15 00:01 - 2014-05-15 00:10
...
2014-05-15 00:09 - 2014-05-15 00:10
2014-05-15 00:10 - 2014-05-15 00:40
2014-05-15 00:11 - 2014-05-15 00:40
...
2014-05-15 00:39 - 2014-05-15 00:40
2014-05-15 00:40 - 2014-05-15 01:10
2014-05-15 00:41 - 2014-05-15 01:10
...
2014-05-15 01:09 - 2014-05-15 01:10
2014-05-15 01:10 - 2014-05-15 01:40
2014-05-15 01:11 - 2014-05-15 01:40
...

Other examples of interval expression

Schedule for every 6 hours starting with 3:00 (3:00, 9:00, 15:00..)

trunc(sysdate) + ( 3*60*60 + floor( (to_char(sysdate,'SSSSS')-(3*60*60)) /(6*60*60) ) *(6*60*60) + (6*60*60) )/(24*60*60)

Schedule for every 1 hour starting with 0:00 (0:00, 1:00, 2:00, 3:00..)

trunc(sysdate) + (  floor( (to_char(sysdate,'SSSSS')) /(1*60*60) ) *(1*60*60) + (1*60*60) )/(24*60*60)

Wednesday, May 14, 2014

My style (CSS) for blogs

Here is sample HTML code with CSS inside, which I am planning to use as template for future blog posts. This is initial one, and may be enhanced as needed.

<html>

<head>
<style type="text/css">

pre {
 white-space: pre-wrap;
 font-family: Ubuntu Mono, Courier New, monospace;
 color: #000000;
 background-color: #dddddd;
 border-style:dashed;
 border-width:thin;
 margin-top:1px;margin-bottom:25px;margin-right:50px;margin-left:25px;
 }

body {
 font-family: Droid Sans, Verdana, Sans-Serif;
 color: #000000;
 background-color: #ffffff;
 line-height:125%
 }

</style>
</head>

<body>

<p> This is a sample text paragraph 1. </p>

<p> This is a sample text paragraph 2. </p>

<pre>
This is a sample code line 1
  This is a sample code line 2 with indentation
</pre>

<p> This is a sample text paragraph 3. </p>

</body>

</html>

Loading LOB data using SQL Loader

When there is LOB data at client side, SQL loader can be used to load into Oracle tables. Here are the steps to do it.

Create table with LOB column(s), if it is not already there.

create table tab1
( 
  id   number(5),
  text varchar2(10),
  dt   date,
  doc  clob
);

Here is the CSV file with data. Instead of LOB data, there will be respective file names that contain LOB data.

$ cat data.csv 
1,AAA,2014-05-01,doc1.txt
2,BBB,2014-05-02,doc2.txt

$ cat doc1.txt 
Text of doc1

$ cat doc2.txt 
Text of doc2

The control file of SQL Loader looks like below. There will be FILLER column for file names.

$ cat load.ctl 
LOAD DATA 
INFILE 'data.csv'
   APPEND INTO TABLE tab1
   FIELDS TERMINATED BY ','
   (
    id        CHAR(5),
    text      CHAR(10),
    dt        DATE "YYYY-MM-DD" ":dt",
    file_name FILLER CHAR(100),
    doc       LOBFILE(file_name) TERMINATED BY EOF
    )

The output on screen looks like below when sqlldr command is executed.

$ sqlldr userid=murty/xxxx@mm1 control=load.ctl 

SQL*Loader: Release 12.1.0.1.0 - Production on Tue May 13 10:13:26 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 2

Table TAB1:
  2 Rows successfully loaded.

Check the log file:
  load.log
for more information about the load.

We can verify if the data is loaded successfully

SQL> select * from tab1;

 ID TEXT       DT
---------- ---------- ---------
DOC
--------------------------------------------------------------------------------
  1 AAA       01-MAY-14
Text of doc1

  2 BBB       02-MAY-14
Text of doc2

The same procedure can be used for BLOB data also.