Skip to content

Instantly share code, notes, and snippets.

@shivallan

shivallan/Archive_job

Last active August 29, 2015 14:20
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save shivallan/82af224d72783e631bc8 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save shivallan/82af224d72783e631bc8 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Archive_job
create or replace procedure CAMPAIGN_archivator
as
begin
/*CONTACTHISTORY*/
/*Copy old data from original table*/
insert into CAMPAIGN.UA_CONTACTHISTORY_PERSON_HIST
select db.*
from CAMPAIGN.UA_CONTACTHISTORY_PERSON db
where (extract (day from (current_timestamp - db.UPDATEDATETIME))) >= 183;
/*Delete old data from original table*/
delete from CAMPAIGN.UA_CONTACTHISTORY_PERSON
where (extract (day from (current_timestamp - UPDATEDATETIME))) >= 183;
commit;
/*Copy old data from original table*/
insert into CAMPAIGN.UA_CONTACTHISTORY_VISIT_HIST
select db.*
from CAMPAIGN.UA_CONTACTHISTORY_VISIT db
where (extract (day from (current_timestamp - db.UPDATEDATETIME))) >= 183;
/*Delete old data from original table*/
delete from CAMPAIGN.UA_CONTACTHISTORY_VISIT
where (extract (day from (current_timestamp - UPDATEDATETIME))) >= 183;
commit;
/*/CONTACTHISTORY*/
/*RESPONSEHISTORY*/
/*Copy old data from original table*/
insert into CAMPAIGN.UA_RESPONSEHISTORY_PERSON_HIST
select db.*
from CAMPAIGN.UA_RESPONSEHISTORY_PERSON db
where (extract (day from (current_timestamp - db.RESPONSEDATETIME))) >= 183;
/*Delete old data from original table*/
delete from CAMPAIGN.UA_RESPONSEHISTORY_PERSON
where (extract (day from (current_timestamp - RESPONSEDATETIME))) >= 183;
commit;
/*Copy old data from original table*/
insert into CAMPAIGN.UA_RESPONSEHISTORY_VISIT_HIST
select db.*
from CAMPAIGN.UA_RESPONSEHISTORY_VISIT db
where (extract (day from (current_timestamp - db.RESPONSEDATETIME))) >= 183;
/*Delete old data from original table*/
delete from CAMPAIGN.UA_RESPONSEHISTORY_VISIT
where (extract (day from (current_timestamp - RESPONSEDATETIME))) >= 183;
commit;
/*/RESPONSEHISTORY*/
/*DTLCONTACTHIST*/
/*Copy old data from original table*/
insert into CAMPAIGN.UA_DTLCONTACTHIST_PERSON_HIST
select db.*
from CAMPAIGN.UA_DTLCONTACTHIST_PERSON db
where (extract (day from (current_timestamp - db.UPDATEDATETIME))) >= 183;
/*Delete old data from original table*/
delete from CAMPAIGN.UA_DTLCONTACTHIST_PERSON
where (extract (day from (current_timestamp - UPDATEDATETIME))) >= 183;
commit;
/*Copy old data from original table*/
insert into CAMPAIGN.UA_DTLCONTACTHIST_VISIT_HIST
select db.*
from CAMPAIGN.UA_DTLCONTACTHIST_VISIT db
where (extract (day from (current_timestamp - db.UPDATEDATETIME))) >= 183;
/*Delete old data from original table*/
delete from CAMPAIGN.UA_DTLCONTACTHIST_VISIT
where (extract (day from (current_timestamp - UPDATEDATETIME))) >= 183;
commit;
/*/DTLCONTACTHIST*/
end;
commit;
begin
dbms_scheduler.create_job (
job_name => 'JOB_CAMPAIGN_ARCHIVE',
job_type => 'PLSQL_BLOCK',
job_action => 'begin CAMPAIGN_archivator; end;',
start_date => systimestamp,
repeat_interval => 'FREQ = DAILY; BYHOUR = 2; BYMINUTE = 0; BYSECOND = 0',
enabled => true
);
end;
commit;
BEGIN
DBMS_SCHEDULER.DROP_JOB (
job_name => 'JOB_CAMPAIGN_ARCHIVE');
END;
select * from user_jobs;
select * from USER_SCHEDULER_JOBS;
select db.* from CAMPAIGN.UA_RESPONSEHISTORY_PERSON_HIST db;
select db.* from CAMPAIGN.UA_RESPONSEHISTORY_PERSON db;
select db.* from CAMPAIGN.UA_CONTACTHISTORY_PERSON db;
select db.* from CAMPAIGN.UA_CONTACTHISTORY_PERSON_HIST db;
select db.* from CAMPAIGN.UA_DTLCONTACTHIST_PERSON db;
select db.* from CAMPAIGN.UA_DTLCONTACTHIST_PERSON_HIST db;
DROP TABLE CAMPAIGN.UA_RESPONSEHISTORY_PERSON_HIST purge;
DROP TABLE CAMPAIGN.UA_CONTACTHISTORY_PERSON_HIST purge;
DROP TABLE CAMPAIGN.UA_DTLCONTACTHIST_PERSON_HIST purge;
DROP TABLE CAMPAIGN.UA_RESPONSEHISTORY_VISIT_HIST purge;
DROP TABLE CAMPAIGN.UA_CONTACTHISTORY_VISIT_HIST purge;
DROP TABLE CAMPAIGN.UA_DTLCONTACTHIST_VISIT_HIST purge;
create table CAMPAIGN.UA_RESPONSEHISTORY_PERSON_HIST
as
select db.* from CAMPAIGN.UA_RESPONSEHISTORY_PERSON db
where 1=0;
create table CAMPAIGN.UA_CONTACTHISTORY_PERSON_HIST
as
select db.* from CAMPAIGN.UA_CONTACTHISTORY_PERSON db
where 1=0;
create table CAMPAIGN.UA_DTLCONTACTHIST_PERSON_HIST
as
select db.* from CAMPAIGN.UA_DTLCONTACTHIST_PERSON db
where 1=0;
create table CAMPAIGN.UA_RESPONSEHISTORY_VISIT_HIST
as
select db.* from CAMPAIGN.UA_RESPONSEHISTORY_VISIT db
where 1=0;
create table CAMPAIGN.UA_CONTACTHISTORY_VISIT_HIST
as
select db.* from CAMPAIGN.UA_CONTACTHISTORY_VISIT db
where 1=0;
create table CAMPAIGN.UA_DTLCONTACTHIST_VISIT_HIST
as
select db.* from CAMPAIGN.UA_DTLCONTACTHIST_VISIT db
where 1=0;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment