what is the best way.....

[UKDBA]

Hi
We have few tables which have millions of records in it. we want to archive the data in antoher table (partial data) so that we can archive it acassionally. we are thinking of having a copy of table and copy all the old data in it and then delete that data from the live table .live table will have only like one month of data and all the previous data will go on archive table. we need to do the archiving of data on daily basis to archive table and deleteing it from the live tables. what is the best way to do that.
one is running pl/sql script which will insert into archive tables and delete it from the live table
or do incremental export of data.(i never used it before)
or is there any other way of doing that.

Let me know what you think
thanks
UKDBA

[pando]

well you dont need two tables, I guess you can partition the table and export the partitions

[anandkl]

Use job packages to run it at a specific time.

cheese
anandkl

[UKDBA]

is there any option in 7344.

[ocpwannabe]

upgrade an option?

[hanselrl]

One of the best and fastest way in doing it is to partition your table by date. Then create another partition table with the same structure as the live table this will be the table that will hold the archive data. Then create another table (not partition) also with same structure this will serve as a temp table.

Do the ff steps..

1. exchange partition of the live table with the temp table
2. exchange temp table with the partition of the archive table.

this procedure is much much faster than exporting or inserting data.