CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Arihant
Arihant

Reputation: 3

How to identify recently updated records in Oracle?

How do I query Oracle(11.2.0.4.0) database to identify records updated in last few minutes? There is no column to store last modified timestamp and I am not looking for a query to identify records "inserted" in last few minutes rather last "updated".

Let's say for table A with 10 records, B is the Primary Key. There is a column 'enable' having values 1 or 0. 9 records have value for enable 1 and 1 record has value 0. If I update one of the record(having enable=1) to 0 ,then how to identify this particular updated record ?

Below query gives 2 records (should return only 1 record) -

select * from A
where enable = 0
  and B in (select B from A as of TIMESTAMP (SYSTIMESTAMP - INTERVAL'5'MINUTE));

Upvotes: 0

Views: 2869

Answers (1)

MT0
MT0

Reputation: 168741

Its difficult to follow what exactly your requirements without some sample data but I think you want it to compare the table to find the rows that have changed from the value they had 5 minutes ago (which you can do using MINUS) and then to filter to only show the rows where the ENABLE value is 0. Something like:

SELECT *
FROM   (
  SELECT * FROM A
  MINUS
  SELECT * FROM A AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL '5' MINUTE )
)
WHERE  ENABLE = 0;

Upvotes: 2

Related Questions