Newsgroups: comp.databases.oracle.misc Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!news.glorb.com!sws1!ornl!news.er.usgs.gov!news From: Brian Peasland Subject: Re: automated history with Oracle? In-Reply-To: X-Nntp-Posting-Host: edc-cv-160-58.cr.usgs.gov Content-Type: text/plain; charset=ISO-8859-1; format=flowed Message-ID: User-Agent: Thunderbird 1.5.0.2 (Windows/20060308) Sender: news@igsrsparc2.er.usgs.gov (Janet Walz (GD) x6739) Content-Transfer-Encoding: 7bit Organization: U.S. Geological Survey, Reston VA References: Mime-Version: 1.0 Date: Sat, 6 May 2006 15:08:11 GMT Lines: 49 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:127158 Danny Gerst wrote: > Hi! > > Thx for your answers. > > Well, I work with 10g and there will be at most one transaction every > minute. I need the historyfunvtion one a regular bases nearly one or twice a > month. Their want to get an overview over all changes that had be done on a > table and an overview of all old records. So the new one will be in the > regular database, while the history should show all the old records. > It like saving the current record in a separate table and than apply the > changes. > > First I have to check the two last suggestion from Brian. But I am open for > every another possibility to make that happen. > > > Greetings, > > Danny > > Since you are using 10g, another option is to leverage something called a Flashback Versions Query. http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_flashback.htm#sthref1477 This will give you all of the changes made to a table between two SCN's or timestamps. This can be used with Flashback Transaction Query to Undo a change if necessary. This relies on your UNDO tablespace, so you'll need to query the records before your UNDO_RETENTION starts expiring the information. HTH, Brian -- =================================================================== Brian Peasland oracle_dba@nospam.peasland.net http://www.peasland.net Remove the "nospam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three" - Unknown