Jobs Oracle…

http://www.orafaq.com/node/871

Jobs Oracle

Using the power of DBMS_JOB.SUBMIT
Submitted by Natalka Roshak on Thu, 2006-06-01 02:13

* RDBMS Server

Natalka Roshak’s picture

Some DBAs complain that Oracle’s pre-10g job queue interface is lacking. Unlike cron or Windows Scheduler, pre-10g Oracle doesn’t provide a mechanism to schedule jobs to run twice a week, on the first of the month, etc.

The truth is that Oracle’s job queue interface has far greater flexibility than even the most full-featured o/s job scheduler. Jobs are scheduled using the sys.dbms_job.submit routine:

declare
my_job number;
begin
dbms_job.submit(job => my_job,
what => ‘my_procedure(foo);’
next_date => sysdate+1,
interval => ‘sysdate+1′);
end;
/

The arguments for dbms_job.submit include two time-related parameters, next_date and interval.
next_date is the next date you want the job to run. An argument of DATE datatype must be passed in.
interval is the parameter that affords the DBA great flexibility. The argument passed in is in varchar2, and it must be an expression that evaluates to a date. Each time the job is run, interval is reevaluated and the result is set as the next_date the job will run.

next_date

This parameter is easy to understand and supply. You can supply it with a to_date cast of a char string, as in,

to_date(’12/13/2004 02:34 PM’,’MM/DD/YYYY HH:MI AM’);

However, it’s often quicker and more convenient to pass it in as a function of the SYSDATE function. Examples:

sysdate+1 –This time tomorrow
trunc(sysdate)+1 –12:00 am tomorrow
trunc(sysdate)+17/24 –5 pm (17:00) today

The date passed in must, obviously, be greater than or equal to sysdate.

Recall that in Oracle date arithmetic, “+1” means add one day. “trunc(date)” returns midnight of the date passed in. Thus, “+17/24” means add 17/24ths of a day, so “trunc(sysdate)+17/24” means “17 hours past midnight today”.

interval

The power of this parameter is that it is a varchar2 string, not a date or a number of days or minutes. You may pass any varchar2 string you like; the only constraint is that the argument must evaluate to a date greater than the date on which the job is run. Eg:

‘sysdate+1’ –Exactly 24 hours after the job’s current run starts
‘trunc(sysdate)+1’ –Midnight after the day the job is run
‘trunc(sysdate)+17/24′ –5 PM on the day the job is run

Each time the job is run, the varchar2 string you passed in as interval is reevaluated and the job’s next run date is set to the result. (If the varchar2 string passed as interval does not evaluate to a date greater than today’s, or to null, dbms_job.submit returns an error. If it evaluates to null, the job is simply not run again.)

The power here is that you can write your own functions and set them to return whatever date you like. Most powerfully, your functions can depend on database state. You could set a job to run every two weeks when the status of a certain employee in your tables was “on leave”, and every month otherwise.

More prosaically, you can write your own functions to run jobs only when you need them to run. For example, if I wanted to run a job only Wednesdays at 3 pm and Fridays at 5 pm, I could write this quick function:

function date_to_run_emp_job
return date
is
mydate date;
begin
if to_char(sysdate,’D’) < 4 –Wednesday is the 4th day of the week in Oracle
then
mydate := trunc(sysdate,'W')+2+15/24 ; –Monday is the 1st day of week
elsif to_char(sysdate,'D')=4 and sysdate < trunc(sysdate)+15/24 then
–ie. it's Wednesday but it's before 3 pm
mydate := trunc(sysdate,'W')+2+15/24 ;
else
mydate := trunc(sysdate,'W')+4+17/24 ; –Friday at 5 pm
end if;
return mydate;
end;
/

Don't worry, I've done the maths for you

So we've established that Oracle's dbms_job.submit supplied procedure is, indeed, just as powerful as you want it to be. By now we've also established that it can be a major pain in the tushie to use. If you have many jobs that require complex schedules, you can wind up with a litter of disorganized functions; worse, a solid knowledge of and comfort with Oracle's date arithmetic and functions are required to really make use of the flexibility of dbms_job.submit.

The solution is a generic date package, owned by system, with execute rights granted to public, which will return some commonly wanted next_dates. Remember that the goal is to have a function that, when run right before each time job is run, evaluates to the next date you want the job to run. These functions are to be used for the interval argument of job_next_dates.

The package job_next_dates includes the following functions:

weekly(daystring,hour24) accepts a string of 2-letter day abbreviations, and a string expressing the time in 24-hour format. For example:
job_next_dates.weekly('MoTuFri','23:00')
supplied as the INTERVAL argument to job_next_dates, will cause your job to be run Mondays, Tuesdays and Fridays at 11 PM.

specific_dates accepts up to 5 specific dates on which you want a job to run (in addition to the next_date you supply to dbms_job.submit). For example:
job_next_dates.specific_dates('mm/dd/rr hh24:mi', '12/23/04 23:11', '11/14/2004 03:33')
will run your job on the specified dates only.

every_day(hour24) will run your job every day at the time specified. eg: job_next_dates.every_day(11:34)

You must supply 24-hour times with a leading zero where appropriate, eg. 03:00 instead of 3:00, 00:24 instead of 0:24.

job_next_dates package

create or replace package job_next_dates is
/*
Author : NROSHAK
Created : 12/17/2003 5:32:14 PM
Purpose : Functions that return the next date specified.
For the "interval" parameter of DBMS_JOB,
which accepts a varchar2 string that evaluates to a date.
These functions allow scheduling of database jobs
on complex schedules.
Example :
dbms_job.submit(:job, 'myproc;', sysdate,
'job_next_dates.weekly(''MoTuWe'',''14:45'')' );
COPYRIGHT 2003 NATALKA ROSHAK
*/

— In all of these functions, HOUR24 should be entered in the format
— HH:MI where HH is the 24-hour hour.
— eg. 14:45
— 00:00 is a valid value, but 24:00 is not.

function weekly (Sun in boolean, Mon in boolean, Tue in boolean,
Wed in boolean, Thu in boolean, Fri in boolean, Sat in boolean,
hour24 in varchar2)
return date;

function weekly (daystring in varchar2, hour24 in varchar2)
return date;

function friendly_date(date_string in varchar2, format_string in varchar2)
return date;

function specific_dates(format_string in varchar2,
date1 in varchar2,
date2 in varchar2 default null,
date3 in varchar2 default null,
date4 in varchar2 default null,
date5 in varchar2 default null)
return date;

function specific_dates(format_string in varchar2,
date1 in varchar2,
date2 in varchar2 default null,
date3 in varchar2 default null,
date4 in varchar2 default null,
date5 in varchar2 default null,
hour24 in varchar2)
return date;

–use this function as "interval" argument to run a job every day at
–the same time
function every_day(hour24 in varchar2)
return date;

end job_next_dates;
/
create or replace package body job_next_dates is

— Private type declarations
type days_t is table of boolean index by binary_integer;

procedure debug (message in varchar2)
is

begin
dbms_output.put_line ( message);
end debug;

function time_in_minutes (hchar in varchar2)
return number
–Accepts a 24-hour string like 09:45
–Returns number of minutes past midnight
is
hno number;
hrs number;
mins number;
begin
if hchar not like '__:__'
then
return -1;
else
hno := to_number(to_char(to_date(hchar,'hh24:mi'),'sssss'))/60 ;
end if;
return hno;
exception
when others then
debug('Error in job_next_dates: Bad time supplied: ' || hchar);
debug('Time must be in format HH24:MI');
return -1;
end time_in_minutes;

function weekly (Sun in boolean, Mon in boolean, Tue in boolean,
Wed in boolean, Thu in boolean, Fri in boolean, Sat in boolean,
hour24 in varchar2)
return date
is

hno number; –number of minutes past midnight
today_is varchar2(5);
day_table days_t;

next_day_to_run number := null;
boost_index number;

bad_time exception;
no_day_supplied exception;

begin
–check format of hour
hno := time_in_minutes(hour24);
if hno sysdate
then
boost_index := 0;
else
boost_index := 1;
end if;

for i in today_is+boost_index..today_is+boost_index+6
loop
if day_table(i) = true
then
next_day_to_run := i;
exit;
end if;
end loop;

if next_day_to_run is null then
raise no_day_supplied;
end if;

return trunc(sysdate)-today_is+next_day_to_run + hno/1440 ;
exception
when bad_time then
debug(‘Error in job_next_dates.weekly’);
raise;
when no_day_supplied then
debug(‘Error in job_next_dates.weekly: No day of week supplied’);
raise;
when others then
debug(‘Error in job_next_dates.weekly’);
raise;
end weekly;

function weekly (daystring in varchar2, hour24 in varchar2)
return date
is
–daystring: Contains any of MoTuWeThFrSaSu
–eg. job_next_dates.weekly(‘MoTuWeSa’,’16:34′);

mon boolean := false;
tue boolean := false;
wed boolean := false;
thu boolean := false;
fri boolean := false;
sat boolean := false;
sun boolean := false;
begin
if UPPER(daystring) like ‘%MO%’ then
mon := true;
end if;

if UPPER(daystring) like ‘%TU%’ then
TUE := true;
end if;

if UPPER(daystring) like ‘%WE%’ then
WED := true;
end if;

if UPPER(daystring) like ‘%TH%’ then
THU := true;
end if;

if UPPER(daystring) like ‘%FR%’ then
FRI := true;
end if;

if UPPER(daystring) like ‘%SA%’ then
SAT := true;
end if;

if UPPER(daystring) like ‘%SU%’ then
SUN := true;
end if;

return weekly (sun, mon, tue, wed, thu, fri, sat, hour24);

end weekly;

function friendly_date(date_string in varchar2, format_string in varchar2)
return date
is
— Useless wrapper around to_date
begin

return to_date(date_string, format_string);

exception
when others then
debug (‘Error in job_next_dates.friendly_date: Bad date or format string’);
debug ( sqlerrm );
raise;
end ;

function specific_dates(format_string in varchar2,
date1 in varchar2,
date2 in varchar2 default null,
date3 in varchar2 default null,
date4 in varchar2 default null,
date5 in varchar2 default null)
return date
is
— Specify up to five unrelated dates, all with same format string,
— in any order, for the job to run
next_date date := null;
curr_date date;
currtime date;
begin

currtime := sysdate + 1/100000;
curr_date := to_date(date1,format_string);
if curr_date > currtime then
next_date := curr_date;
end if;
curr_date := to_date(date2,format_string);
if curr_date > currtime and curr_date currtime and curr_date currtime and curr_date currtime and curr_date ’02:34′);
retval date;
hno number;
begin
hno := time_in_minutes(hour24);
retval := specific_dates (format_string,
date1, date2, date3, date4, date5) + hno/1440 ;
return retval;
exception
when others then
debug (‘Error in job.next_dates.specific_dates: ‘);
debug ( sqlerrm );
raise;
end specific_dates;

function every_day(hour24 in varchar2)
return date
is
— Return tomorrow at hour24 o’clock
begin

return trunc(sysdate+1) + time_in_minutes(hour24)/1440 ;

exception
when others then
debug (‘Error in job.next_dates.tomorrow: ‘);
debug ( sqlerrm );
raise;
end every_day;

end job_next_dates;
/

STANDARD DISCLAIMER: Test all code before running it on your production system. Code provided as an example for educational purposes only. Etc.

For maximum utility, I recommend that (after testing it on your system) you install this package as a user found in all your databases, then –

grant execute on myuser.job_next_dates to public;
create public synonym job_next_dates for myuser.job_next_dates;

About the author

Natalka Roshak is a senior Oracle and Sybase database administrator, analyst, and architect. She is based in Kingston, Ontario, and consults across North America. More of her scripts and tips can be found in her online DBA toolkit at http://toolkit.rdbms-insight.com/.
»

* Natalka Roshak’s blog
* Login to post comments

Cool but…
Submitted by Fabien (not verified) on Mon, 2006-06-12 02:18.

Very cool script, but there is a missing “then” in function date_to_run_emp_job at line with the “elsif”.

»

* Login to post comments

Thanks – problem fixed!
Submitted by admin on Mon, 2006-06-12 02:36.

Thanks – problem fixed!
»

* Login to post comments

Brilliant
Submitted by Nicolas Doye (not verified) on Thu, 2006-09-28 09:36.

You rock!
»

* Login to post comments

really handy
Submitted by Nicolas de Fontenay (not verified) on Thu, 2006-10-12 20:19.

Thanks a lot.

This example came in very handy while I had to create some jobs recently.
»

* Login to post comments

Re: TRUNC(SYSDATE, ‘W’)
Submitted by Sudarshana Bhat (not verified) on Wed, 2006-11-01 10:11.

trunc(sysdate,’W’) – the use of this in the date_to_run_emp_job function is incorrect.

trunc(sysdate, ‘W’) will get the date based on the day of the week of the 1st date of that month. It will not necessriy get you a Monday or Wednesday.

Ciao,
Sudarshana Bhat
»

* Login to post comments

You’re right, Sudarshana –
Submitted by Natalka (not verified) on Thu, 2006-11-02 17:34.

You’re right, Sudarshana – that should be a ‘D’ to get the first day of the current week, and the math is a bit off too. Thanks for catching it.
»

* Login to post comments

First day of the week
Submitted by Kavya (not verified) on Wed, 2006-12-27 03:19.

mydate := trunc(sysdate,’W’)+2+15/24 ; –Monday is the 1st day of week

I think Sunday is the first day of the week. So, to get Wednesday we need to add 3 instead of 2.
»

* Login to post comments

how does it help in online transactions
Submitted by madhuri_funny on Wed, 2008-06-11 06:33.

Hi,

For a online transaction in my application, I need to execute 2 queries.

I am invoking 2 jobs in the transaction using the following code for improving performance as two queries will execute in parallel.

dbms_job.submit(v_JobNum1, ‘proc1;’,SYSDATE,NULL);

COMMIT;

dbms_job.submit(v_JobNum2, ‘proc2;’,SYSDATE,NULL);

COMMIT;

I have a question here, If Multiple users are accessing, should the 2nd request jobs wait until the first request jobs gets finished? Because I see when I am giving multiple requests, they are getting hanged.

Thanks
»

* Login to post comments

Sine qua non

Sabe… É necessário que vivamos melhor. Tantas coisas nos cercam e, por vezes nos deixamos levar por decisões que não são nossas, àquelas decisões que outros tomaram por nós, em nosso lugar. Eu tenho aprendido a viver diferente! Dei um basta a esta maneira de pensar que me foi enraizada culturalmente. Hoje em dia, para mim, necessário é tomar minhas próprias decisões, assumir meus riscos e responsabilidades e tampar os ouvidos para vozes exteriores que confundem e inundam de incertezas o meu caminho.
Certa vez um homem disse: “se você acha que pode ou acha que não pode, em ambos os casos você tem toda razão”. Essa frase de Henry Ford é latente em minha consciência. Todos os dias quando me deparo com uma dessas bifurcações e vicissitudes que a vida oferece, a mesma frase ecoa, soando baixinho e suave lá na alma. O ímpeto me toma e o acreditar surge, me dizendo que o realizar, fazer, conquistar apenas depende de mim. Vou adiante então e construo a cada instante um mundo novo e melhor, com estradas limpas, asfaltadas, bem iluminadas, belíssimas árvores em ambos os lados; onde eu e meus consortes poderemos tranqüilamente pousar os pés.
É necessário! É necessário agir desta maneira. Aliás, aprendi que a força que movimenta o mundo – e principalmente o meu mundo – é a força do pensar, e “pensar grande”. Não obstante, não pára por aí. Existe uma fluência responsável pela realização dos pensamentos e a aplico todos os dias em minha vida. A fluência do “pensar, sentir e agir”. Ou seja, não podemos parar no pensar. Este verbo precisa ser intercalado imediatamente ao sentir. Por sua vez, o sentir homogeneizado com o pensar traz o resultado magnífico que cria o universo: o agir. Poderia explicar tudo isso de outra forma, contudo, a síntese é: o pensamento produz em nós o sentimento; é aquela emoção que começa a se mover dentro do coração, no plexo solar e que através de nossos nervos chegam até o centro de tomada de decisões, produzindo assim a ação, ou seja, o agir. Isso é mais que necessário para cada indivíduo. Criar seu próprio universo. No meu caso, passou a ser uma prerrogativa, o sine qua non do meu viver. Creio que todos nós precisamos despertar esse poder em nossas vidas. Eu agradeço todos os dias por em determinado momento de minha caminhada ter sido desperto.
É necessário! Necessário que construamos um mundo melhor, nosso próprio mundo em sintonia com tudo e com todos, seguindo o fluxo normal das leis que regem este universo e das quais todos temos conhecimento. Ninguém pode se abster ou dizer o contrário, pois, lá bem no fundo, enraizado em nosso ser, em nossas estruturas molecular, celular, orgânica, mental e espiritual, a regência é perfeita e comprova tais fatos. Tudo no universo segue uma harmonia indescritível, uma métrica perfeita num ritmo sem igual, logo, não podemos estar distantes disso, afinal, somos parte da mesma criação.
Quiçá um dia a humanidade evolua bem mais do que já atingimos com nossos paradigmas atuais. Aliás, é necessário continuar quebrando os paradigmas e criando novos, e assim sucessivamente. Quiçá um dia possamos dar maior ênfase ao problema de linguagem dos homens. Refiro-me a linguagem interior, aquela linguagem utilizada para falar consigo mesmo e que tanto tem sido prejudicada pela cultura equivocada que recebemos de nossos pais e do mundo. Faz-se necessário uma reprogramação lingüística do homem em busca da continuidade evolucionária. Repito: é necessário!
A partir do momento que mudamos nossa maneira de pensar, imediatamente nosso agir passa a ser translúcido e um leque “caleidoscópico” de ações se apresenta diante dos nossos olhos. Será um passe de mágica? Será? Sine qua non!

Marinho (11-01-06)

Construindo vídeo lightbox automaticamente… http://videolightbox.com/

http://videolightbox.com/

Pure menu css – construindo menus CSS on-line…

http://purecssmenu.com/

Lightbox2 – Projeto Original – IMAGENS APENAS – http://www.lokeshdhakar.com/projects/lightbox2/

http://www.lokeshdhakar.com/projects/lightbox2/

Oracle – detonar o serviço no windows…

Excluir (Deletar, Matar, Chutar, Explodir) Um Serviço do Windows
25
09
2007
Essa vai ser de matar o peão ….

Um belo dia eu instalei o Oracle no meu notebook na partição windows pra teste e fiz o procedimento de criar toda a estrutura do banco na mão … inclusive o maldito serviço do Windows.

Eis que na hora em que eu fui criar o serviço estava passando uma cena bem interessante na TV … e o que aconteceu ??? criei o serviço com o nome errado.

E agora minha gente ??? alguém já teve a triste tarefa de excluir um serviço do Windows?????

Isso é um trabalho para o Super Homem???? Silvester Stallone ou Chuck Norris ?????

Depois de muito googlar … todo mundo mandando dar uma de Chuck Norris lançar logo uma voadora no register … eu achei uma maneira bem tosca estúpida e simples de fazer isso. Basta usar um comando bem simples para excluir o serviço:

sc delete service_name

Essa foi de matar hein !!!!

extraído de ====> Kenia Milene

Volumetria de Banco Oracle

Volumetria de Banco Oracle
25
09
2007
Uma bela tarde de sol se via da janela da empresa .. os passarinhos pipiuavam em seus ninhos tranquilamente e felizes quando derrepente toca o ramal:
”Boa Tarde, preciso da Volumetria do Banco de Produção pra ONTEM”

Tudo estava tão tranqüilo não é mesmo????

Bem … eu podia ter queimado a “muffa”, mas como o tempo era levemente curto, apelei para os amigos de trabalho. Eis que lá no 6º andar uma santa alma chamado Eduardo Tomazini tinha exatamente o que eu precisava…..

Obrigada a toda equipe da IBM …. aprendi muito com vocês !!!!!!

– Tamanho de Cada Tabela

SELECT owner, tablespace_name, segment_name,
round(sum(bytes/1024/1024),2) as Tamanho_MB –, extents as Num_extents
FROM dba_segments
WHERE owner = ‘SCOTT’
AND segment_type = ‘TABLE’
– AND segment_name like ‘DEPT%’
GROUP BY owner, tablespace_name, segment_name

– Tamanho das Tabelas Por Usuário

SELECT owner, round(sum(bytes/1024/1024),2) as Tamanho_MB –, extents as Num_extents
FROM dba_segments
GROUP BY owner

– Tamanho Total das Tabelas

SELECT round(sum(bytes/1024/1024),2) as Tamanho_MB –, extents as Num_extents
FROM dba_segments

– % de Uso das TableSpaces

SELECT a.TABLESPACE_NAME “TableSpace Name”,
round(a.BYTES/1024/1024) “MB Allocated”,
round((a.BYTES-nvl(b.BYTES, 0)) / 1024 / 1024) “MB Used”,
nvl(round(b.BYTES / 1024 / 1024), 0) “MB Free”,
round(((a.BYTES-nvl(b.BYTES, 0))/a.BYTES)*100,2) “Pct Used”,
round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2) “Pct Free”
FROM (SELECT TABLESPACE_NAME,
sum(BYTES) BYTES
FROM dba_data_files
GROUP BY TABLESPACE_NAME) a,
(SELECT TABLESPACE_NAME,
sum(BYTES) BYTES
FROM sys.dba_free_space
GROUP BY TABLESPACE_NAME) b
WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
ORDER BY ((a.BYTES-b.BYTES)/a.BYTES);

extraído de ===> Kenia Milene