I need to schedule a script in crontab which needs to be execcuted on every 2nd Sunday of every month.
I used this logic
30 9 8-14 * 0 /$PATH/$FILE_NAME.sh but this is not working only on sunday but on all days of second week.
Any logic to schedule this?
I want to schedule a job which will run evry Sunday at 12 P.M IST.I have written the below script.
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SERVICE_SAL_FS.SAL_MESSAGE_BUFFER_PURGE_JOB'
,start_date => SYSTIMESTAMP
[code]...
But the server in US.
SELECT sysdate from Dual;
6/11/2013 3:58:58 AM
--But the time in India is 02:28 PM
How to convert the timezone to IST. how to schedule the job to run evry Sunday at 12 P.M IST.
I need to get the previous Sunday through Saturday from each Sunday. I want to run the report every Sunday and it will compare the previous dates from Sunday to Saturday.
View 3 Replies View Relatedi have a production database server with 2 node rac. i now have a separate database server for my report db. Now i want to do an automatic export of the production database or a schema in the production database and then import it automatically to the report db immadiately after the export job finishes at a specific time, say 12 midnight everyday. how do i go about this using crontab or any other better solution. my database is 11gr2 and operating system is rhel 5.7.
View 1 Replies View RelatedI need to take RMAN full backup by every Sunday night and Wednesday night . I have the netbackup script which will take the backup to media.
Question is:1. We have 15 - backup scripts for 15 DB in that server , so if i configure crontab for backup do i need to give all 15 scripts one by one or (*) will work . ie) 00 20 * * 3,7 /app/oracle/rman/scripts/hot_db_<sid>.sh
instead of this if i give : 00 20 * * 3,7 /app/oracle/rman/scripts/hot_db_*.sh then it will works fine or not ? 2.
Already my backup will create .output file in the same location , do i need to give output file location in crontab using ( > ) ? 3.
Is the above crontab timing is correct ? (3,7) for Wednesday and Sunday 8pm? so Sunday is 0(zero) or 7 ?
I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.
1. Table creation:
Create table T1 (account_no varchar2(15), area_code varchar2(2), bill_month date, consumption number);
2. List table content:
select * from T1;
account_no area_code bill_month consumption
Q00001Q31-Jan-12125
Q00002Q31-Jan-1265
Q00003Q28-Feb-12219
Q00004Q28-Feb-12805
Q00005Q28-Feb-1254
Q00001Q31-Mar-12234
Q00002Q31-Mar-12454
Q00003Q31-Mar-12232
Q00004Q30-Apr-1221
Q00005Q30-Apr-12218
Q00001Q30-Apr-1254
Q00002Q31-May-1219
Q00003Q31-May-1287
Q00004Q30-Jun-12187
Q00005Q30-Jun-1278
so on......so on......so on......so on......
3. Expected output:
account_no area_code Jan-12 Feb-12 Mar-12 Apr-12 May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12
Q00001 Q 125 548 2345487423154821518738721512
Q00002 Q 65 127 45487819357831585683152878
Q00003 Q 545 219 2328738735188745897313
Q00004 Q 78 805 1221218187885718387389787138
Q00005 Q 541 54 2621878778386538698182
With the conventional query I hope this is impossible,
I have following code. It is always returning sysdate even run on Sunday.
declare
vDate date;
begin
if to_char(sysdate,'Day')='Sunday' THEN
vDate := sysdate-3;
[Code]...
if there is something missing or wrong.
I want to get the week of the year.
Conditions are:
1. Year's first week starts with first Sunday of the year. (6th Jan 2013 will be the starting week (week 1) in Year 2013)
2. 2nd Jan 2013 will be the last week of the previous year i.e. 2012 (either 52th or 53rd week)
At many place I found the below solution:
select to_char(to_date('01-JAN-2008','DD-MON-YYYY')+1,'IW') week_number from dual;
But its not working for the given dates (2nd Jan 2013, which should fall in the last week of 2012, and 6th Jan 2013, which should be the starting week of 2013).
I would like to design a date range query where the beginning of the week is always sunday and the end of the week is always monday.
View 3 Replies View RelatedSelect to_char(hiredate,'Day'),count(empno)
from empgroup by to_char(hiredate,'Day')order by to_char(hiredate,'Day')
select to_date('25-04-2012', 'DD-MM-YYYY') + rownum -1 dt
from dual
connect by level <= to_date('05-05-2012', 'DD-MM-YYYY') - to_date('25-04-2012', 'DD-MM-YYYY') + 1;
The above query returning the following output,
DT
04/25/2012
04/26/2012
04/27/2012
04/28/2012
04/29/2012
04/30/2012
05/01/2012
05/02/2012
05/03/2012
05/04/2012
05/05/2012
here I need to exclude the Dates which comes on 'saturday' and 'sunday' and also the common holiday..Here it is '01-May-2012' and I need the output like the following,
DT
04/25/2012
04/26/2012
04/27/2012
04/30/2012
05/02/2012
05/03/2012
05/04/2012
I need the common query to calculate between any two dates.
i need to write a function to eliminate SUNDAY AND SATURDAY;
My criteria is
if My date as (5/19/2012 ) and i want to add 10 days to it themn my function should return 06/01/2012
if My date as (5/13/2012 ) and i want to add 12 days to it themn my function should return 05/29/2012
Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53 24
2013 March loc1 55007 78 57 28
2013 April loc1 54345 72 58 77The code:
UPDATE missing_volume g
[Code]....
The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53
2013 March loc1 55007 78 57
2013 April loc1 54345 72 58 77
why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).
I given the table name,column name,datatype and sample record in the table. I have given the sample record for 01-jan-2008 to 8-Jan-2008, but in the real thing it will be for 30 years.
My Requirement:
For each class_no (202,203..), I need the missing date excluding weekends (sat, sun), I have provided the sample output below.
Table Name : ABC
Column Name : Class_no Data Type : Number
Column Name : Class_DateData Type : Date
Sample Record in the Table :
Class_noClass_Date
202 1-Jan-08
202 2-Jan-08
202 7-Jan-08
202 8-Jan-08
203 1-Jan-08
203 2-Jan-08
203 3-Jan-08
203 7-Jan-08
203 8-Jan-08
OUTPUT:
Class_noClass_Date
202 3-Jan-08
202 4-Jan-08
203 4-Jan-08
I need for each date sum the values from the begin of the year to present date. In January I will have the value of this month, on February I must sum the value of this month and the value of the month before, and so on, at the end of the year.
Date input
SELECT ID_CLIENT, DT_REG, VAL
FROM (
SELECT 1 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100401', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 220 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 500 as VAL FROM DUAL UNION
SELECT 3 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION
SELECT 3 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL);
Result
ID_CLIENTDT_REGVAL
101/01/2010200
101/02/2010300
101/03/2010500
101/04/2010650
101/05/2010750
201/01/2010100
201/03/2010320
201/05/2010820
301/02/2010150
301/05/2010250
I want to create a scheduler which should run from 8 am to 8 pm and every half an hour.How to do that. I tried dbms_scheduler because my procedure accepts two parameters.
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'in_program_schedule',
start_date => trunc(sysdate)+(1/3),-- it is not taking
repeat_interval => 'freq=minutely; interval=30; bysecond=0;',
END_DATE => trunc(sysdate)+(5/6),-- it is not taking
comments => 'Repeats every half an hour');
I want to run a procedure every day at 9 AM ,so how can i schedule it.
View 3 Replies View Relatedi want to schedule a job chain. I 've created 4 chain steps. The first perform an action and the second must be executed only if the first completed successfully.
The third must be executed only if the second ends successfully, etc.
The Chain_rule_2 is
CHAIN_STEP_1 Completed and CHAIN_STEP_1 SUCCEEDED
The chain_rule_3 is
CHAIN_STEP_2 Completed and CHAIN_STEP_2 SUCCEEDED
...
But the job is actually running because there isn't an end steps (I suppose). End step must be done if all steps works fine or if only one failed.
I am trying to schedule a job using DBMS_SCHEDULER.
The job has to start on SATURDAY 12:00 midnight and should run every on SATURDAY 12:00 midnight.
Even if I create the job on MONDAY first it should start on coming sunday and following sundays.
But I am getting the below error.
ORA-27452: BEGIN APPS_GLOBAL.POPULATE_TARGET144('APPS_XX','APPS_BE',5959); end; is an invalid name for a database object.
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 271
ORA-06512: at line 2
The Job script is
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'TEST_RAM',
job_type => 'STORED_PROCEDURE',
job_action => 'BEGIN APPS_GLOBAL.POPULATE_TARGET144(''APPS_XX'',''APPS_BE'',5959); END;',
[code]......
If I have a sql file containg 'EXECUTE Proc_Test' i.e or any other procedure then to call that sql file what I will mention in the job_type.
OR
If the sql file contains a procedure and I want to call that file through scheduler then what I need to mention in the job type.
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]....
How to set time interval in schedule job i need five minutes interval..
View 5 Replies View RelatedI have create a schedule using dbms_scheduler and the problem is its not running on exact time that is start time , i even changed the attribute as following but its not.Its running using dbms_scheduler.run_job(veh_insp) when run directly.The schedule is as follows.
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'INTERVAL_DAILY_2000',
attribute => 'start_date',
value => TRUNC(SYSDATE)+9.50/24);
END;
schedule the dbms_job to run every Wednesday in 20min interval. I am using below script but getting error
exec dbms_job.isubmit(44,'ER2.LOAD_CRID2_DATA;',trunc(sysdate),'trunc(SYSDATE,''WEDNESDAY'') + 20/1440),TRUE);
How to set a Schedule or run a Job where repeat_interval should be null.
Like Below
DECLARE
-- Create jobs.
M_PROC varchar2(4000);
P_POL_SYS_ID NUMBER;
P_POL_END_NO_IDX NUMBER;
P_POL_END_SR_NO NUMBER;
JOB_NO NUMBER;
[code].......
I am running now then Job is not getting created and not able to see it in SELECT * FROM dba_scheduler_jobs;
OS:Windows Server 2008
Database:Oracle 11gR2
I want to schedule a procedure in oracle and not from the Tash scheduler utility.Is there any way to schedule the procedure at database level.I want to run the procedure daily at specific time.
I have written small procedure here...
I used two tables in my procedure..TBLEMP and TBLORG..
STEP I :->
I try to insert record in both table TBLEMP and TBLORG...I written seperate BEGIN and END block for TBLEMP and TBLORG Insert operation...When some exception is occured then I rollback all the data..if not then insert statements are commited..
STEP II :->
On table TBLORG one trigger is written..that will execute on each new insert...In this trigger I get the current record(Current employee) data from TBLEMP
Now my question is for executing trigger is it necessary to commit the TBLEMP operation, before getting data from TBLEMP.
We have recently upgraded the database from Oracle 9i(9.2.0.8) to Oracle 11g (11.1.0.7).our environment:OS - AIX 5.3, application - COBOL server express 5.1-front end, which means cobol programs having embedded SQL connecting to Oracle database.
When executing programs, the a file with name "dbname_mmon_84444.trc" got generated in the "/trace" path with below message.
Unable to schedule a MMON slave at: Auto Flush Main 1
Slave was not permitted to be scheduled
- A slave for this action is already running.
ktte_monitor_ts: unable to schedule MMON Slave, error 100
ktte_monitor_ts: unable to schedule MMON Slave, error 100
I migrate schema with data from oracle 10gR2(Windows) to oracle RAC 11gR2 (Linux).My JOBS are created but they are not running on defined schedule. but they run manually successful.
----script of my job are as below --------
BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB
(job_name => 'JHON.TAX_IT);
END;
/
[code]...
I need to create a new schedule to export schema every day for Oracle on Linux Server. I am using OEM, But in Job Schedule of Export Schedule I can not see the repeat option.
View 1 Replies View Related