Fun with event based oracle sheduling

Goal: On  event triggered by application another job should execute

Step 1: Create job

1.1 Table job_output

create table job_output(systmstmp timestamp with time zone,msg varchar2(4000));

1.2* Event_test_proc procedure 

create or replace procedure event_test_proc(MESSAGE IN sys.scheduler$_event_info)as

begin
  insert into job_output values(systimestamp,message.object_name);
  insert into job_output values(systimestamp,message.event_type);
  commit;
end;
/

1.3 Test program for scheduler

BEGIN
  dbms_scheduler.create_program ( program_name => 'event_test_prog'
  , program_action=> 'event_test_proc'
  , program_type => 'stored_procedure'
  , number_of_arguments => 1
  , enabled => FALSE) ;
  dbms_scheduler.define_metadata_argument ( 'event_test_prog','event_message',1);
  dbms_scheduler.enable('event_test_prog');
END;
/

1.4 Add subscriber for oracle streams advance queue

begin
dbms_scheduler.add_event_queue_subscriber('my_agent');
end;
/

1.5** Event monitoring job 

BEGIN
  dbms_scheduler.create_job(job_name => 'event_test_job'
  , program_name => 'event_test_prog'
  , event_condition => 'tab.user_data.event_type = ''JOB_OVER_MAX_DUR'''
  , queue_spec =>'sys.scheduler$_event_queue,my_agent'
  , enabled=>true);
END;
/

Step 2*** : Now create a test job

BEGIN
  dbms_scheduler.create_job ( job_name => 'MAX_TIME_TEST'
  ,job_type => 'PLSQL_BLOCK'
  ,job_action => 'BEGIN         dbms_lock.sleep(90);      END;'
  ,start_date => SYSTIMESTAMP
  ,enabled => FALSE
  ,comments => 'TEST');
  dbms_scheduler.set_attribute(name => 'MAX_TIME_TEST'
  ,attribute => 'max_run_duration'
  ,value => interval '61' second);
  dbms_scheduler.set_attribute(name => 'MAX_TIME_TEST', attribute => 'raise_events',value => DBMS_SCHEDULER.job_all_events);
  dbms_scheduler.enable('MAX_TIME_TEST');
END;
/

Step 3 : Scheduler Monitoring view

select M.state,M.* from ALL_SCHEDULER_JOBS M where owner='RADHEY';

select * from ALL_SCHEDULER_JOB_LOG where owner='RADHEY' order by log_date desc;

select * from ALL_SCHEDULER_JOB_RUN_DETAILS where owner='RADHEY' order by req_start_date desc;

select * from ALL_SCHEDULER_RUNNING_JOBS where owner='RADHEY';

Step 4: Result time

select * from job_output order by 1 desc;

NOTE: 

*1. The program event_test_proc can be anything like email notification, stop running job , postpone job to run at later time etc. It is totally based on the requirement.
**2 event_type can be : "JOB_STARTED", "JOB_SUCCEEDED", "JOB_FAILED", "JOB_BROKEN", "JOB_COMPLETED", "JOB_STOPPED", "JOB_SCH_LIM_REACHED", "JOB_DISABLED", "JOB_CHAIN_STALLED", "JOB_OVER_MAX_DUR".
***3 attribute raise_events can not raise JOB_OVER_MAX_DUR event. max_run_duration attribute with non null value is required to raise this event.

Disclaimer: I am not the original author of above scripts but I have tested this on my machine and implemented in production system. The job in production stops long running job and schedules to run that job on later time. These notes are for my own reference.

all about partitioning

What is partition?

This is a wonderful concept provided by oracle to break larger tables/indexes/index organized tables etc.  into much smaller,manageable pieces.

When to partition?

1)Generally when the table grows more than 2 GB data are suitable candidate for partitioning
2)When table contains historical data and application is interest in a subset of data only.
3)When you want to spread the data of table to spread across the different storage device (obviously having different access speed)

4)Index partitioning is done mainly in view of manageability.
5)It provides performance, manageability and availability in case of index organized table as well.

Why partition ?
1)Performance [dramatic in case of ]
2)Manageability  [more DBA thing]
2)Availability  [more DBA thing]

Partition strategies offered by oracle

1)Range Partitioning
2)List Partitioning
3)Hash Partitioning

derived partitioning strategies
4)Interval Partitioning
5)Reference Partitioning
6)System Partitioning
7)Virtual column based Partitioning

     All these things has to be decided at the database design phase. Sometime we are not sure about the data access pattern, size of the database and off-course the tiered device available. Oracle has solution to this problem as well. We can use partition exchange mechanism to achieve this goal. It requires downtime to complete the process . But when we are very much concern about the downtime and really really don't want to ask for measurable downtime oracle provide another wonderful mechanism .
    There is oracle supplied dbms_redefinition package available in database that can be used to change the partition of table/index/index organized table online. We will do a small demonstration on changing the partition strategy of partitioned table.

Step 1: Ask DBA to provide necessary privilege on dbms_redefinition package.

grant execute on dbms_redefinition ;
create synonym dbms_redefinition for sys.dbms_redefinition.