SQL & PL/SQL :: Program Automating Scheduled Jobs?
Jun 26, 2011
We are using one application. This application is having 2types of jobs. Under each job again there are each 5 sub jobs. All the jobs are scheduled jobs. Now the requirement is The main 2 jobs we are going to run on some specified days( for this we need to mention on what days we want to run.once we gave those day the next steps will start).
for example 1 main job is scheduled on Monday ( we need to schedule it on monday-- we need to write a program for this) under this every sub job will run.these sub jobs are dependent jobs.if sub job completed successfully it should send an email to the distributed mail group which says like " <Job_name> completed successfully" if it fails also it needs to send an email to the distributed group. for each sub job it should send either success/falure mail to the distriuted mail group.
once after completing all the sub jobs successfully it should send a mail to the distributed mail group saying that The main job got completed successfully.these two main jobs will run paralelly but the the second main job will run after 30 mins of 1st job starts.
in the second job also will work as same as job1 process.it should also send mails to the distributed mail groups.The jobs are already scheduled.No need to scheduled the jobs.need to write a pl/sql program like it will check all the jobs and respected sub jobs if any thing got completed/failed it should send an email to the distributed mail group automatically. For that it should monitor all the jobs..that's the requirement.
I have already scheduled all the jobs. now want to write a script for the above requiement.
View 3 Replies
ADVERTISEMENT
Jun 22, 2010
am facing an issue with scheduled jobs. The below script will create a scheduled job to insert a record for every 2 mins into the table "job_table".
--Table script
CREATE TABLE job_table (now DATE);
--Stored procedure run by scheduled job
CREATE OR REPLACE PROCEDURE test1 IS
BEGIN
[code]...
The scheduled job does not require any special grant privillege as the PUBLIC has access to run it. Anyhow the same set of instruction working for my friend on his machine but not on my environment.
View 12 Replies
View Related
Sep 13, 2010
changing the user for a job.
There are 2 jobs which are scheduled in database by a user XX.
Now I need to make these scheduled jobs run by user YY.
In order to do this do I need to disable the jobs scheduled by XX and then create same jobs as before under the new user YY.
View 9 Replies
View Related
Feb 27, 2013
How to find currently running scheduled jobs using query !?
View 3 Replies
View Related
Jun 26, 2012
My automated RMAN backup plan.
ShutdownDB1.bat
set oracle_sid = pldg
C:oracleproduct10.2.0insqlplus.exe /nolog @c:oraclebackupshutdowndb2.sql
ShutdownDB2.sql (called by the previous batch file)
set echo on
spool C:oraclebackupShutdownDB.log
connect /@pledgemaker as sysdba
shutdown immediate
spool off
exit
<Here there will be a thirty minute window or so while I XCOPY the Oracle Inventory and Oracle Home directories.>
RMANBackup1.bat
set oracle_sid=pldg
C:oracleproduct10.2.0BIN
man.exe target / cmdfile = 'c:oraclebackup
manbackup2.scr'
log = 'c:oraclebackupRMANBackup.log'
RMANBackup2.scr (called by the previous batch file)
startup mount
backup database
alter database open
exit
View 15 Replies
View Related
Oct 25, 2011
I'm working for a credit card company and on a security project. We have oracle databases. Currently the passwords have to be changed every so often for key accounts for security purposes. Any tool to automate the process?
Any way to automate password changes on many accounts where only some people would be able to get the new password once it was changed.
Also, these IDs/passwords are sometimes used by applications to connect to the database so .ini files or some type of connection file would need to be changed automatically also.
View 2 Replies
View Related
Jan 5, 2012
We are having a problem trying to automate our 11g database startup on a Linux Platform (Redhat). This is the procedure we followed.
1. Logged in as the root user.
2. Edited the oratab file for the platform.
vi /etc/oratab
db:/u01/app/oracle/product/11.2.0/dbhome_1:Y
3. Create a file called dbora in /etc/init.d directory
4. Entered the following in dbora:
#! /bin/sh -x
#
# Change the value of ORACLE_HOME to specify the correct Oracle home
# directory for your installation.
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[Code] .....
4. Changed permissions of dbora
chgrp dba dbora
chmod 750 dbora
5. Created symbolic links to the dbora script in the appropriate run-level script directories:
# ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora
Have I covered everything needed? At system restart the listener and database fail to startup as expected.
View 4 Replies
View Related
Jan 28, 2011
I have a Cobol program that call C program above
#include stdlib
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
char *uid = "puntos/puntos@cmrdesar";
[code]....
Are something wrong in C programm ? Pro*C code from vouters.dyndns.org/tima/OpenVMS-Cobol-C-Cobol_ passing_ variable_ number_ of_ arguments_to_C.html
View 1 Replies
View Related
Aug 30, 2011
I have set up two scheduled jobs in oracle 11g for executiong a procedure in specific time intervals. One as Hourly and other as minutely.But the scheduled jobs are not running as per schedule. i.e the minutely job is not running every minute. But If i see the run logs for some period it would have run every minute, and suddenly it will not execute and give a gap of 30 mins or 1 hour and then run again.
P.s. The job priority is set as 3. and except for these two jobs there are no other jobs set. But this is an important job which needs to be run every minute.
JOB_QUEUE_PROCESSES is set to 1000, which is max. And there are only 3 scheduled jobs in the whole DB.
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'LOGTBLPURGE'
[code]....
View 2 Replies
View Related
May 12, 2010
A procedure in a package uses a database link. The database link is defined without a username and a password. The user that uses the database link is supposed to be present in the opposite database as wel.
When the procedure is started in SQL*Plus or TOAD it runs perfect. But the procedure has to run in a scheduled job. And that doesn't work. The procedure fails because of: ORA-01017 invalid username/password. The user that is used to run the scheduled job is the right one.
View 5 Replies
View Related
Jul 12, 2012
I have created a materialized view log on table testing_mview_rev in 11g:
SQL> CREATE MATERIALIZED VIEW LOG ON testing_mview_rev WITH ROWID;
Materialized view log created.
And a MV on it in 10g:
SQL> create materialized view testing_mview_1_rev REFRESH Force start with (sysdate) next (sysdate+1/1440) with rowid as select * from testing_mview_rev@S58_TO_S56;
Materialized view created.
Now this is suppose to automatically refresh itself every minute but refresh is failing. However manual refresh is working fine.
what I have missed here. is there any other way to schedule the MV to run every minute?
View 13 Replies
View Related
Oct 11, 2012
I have a job running under HUMADM user and would like disable or drop it.I do not have the login credentials of HUMADM user but can log into the database as system/sys user.
When I run the below commands as sys/system user it says humadmjob must be a job or it is not running.This is because it is looking for the job under sys/system user.
exec DBMS_SCHEDULER.drop_job (job_name => 'humadmjob');
exec DBMS_SCHEDULER.stop_job (job_name => 'humadmjob');
SELECT owner, job_name, job_class, enabled FROM dba_scheduler_jobs;
OWNER JOB_NAME JOB_CLASS ENABLED
====== ========= ======= ======
HUMADM HUMADMJOB DEFAULT_JOB_CLASS TRUE
how to stop/disable this job? My oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
View 8 Replies
View Related
Jun 5, 2013
-- Scheduler details
BEGIN
dbms_scheduler.create_schedule(
schedule_name => 'INTERVAL_EVERY_1_MINUTES',
start_date => trunc(sysdate)+18/24,
repeat_interval => 'freq=MINUTELY;interval=1',
comments => 'Runtime: Every day all 1 minutes');
END;-- program code
BEGIN
[code]....
all above three code compiled successfully . but it is not running automatically and not run every 1 minute.
I try to run this manuall by using following code
BEGIN
dbms_scheduler.run_job('JOB_FEED_UK_DATA',TRUE);
END;
It works fine..
create or replace
PROCEDURE LOAD_CUSTOMERS_UK
IS
BEGIN
Insert into CUSTOMERS_UK (ID, NAME, CITY) VALUES (1,'SMITH','LONDON');
Insert into CUSTOMERS_UK (ID, NAME, CITY) VALUES (2,'JONES','LONDON');
Insert into CUSTOMERS_UK (ID, NAME, CITY) VALUES (3,'BROWN','BRIGHTON');
[code]....
View 12 Replies
View Related
Oct 24, 2012
I have a 11.2.0.1 database windows 2008 server. I have RMAN with nocatalog. I need to take daily RMAN backup of database at 6 PM.
I want to schedule this RMAN backup to run automatically. I want to run this backup when I am not logged on.
View 2 Replies
View Related
May 17, 2011
i have batch file name exptab,to export all table in database
for example
@echo off
exp scott/tigger tables=emp file=C:impemp.dmp LOG =C:implog1
batch file working fine when i click on it,
I need to use the Scheduled Tasks in windows 2003 so when the computer startup batch file will run automatically i follow all the step but its not working i try the same story with windows xp still problem exist .
View 6 Replies
View Related
Sep 19, 2011
As I was observing the space issues on my db server. I found that there is lots of Trm and Trc file which is being created very much frequently. Due to this its consuming lots of space even the size of each files is not more than 1Mb.
For cleaning I am deleting all the trm and trc files mannully using DEL command Os level. How can i schedule the purging of trm and trc files.
View 3 Replies
View Related
Jun 20, 2010
Is there any way to find the last run duration of a job which was scheduled using DBMS_JOB?
View 3 Replies
View Related
Sep 11, 2013
Yesterday, there were performance issue at server. So today, when i am generating report for that particular period, found snapshot id sequence is serially but with skipped hourly timed. Instead of generating report at 15:30, it generated at 16:30.
Enter value for num_days: 2
Listing the last 2 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
tagidev TAGIDEV 2857 10 Sep 2013 00:30 1
2858 10 Sep 2013 01:30 1
2859 10 Sep 2013 02:30 1
2860 10 Sep 2013 03:30 1
2861 10 Sep 2013 04:30 1
2862 10 Sep 2013 05:31 1
[code]....
Below are the details at alert log -
Tue Sep 10 14:28:20 2013
Thread 1 cannot allocate new log, sequence 7029
Checkpoint not complete
Current log# 2 seq# 7028 mem# 0: E:APPORACLEORADATATAGIDEVREDO02.LOG
Thread 1 advanced to log sequence 7029 (LGWR switch)
[code]....
1) why snap didn't started at 15:30?
2) since database just started at the scheduled time of AWR snap time. But generated at 16:32 instead of 16:30, though last services "SMCO" is started at 16:42. How it snap id generated for this particular time?
3) what does "kewastUnPackStats(): bad magic 1 (0x000000001B3CE48D, 0)" mean?
View 7 Replies
View Related
Jan 7, 2013
I copied a sqlplus scheduled job procedure. Procedure successfully created .I fount it here [How to Schedule RMAN Daily Backup? [URL]
The procedure is successfully created. Now, doing the following operations:
1) running that job
2) checking whether that job is running or not
3) listing all other jobs
4) unschedule a scheduled job
5) delete a job
6) edit a job
View 3 Replies
View Related
Jan 18, 2013
There is a readonly user on our reporting server. Developers want to use global temporary tables with this user. I don't want the user to have permissions other than readonly.I can grant the user CREATE TABLE privilege and did not grant quota on any permanent tablespace, therefore user would not be unable to create permanent tables but still should be able to create global temporary tables.
Question is would a user with such permissions still be able to utilize temp tables as part of a scheduled job?
View 2 Replies
View Related
Sep 3, 2010
how to execute one stored procedure for every 10 minutes using oracle jobs
sp name: abc
using anonymous block with sample code.
View 10 Replies
View Related
Dec 19, 2011
I want to run a procedure every day at 9 AM ,so how can i schedule it.
View 3 Replies
View Related
Aug 20, 2011
I have executed two jobs using dbms_scheduler. The value of the job_queue_process is 10 in my database. It is RAC database with Oracle 10g.
My job gets executed. But the next run is not getting executed at the next run date. In the below example, the systimestamp is 12:27. The first job's next run date is 12:30 so that is fine. But the second job's run date is 12:21 which is 6 mintues less than current time. As a result, the second job does not get executed after this at all..
SQL> select job_name, next_run_date, last_start_date, last_run_duration from user_scheduler_jobs;
HISASAV_JOB
20-AUG-11 12.30.41.000000 PM ASIA/CALCUTTA
20-AUG-11 12.20.41.122959 PM ASIA/CALCUTTA
+000000000 00:00:01.307071
[code]....
View 4 Replies
View Related
Dec 31, 2010
We have a job table, and whenever jobs are added to this table the job processing procedure should be started.
In unix we have crontab facility where we can schedule to run for 24/7.
similarly do we have any option in oracle daatbase, to keep on running a prcedure in loop.
View 2 Replies
View Related
Apr 3, 2013
I am trying to find a way to identify what are the user_jobs currently running. I have found 1 way which requires DBA User ID to execute (e.g. DBA_JOBS and DBA_JOBS_RUNNING) which I don't want.
I believe there is NO equivalent User table for DBA_JOBS_RUNNING table.
I have done my 20 mins search in this forum and unable to find.
View 8 Replies
View Related
Nov 20, 2012
I have a procedure that needs to be scheduled as a job. This procedure takes an average of 20-25 minutes to complete. The constraint is that these jobs are submitted using dbms_job.submit with an interval of 5 minutes.
For Example:
Job1 Procedure_B 20-Nov-12 21:05:00
Job2 Procedure_B 20-Nov-12 21:10:00
Job3 Procedure_B 20-Nov-12 21:15:00
Job4 Procedure_B 20-Nov-12 21:20:00
So, will Job2 execute only after Job1 completes [even though Job1 takes 25 minutes to complete] or is there any other mechanism for setting incompatibility for a job with itself.
View 5 Replies
View Related
Mar 11, 2013
I have created two jobs in sys database as follows,
BEGIN
sys.dbms_scheduler.create_job(
job_name => 'JOB_FCDB_CHARGES',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN FCDBCHARGES; END;',
repeat_interval => 'FREQ=DAILY;BYHOUR=19',
job_class => 'DEFAULT_JOB_CLASS',
comments => 'Job to process Internet Banking Charges.',
auto_drop => FALSE,
enabled => FALSE);
[code]....
It has been successfully created.Even i get the entry in dba_objects table as follows,
select owner, object_name, object_type from dba_objects where owner = 'SYS' and object_name like '%JOB_FCDB_CHARGES%';
But, my problem is when i log in to PL/SQL developer i am not able to see anything under job.
View 9 Replies
View Related
Oct 12, 2011
Assign employees to their jobs in consideration the maximum number of employees to each jobs is 5 employee plus each job has own the maximum number of employees
we need the maximum number of employees for each job 5 to be variable when need to change this maximum for certain job , change this number from database (form the from of job ) not form code )
tables
emp
emp_no
name
manager
hiredate
salary
job
job_no
job
you can add tables or attributes to tables to complete you business.
View 10 Replies
View Related
Sep 7, 2010
Under what userid(privileges) does a scheduled DBMS Jos run, is it under the same user that scheduled the job?
Does oracle internally log in(opens a new session) in order to run a scheduled DBMS Job?
some reference websites where I can get some details about how the scheduled jobs are executed?
View 2 Replies
View Related
May 6, 2011
I work an migration jobs Oracle Forms 4.5 INp to 10G.Have any sentence that i havent idea:
DEFINE TRIGGER
NAME = xxxxx
TRIGGER_TYPE = V2
DEFINE STEP
TEXT = <<<
#EXEMACRO NOFAIL NULL;
>>>
REVERSE = ON
ENDDEFINE STEP
special with REVERSE = ON...I need replace this en PL/SQL Oracle Forms 10G
View 6 Replies
View Related
