CODEWITHSUNDEEP
oracle-databasearchiveforall
user2899615
user2899615

Reputation: 51

Archiving data in oracle

i am trying to Archive my data from one table to another.Please find below my requirement.

  1. I have a table A and another table B.

  2. I need to find all the records from A which is less than a particular date

  3. After identifying the records ,i need to move the Records to table B

  4. Once the data is moved to Table B,I need to delete those records from table A.

I am planning to use a Stored procedure with the number of days to archive as parameter.

Now i need to check for the errors while inserting in the table A and should not delete those records in table B and also if the records is successfully inserted in Table A and if fails in the deletion of Table B.Then i need to rollback the record inserted in Table A.

I need to archive on a daily basis and there will at least a million records to archive.

I started with the coding by using the forall and save exceptions but struck with the logic .

Can anyone help me with this logic.

Upvotes: 0

Views: 1575

Answers (1)

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

First of all, I'm doubt if such 'archieving' is a good idea. It seems like transferring soup from one plate to another using teaspoon. There are better decisions exist for almost every task, say, using partitioning and maybe exchange partition.

But if you immovably wish to do this, you should write something like this:

procedure Move_Many_Records is
begin
  savepoint MMR;
  insert /*+ APPEND */ into TARGET (fields)
    select fields from SOURCE where {condition};
  delete from SOURCE
    where id in (select id from TARGET);
  savepoint MMR;
exception
  when others then
    rollback to savepoint MMR;
    My_Alerts.Shit_Happens('Failed to move records!');
    raise;
end;

Upvotes: 1

Related Questions