Home » SQL & PL/SQL » SQL & PL/SQL » how to get unupdated records?
how to get unupdated records? [message #330755] Tue, 01 July 2008 02:26 Go to next message
h_jitendras
Messages: 36
Registered: October 2006
Member
Hi,
I'm updating some old(history) records in a table- that too some attributes in the table.Now during this exercise,I want to write the records which are not updated to a flat file.Writing the records to a flat file is ok, but how do I get the unupdated records in the 1st place?
Any ideas?
Re: how to get unupdated records? [message #330761 is a reply to message #330755] Tue, 01 July 2008 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
non-updated = whole table minus updated.

Regards
Michel
Re: how to get unupdated records? [message #330780 is a reply to message #330761] Tue, 01 July 2008 03:51 Go to previous messageGo to next message
h_jitendras
Messages: 36
Registered: October 2006
Member
Hi,
yeah thats fine.. but i'm not sure how i'd get the updated records or for that matter the unupdated records?we do have a timestamp field in the table which i'm not sure we can update, but then i need a way to find out the unupdated records?

Re: how to get unupdated records? [message #330790 is a reply to message #330780] Tue, 01 July 2008 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You posted a generic question, you have a generic answer.
With what you posted noone can help you more.

Regards
Michel
Re: how to get unupdated records? [message #330834 is a reply to message #330790] Tue, 01 July 2008 08:07 Go to previous messageGo to next message
h_jitendras
Messages: 36
Registered: October 2006
Member
thanks for your time.
Re: how to get unupdated records? [message #330950 is a reply to message #330834] Tue, 01 July 2008 22:36 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oracle does not store the number of times a row has been updated, or even whether it has been updated at all.

Possible solutions that come to mind are:
- Metadata: Have an INSERTED_DATE and UPDATED_DATE on the table and compare them. You could maintain them with triggers possibly.

- Materialised view log: Records all updates, inserts, and deletes in another table. This would be a gross mis-use of the technology though, and wourld preclude you ever creating a fast-refresh materialised view over that table. I only mention it because it's dead easy.

- Use Oracle's Auditing option. Updates can be audited and written to an audit table.

- Use Oracle Change Data Capture. Updates are captured out of the Redo Logs and written to a table that can be on the same database.

All of these require some action on your part to initialise and will only be useful from the date you implement them. They will not magically work on historical data.

Ross Leishman
Previous Topic: Decode with conditions
Next Topic: Error in displaying data from PL/SQL type
Goto Forum:
  


Current Time: Wed Dec 07 04:45:45 CST 2016

Total time taken to generate the page: 0.08300 seconds