fastest and easiest way to do it is the following. create another tablespace with another user. Set it to nologging.
alter session enable parallel dml; create table /*+ parallel(max#,s) */ as select * from old_tablespace.table where -- your filter. export this new tablespace so you have a full backup. Store it on multiple tapes.(in case one dies). then delete the records from your old tablespace that you just keep that history tablespace in the database and dont give anyone access to it. You never know when someone might want the data. > > From: "Stephen Andert" <[EMAIL PROTECTED]> > Date: 2003/09/12 Fri PM 01:24:24 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re: archive old data > > Nancy, > > As with so many things, it depends. > > If you *might* need some of the data back, then you need to be able to > recover it. That means if you do an export, you need to know what > version it is and track whether that table ever has any changes. If it > does change, you will need to jump through more hoops to restore the > data. If the version of oracle changes, you need to be aware of > differences when importing data from older versions and should probably > do a test before the older version of the database goes away. Safest > would probably be to create a flat file (comma-delimited or something > like that) and use SQL*Loader or external tables should you need to > recover. > > I would try and get a "safe" date beyond which no one will ever expect > recovered data. For example, do you really need line items for orders > that are 5 years old? Do you need insurance policy information for > policies that expired 10 years ago? Do you need information on > employees that left 4 or more years ago? The sooner you can get that > information from your business users, the sooner you'll be able to start > enforcing data removal. If you can handle that in the database, then > partitioning based on the removal date would be useful and let you use > partitioning to accomplish the purge easily. > > Good luck. > > Stephen > > >>> [EMAIL PROTECTED] 09/12/03 09:49AM >>> > 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 > > _________________________________________________________________ > Fast, faster, fastest: Upgrade to Cable or DSL today! > https://broadband.msn.com > > -- > 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). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Stephen Andert > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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).