archive & Purge of table [message #617300] |
Fri, 27 June 2014 01:37 |
balaji123
Messages: 29 Registered: October 2009 Location: sanfrancisco
|
Junior Member |
|
|
I have a non-partition table HISTORY_trans - 300 gb oracle table
I wanted to archive --> keep Last 3 months data
Purge --> keep last 3 months ago data.
I thought of doing below steps
1. create HISTORY_ARCH table based on select HISTORY_trans structure.
2. validate HISTORY_trans & HISTORY_ARCH has same record count.
3. truncate HISTORY_trans
4. insert HISTORY_trans table record from HISTORY_ARCH where last 3 months data..
5. commit;
6. delete last 3 months data from HISTORY_ARCH.
7. commit;
8. Gather stat
Is there any best practice to archive and purge 300 gb non-partition table data with minimal database downtime.
Please advise . I love to here your suggestion please.
thanks
Bala
[Updated on: Fri, 27 June 2014 01:43] Report message to a moderator
|
|
|
|
Re: archive & Purge of table [message #617304 is a reply to message #617300] |
Fri, 27 June 2014 02:43 |
John Watson
Messages: 8929 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Instead of your 1-2-3 which might take hours, this will take seconds:
rename HISTORY_trans to HISTORY_ARCH;
create table HISTORY_trans as select * from HISTORY_ARCH where 1=2;
|
|
|
Re: archive & Purge of table [message #617306 is a reply to message #617304] |
Fri, 27 June 2014 02:51 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Why not combine 4 in while you're at it:
rename HISTORY_trans to HISTORY_ARCH;
create table HISTORY_trans as select * from HISTORY_ARCH where last three months;
|
|
|
|
Re: archive & Purge of table [message #617499 is a reply to message #617307] |
Mon, 30 June 2014 15:24 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
if you do the nologging, make sure to turn logging back or it will not be recoverable. and remember that a CTAS will only copy the structure. all foreign keys, indexes, and primary keys will NOT be built.
|
|
|
|
|