topic Re: how to know when an Oracle table was last updated in SAS Enterprise Guide

how to know when an Oracle table was last updated

alepage — Tue, 27 Feb 2024 20:34:24 GMT

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

Re: how to know when an Oracle table was last updated

SASKiwi — Tue, 27 Feb 2024 21:05:36 GMT

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.

Re: how to know when an Oracle table was last updated

AhmedAl_Attar — Tue, 27 Feb 2024 21:08:21 GMT

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