CREATING SCHEDULER JOBS

-


DBA_SCHEDULER_JOB_RUN_DETAILS
DBA_SCHEDULER_JOBS
exec dbms_scheduler.disable('"ETCSSP"."CUSTOMER_UNIFIED_JOB2"');
EXEC DBMS_SCHEDULER.DROP_PROGRAM(program_name=>'"ETCSSP"."SP_UNVERIFIEDLOANS_MAPPED_JOB1"',FORCE=>TRUE);
EXEC DBMS_SCHEDULER.DROP_JOB (job_name => 'CUSTOMER_UNIFIED_JOB2');


SELECT OWNER,JOB_NAME,STATUS,ERROR# FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME='CUSTOMER_UNIFIED_JOB2';


BEGIN
sys.dbms_scheduler.create_job(
job_name => '"ETCSSP"."SP_UNVERIFIEDLOANS_MAPPED_JOB1"',
job_type => 'STORED_PROCEDURE',
job_action => '"ETCSSP"."SP_UNVERIFIEDLOANS_MAPPED"',
repeat_interval => 'FREQ=DAILY; BYHOUR=22; BYMINUTE=30; BYSECOND=0;',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'UNVERIFIED LOAN MAPPED',
auto_drop => FALSE,
enabled => FALSE);
END;

exec dbms_scheduler.set_attribute( name => '"ETCSSP"."SP_UNVERIFIEDLOANS_MAPPED_JOB"',attribute => 'restartable', value => TRUE);
exec dbms_scheduler.enable( '"ETCSSP"."SP_UNVERIFIEDLOANS_MAPPED_JOB"' );

-------------------------------------------------------
CUSTOMER_UNIFIED_VIEW  -
RL_RCFLOANSUMMARY_VIEW
RL_RHFLOANSUMMARY_VIEW



BEGIN
sys.dbms_scheduler.create_job(
job_name => '"ETCSSP"."CUSTOMER_UNIFIED_JOB"',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_MVIEW.REFRESH("CUSTOMER_UNIFIED_VIEW",''C''); END;',
repeat_interval => 'FREQ=HOURLY;INTERVAL=1',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'CUSTOMER_UNIFIED_JOB',
auto_drop => FALSE,
enabled => TRUE);
END;

exec dbms_scheduler.set_attribute( name => '"ETCSSP"."CUSTOMER_UNIFIED_JOB"',attribute => 'restartable', value => TRUE);
exec dbms_scheduler.enable( '"ETCSSP"."CUSTOMER_UNIFIED_JOB"' );


-------------------------------------------------
select OWNER,JOB_NAME,ENABLED,AUTO_DROP,STATE from DBA_SCHEDULER_JOBS where JOB_NAME like 'RL_RCFLOANSUMMARY_VIEW_JOB';

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"ETCSSP"."RL_RCFLOANSUMMARY_VIEW_JOB"',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_MVIEW.REFRESH("RL_RCFLOANSUMMARY_VIEW",''C''); END;',
repeat_interval => 'FREQ=HOURLY;INTERVAL=1',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'RL_RCFLOANSUMMARY_VIEW_JOB',
auto_drop => FALSE,
enabled => TRUE);
END;

exec dbms_scheduler.set_attribute( name => '"ETCSSP"."CUSTOMER_UNIFIED_JOB"',attribute => 'restartable', value => TRUE);
exec dbms_scheduler.enable( '"ETCSSP"."CUSTOMER_UNIFIED_JOB"' );
---------------------------------------------------
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"ETCSSP"."RL_RHFLOANSUMMARY_VIEW_JOB"',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_MVIEW.REFRESH("RL_RHFLOANSUMMARY_VIEW",''C''); END;',
repeat_interval => 'FREQ=HOURLY;INTERVAL=1',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'RL_RCFLOANSUMMARY_VIEW_JOB',
auto_drop => FALSE,
enabled => TRUE);
END;

exec dbms_scheduler.set_attribute( name => '"ETCSSP"."RL_RHFLOANSUMMARY_VIEW_JOB"',attribute => 'restartable', value => TRUE);
exec dbms_scheduler.enable( '"ETCSSP"."RL_RHFLOANSUMMARY_VIEW_JOB"' );


begin
dbms_scheduler.run_job('"ETCSSP"."RL_RHFLOANSUMMARY_VIEW_JOB"',TRUE);
end;
----------------------------------------------------------------------------------------

DBA_SCHEDULER_JOB_RUN_DETAILS
DBA_SCHEDULER_JOBS
exec dbms_scheduler.disable('"ETCSSP"."CUSTOMER_UNIFIED_JOB2"');
EXEC DBMS_SCHEDULER.DROP_PROGRAM(program_name=>'"ETCSSP"."SP_UNVERIFIEDLOANS_MAPPED_JOB1"',FORCE=>TRUE);
EXEC DBMS_SCHEDULER.DROP_JOB (job_name => 'CUSTOMER_UNIFIED_JOB2');

select OWNER,JOB_NAME,SCHEDULE_OWNER,SCHEDULE_NAME,REPEAT_INTERVAL,ENABLED,AUTO_DROP,STATE,RUN_COUNT from dba_scheduler_jobs where JOB_NAME='OIS_MERGE_DFS_DATA_JOB';
SELECT OWNER,JOB_NAME,STATUS,ERROR# FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME='OIS_MERGE_DFS_DATA_JOB';
select OWNER,JOB_NAME,SCHEDULE_OWNER,ENABLED,AUTO_DROP,STATE,RUN_COUNT from dba_scheduler_jobs;

1) OIS_MERGE_DFS_DATA
2) OIS_MERGE_TRANCHES


BEGIN
sys.dbms_scheduler.create_job(
job_name => '"OISUAT"."OIS_MERGE_DFS_DATA_JOB"',
job_type => 'STORED_PROCEDURE',
job_action => '"OISUAT"."OIS_MERGE_DFS_DATA"',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'OIS_MERGE_DFS_DATA',
auto_drop => FALSE,
enabled => FALSE);
END;

exec SYS.dbms_scheduler.set_attribute( name => '"OISUAT"."OIS_MERGE_DFS_DATA_JOB"',attribute => 'restartable', value => TRUE);
exec SYS.dbms_scheduler.enable( '"OISUAT"."OIS_MERGE_DFS_DATA_JOB"' );



BEGIN
sys.dbms_scheduler.create_job(
job_name => '"OISUAT"."OIS_MERGE_TRANCHES_JOB"',
job_type => 'STORED_PROCEDURE',
job_action => '"OISUAT"."OIS_MERGE_TRANCHES"',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'OIS_MERGE_TRANCHES',
auto_drop => FALSE,
enabled => FALSE);
END;

exec SYS.dbms_scheduler.set_attribute( name => '"OISUAT"."OIS_MERGE_TRANCHES_JOB"',attribute => 'restartable', value => TRUE);
exec SYS.dbms_scheduler.enable( '"OISUAT"."OIS_MERGE_TRANCHES_JOB"' );


BEGIN
DBMS_SCHEDULER.run_JOB (job_name =>'"OISUAT"."OIS_MERGE_TRANCHES_JOB"');
END;



BEGIN
sys.dbms_scheduler.create_job(
job_name => '"OISUAT"."OIS_MERGE_TRANCHES_JOB"',
job_type => 'STORED_PROCEDURE',
job_action => '"OISUAT"."OIS_MERGE_TRANCHES"',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'OIS_MERGE_TRANCHES',
auto_drop => FALSE,
enabled => FALSE);
END;

exec SYS.dbms_scheduler.set_attribute( name => '"OISUAT"."OIS_MERGE_TRANCHES_JOB"',attribute => 'restartable', value => TRUE);
exec SYS.dbms_scheduler.enable( '"OISUAT"."OIS_MERGE_TRANCHES_JOB"' );


BEGIN
DBMS_SCHEDULER.run_JOB (job_name =>'"E_AUCTION"."PACK_AVG_SALE_PRICE_JOB"');
END;
-------------------------------
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"E_AUCTION"."PACK_AVG_SALE_PRICE_JOB"',
job_type => 'STORED_PROCEDURE',
job_action => '"E_AUCTION"."PACK_AVG_SALE_PRICE"',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'PACK_AVG_SALE_PRICE',
auto_drop => FALSE,
enabled => FALSE);
END;

exec SYS.dbms_scheduler.set_attribute( name => '"E_AUCTION"."PACK_AVG_SALE_PRICE_JOB"',attribute => 'restartable', value => TRUE);
exec SYS.dbms_scheduler.enable( '"E_AUCTION"."PACK_AVG_SALE_PRICE_JOB"' );


BEGIN
DBMS_SCHEDULER.run_JOB (job_name =>'"E_AUCTION"."PROC_REPO_DATA_UPLOAD"');
END;


=======================================


BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'PACK_AVG_SALE_PRICE_JOB',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'DECLARE
 
V_BATCH_ID         NUMBER(18);
V_COUNT            NUMBER(18);
V_CODE             VARCHAR2(200);
V_ERR              VARCHAR2(200);
BEGIN

E_AUCTION.PACK_AVG_SALE_PRICE.PROC_AVG_SALE_PRICE_DATA ;
 
COMMIT;

END; ',
   start_date           => '14-APRIL-2016 01:00:00.000000 PM +05:30',
   repeat_interval      => 'FREQ=DAILY; BYHOUR=8; BYMINUTE=30; BYSECOND=0;',
   end_date             =>  NULL,
   enabled              =>  TRUE,
   comments             => 'PACK_AVG_SALE_PRICE_JOB');
END;
/




BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'PACK_LOS_CSG_STAG_JOB',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'DECLARE
 
V_BATCH_ID         NUMBER(18);
V_COUNT            NUMBER(18);
V_CODE             VARCHAR2(200);
V_ERR              VARCHAR2(200);
BEGIN

CSG_SAVVPRD2.PACK_LOS_CSG_STAG.PROC_LOS_CSG_STAG_DATA;

COMMIT;

END; ',
   start_date           => '10-NOVEMBER-2016 01:00:00.000000 PM +05:30',
   repeat_interval      => 'FREQ=MINUTELY;INTERVAL=30;',
   end_date             =>  NULL,
   enabled              =>  TRUE,
   comments             => 'PACK_LOS_CSG_STAG_JOB run every 30 min');
END;
/



DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X
     ,what      => 'PACK_AVG_SALE_PRICE.PROC_AVG_SALE_PRICE_DATA;'
     ,next_date => to_date('17/07/2016 08:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'trunc(sysdate+1)+8/24'
     ,no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/

commit;

------------
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job        => X
,what      => 'PACK_SIP_DATA.PROC_SIP_DATA;'
,next_date => to_date('07/20/2016 13:20:00','mm/dd/yyyy hh24:mi:ss')
,interval  => 'sysdate + 10/1440'
,no_parse  => FALSE
);
END;
/
commit;


======================================================

'FREQ=DAILY; BYHOUR=8; BYMINUTE=30; BYSECOND=0;'



Run on the next to last day of every month:
FREQ=MONTHLY; BYMONTHDAY=-2;

Run on March 10th (Both examples are equivalent):
FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10;
FREQ=YEARLY; BYDATE=0310;

Run every January 10, 11, 12, 13 and 14 (Both examples are equivalent):
FREQ=YEARLY; BYDATE=0110,0111,0112,0113,0114
FREQ=YEARLY; BYDATE=0110+SPAN:5D;


BEGIN
sys.dbms_scheduler.create_job(
job_name => '"ORSP"."ORSP_DUMP_MIGRATION_JOB"',
job_type => 'STORED_PROCEDURE',
job_action => '"ORSP"."ORSP_DUMP_MIGRATION"',
repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=1,2,3,4,5; BYHOUR=5; BYMINUTE=00; BYSECOND=0',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'ORSP_DUMP_MIGRATION run at 5Am on 1 to 5 month start day',
auto_drop => FALSE,
enabled => FALSE);
END;

exec SYS.dbms_scheduler.set_attribute( name => '"ORSP"."ORSP_DUMP_MIGRATION_JOB"',attribute => 'restartable', value => TRUE);
exec SYS.dbms_scheduler.enable( '"ORSP"."ORSP_DUMP_MIGRATION_JOB"' );

EXEC DBMS_SCHEDULER.DROP_JOB (job_name => 'ORSP_DUMP_MIGRATION_JOB');




BEGIN
sys.dbms_scheduler.create_job(
job_name => '"ORSP"."TEMP_ORSP_DUMP_MIGRATION_JOB"',
job_type => 'STORED_PROCEDURE',
job_action => '"ORSP"."ORSP_DUMP_MIGRATION"',
repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=15; BYHOUR=16; BYMINUTE=18; BYSECOND=0',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'ORSP_DUMP_MIGRATION run at 5Am on 1 to 5 month start day',
auto_drop => FALSE,
enabled => FALSE);
END;
exec SYS.dbms_scheduler.enable( '"ORSP"."TEMP_ORSP_DUMP_MIGRATION_JOB"' );

Comments

Post a Comment

Popular posts from this blog

ORACLE GOLDENGATE

-
Image
1)  GoldenGate Setup & Configuration On Source & Target Hosts Perform the Following On Source: Unzip the GG Software and Perform the installation & Configuration [oracle@targethost]/data # unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip [oracle@targethost]/data # tar -xvf ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip [oracle@sourcehost]/data/ggate # ./ggsci GGSCI (targethost) 1> create subdirs 1. Ensure The database is in archive log mode 2. Create Necessary tablespace and goldengate user in database SQL> create tablespace ggs_data datafile '/data/GGS/Base/ggs_data01.dbf' size 200m; SQL> create user ggs_owner identified by ggs_owner default tablespace ggs_data temporary tablespace temp; SQL> grant connect,resource to ggs_owner; SQL> grant select any dictionary, select any table to ggs_owner; SQL> grant create table to ggs_owner; SQL> grant flashback any table to ggs_owner; SQL> grant execute on dbms_flashback to
Read more

ORACLE RAC QUESTION

-
Image
                                  RAC               What   is   Cluster ? A cluster provides an optional method of storing table data. A cluster is made up of a group of tables that share the same data blocks. The tables are grouped together because they share common columns and are often used together          OR Two or more computers that share resources and work together to form a larger logical computing unit. RAC and Oracle Parallel Server can be used to access Oracle from multiple nodes of a clustered system. What is   oracle clusterware ? Oracle Clusterware is software that enables servers to operate together as if they are one server. Each server looks like any standalone server. However, each server has additional processes that communicate with each other so the separate servers appear as if they are one server to applications and end users. Why RAC, what are the benifits of using RAC? The benefits of Real Application Clusters: Ability to spread CPU
Read more

EXADATA ARCHITECTURE IN ORACLE

-
Image
OVERVIEW :                You can use Exadata to consolidate your storage environment Using Exadata multiple  databases can use storage from a single pool. Exadata uses Oracle Automatic Storage Management (ASM) to evenly distribute the storage load for every database across every available disk in the storage pool. Every database can use all the available disks to maximize performance. Exadata requires the use of Oracle Database 11g Release 2. Exadata works equally well with single-instance or Oracle Real Application Clusters (RAC) databases. Users and database administrators use the same tools and knowledge they are already familiar with. Being based on industry-standard components and technologies, Exadata is inexpensive to deploy. In addition, tight integration with the full suite of Oracle Database high-availability features, ensures that the reliability and integrity needs of mission-critical environments are met.              A key advantag
Read more