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
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.