Re: Data Archiving

• Nirmal Kumar Muthu Kumaran <NIRMALK_at_qtel.com.qa>

> Hi all,
>
> New year Greetings.
>
> We need to archive some history tables from the production database to new
> server. Totally around 50 history
> tables we need to move and each table having around 10 million of records.
> Some of history tables having records, which are required for another a
> year the production database, approximatedly i can say around 2 million of
> records in each table
> like this.
>
> So what is the best way to move these tables?.
>
> I suggested them to export/import tables and delete invalid records.
>
> But another team are planned to do it by a simple pl/sql script by making
> cursor, check validity of record, if yes insert into new server and then
> delete from production, repeat.

It will be far less work to do the same thing via DBI if you want to stay w/in the running oracle server.

You can dump the stuff out to disk, filter it to new files and re-insert it also (i.e., combine your export and their filtering methods).

Main problem with single-row methods is that latency between fetches will greatly increase the processing time -- this has nothing to do with bandwidth, it's the turnaround time required to re-access the curser, move it to the next record, etc.

If you dump the data out delimeted (vs. fixed field) it will save time by decreasing the disk writes. You can then access the external files using perl and split /$delimieter/o, $line to get the fields, examine them to your heart's content and then print them to another delimeted file. After that suck up the delimeted files and you're done with it. I've used that method in data warehousing projects and it works well enough. If the product Fast Unload for Oracle ("FUO") still exists it does a faster job of dumping the data out; used to be a Platinum Tech. product.

--
Steven Lembark                               2930 W. Palmer
Workhorse Computing                       Chicago, IL 60647
                                            +1 800 762 1582
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Lembark
  INET: lembark_at_wrkhors.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).