Hello,
I would like to know if someone has already developped a SAS scprit to estimate the last time an oracle table was updated.
Please provide an example.
Regards,
I have tried that in Toad: SELECT (MAX(ora_rowscn)) from MRKT_???.QUALTRIX_??????; The response is: 6479526695024 select scn_to_timestamp(6479526695024) as timestamp from dual; ora-08181; specific number is not a valid system change number ora-06512 : at sys.scn_to_timestamp, line 1
How can I get the last updated date
Googling the question provides this:
SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) from myTable;
Apparently this only works for 'recent' updates so taking the max value is important.
Hi @alepage
Typically, such questions should be directed to your Oracle DBA. But here is what I was able to find on this page Using Oracle Flashback Technology
Using ORA_ROWSCN
ORA_ROWSCN is a pseudocolumn of any table that is not fixed or external. It represents the SCN of the most recent change to a given row; that is, the latest COMMIT operation for the row. For example:
SELECT ora_rowscn, last_name, salary
FROM employees
WHERE employee_id = 7788;
ORA_ROWSCN NAME SALARY
---------- ---- ------
202553 Fudd 3000
The latest COMMIT operation for the row took place at approximately SCN 202553. To convert an SCN to the corresponding TIMESTAMP value, use the function SCN_TO_TIMESTAMP.
Hope this helps,
Ahmed
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.