Hi Guys,

Need your help.

I have 4-5 different tables in Oracle database(11g). I have to copy the data from all these tables from there(1st DB) to another database(2nd DB). Source database is having partitioned tables and data in one table could be linked/reference to another row in another table like foreign key.

I am trying to create a procedure, reason being : I need to schedule a job from Java program which will run at sometime and will execute this procedure which will copy the data from 1st database to 2nd database(with all partitions and references)

After the data is copied, I need to delete the data from 1st database so that the space could be recovered.

The data to be copied will be based on condition like copy all data older than 1 year for which I have written the SQL as

select * from schema1.table1 where last_updt_ts < add_months(SYSDATE, -12);

Basically, its a database archiving thing and I need to achieve it through mix of Java job scheduling and a stored procedure.

Source and Destination dB would be Oracle. But I also have to test this between Oracle(source) and MySql(destination).

Can anyone please give me some pointers?

Thanks
Nitin

create or replace procedure arch is
begin
  insert into t@arch_db
    select * from t 
    where  upd_dt < add_months(sysdate, -12);

  delete t
  where  upd_dt < add_months(sysdate, -12);
end arch;
/

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'arch_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN arch; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=monthly;',
    enabled         => TRUE);
END;
/