Oracle-L: Methods for purging large table

From: oracle dba <oracle12i_at_hotmail.com>
Date: Thu, 14 Mar 2002 08:28:43 -0800
Message-ID: <F001.0042966A.20020314082843@fatcity.com>

Hi All,

Sorry for reposting this again. I think my last message's subject was not on target. Basically, I am trying to find out the different ways to purge a large transcation table. I have two methods so far as described below. Are there any other methods/tricks that people use to purge large transaction table? What did people do before the partitioning option was available?

Thanks again.

My original posting:

Hi All,

I am wondering how other people implement the following. I have a large transaction table in the OLTP database which will get millions of records per day. I then transfer it to the data warehouse via replication. The table in data warehouse is partitioned. Now the table on OLTP side is also partitioned so the purge of old data is just a matter of dropping partition. Is the partition used on the OLTP side an overkill? Someone (a consultant) suggested using two tables and build a synonym to point to one. Then when midnight comes we drop the synonym and point to another table. We can then truncate the previous table. However, I can't afford to lose any transaction during the swtiching of synonym point to one table from another, even though it only takes a second to do so.

Any thoughts?

Thanks.

Send and receive Hotmail on your mobile device: http://mobile.msn.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: oracle dba
INET: oracle12i_at_hotmail.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). Received on Thu Mar 14 2002 - 10:28:43 CST