Move table/records to another database

[foxbox]

I have a table with 100.000.000 records, where 'only' 25.000.000 are needed. I don't want to delete those 75.000.000 records, i want them removed from my production environment database into an 'archive-database'. Every quarter or so i will move reocrds from production to archive. So i only need to backup archive 4 times a year, and my production environment backup is smaller. At the moment thoose 100.000.000 records are in every backup...
So basically: how to i set these records completely apart?

 

[Turkbear]

Hi,
What distinguishes those records from the ones that are needed? If some field value, then use that to insert them into the archive database and then you can delete them from the production one ( after assuring that they were actually inserted into the archive).





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Dagon]

The quickest approach might be to copy the 25m into a new table, delete them from the old table and then rename the two tables to have the correct names. If you're going to do something like that, I'd advise dropping all indexes, triggers, constraints etc against the tables before commencing and then re-create them at the end.

For Oracle-related work, contact me through Linked-In.

 

[Thargy]

I would suggest that you use the presumed criterion mentioned above and do CTAS (Create Table As Select) to produce the target 75 million row table. Do another CTAS to mak a copy of the 25 million you want to keep, and then drop the original table.

Rename the 25 million record table to be that of the just-dropped table, and reinstate indexes etc in parallel at your leisure.

Regards

T

 

[foxbox]

By now i did this:
- remove all constraints, indexes, etc, except 1 for my CTAS select
- CTAS for 75m
- CTAS for 25m
- Rename original
- rename 25m table
- reindex, constraints etc

Is it oke to create a seperate tablespace and move the 75m table to that tablespace? That more or less sets the 75m apart. If i cleanup quarterly, i only have to backup that that tablespace file quarterly...
This action will not shrink the tablespace with production data? But moving out 75m records will free up space within that tablespace?

 

[Thargy]

Fox,

moving the 75 million off to a once-per-quarter backup tablespace is an excellent move. Data warehouses use a similar technique to keep the size of their backup pieces manageable.

If you're using locally managed tablespaces, then Oracle will automatically mark the space as being available (free). This won't reduce the size of the OS file.

Regards

T