Nancy
From: "Stephane Faroult" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: archive old data Date: Fri, 12 Sep 2003 09:34:25 -0800
In an ideal world, tables are partitioned and you just have to archive partitions - either to other tables in an ARCHIVE schema, or by exporting them - and then you can truncate them (=partitions). In the real world, you always have some rows at a very old date which are *not* in the CLOSED state. Which means that partitioning on a state with row movement enabled is not necessarily to be frowned upon.
If you don't have enormous amounts of data, CREATE TABLE AS SELECT to save the data to archive somewhere. Rather than deleting the rows afterwards, it's probably better to do a CREATE TABLE AS SELECT elsewhere with the rows you want to keep, then TRUNCATE the table, then reinsert the rows back - it will have the advantage of reorganizing and resetting the high-water mark. Of course you will have to juggle with constraints and triggers.
If you feel lazy, just convince your management that since disk space is so cheap nowadays they can keep everything online.
HTH
SF
>----- ------- Original Message ------- ----- >From: "Nancy Hu" <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Fri, 12 Sep 2003 08:49:31 > >We have some tables that have data for many years. >We are going to archive >the data that are older than 3 years. I would like >to find out how you guys >usually do this or a best way to do this. Thanks >for any inputs in advance. > >Nancy > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
_________________________________________________________________
Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nancy Hu INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
