Find the last update date of a record

Since 10g, Oracle provides a new pseudocolumn ORA_ROWSCN, For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated.
SCN_TO_TIMESTAMP is a function used to convert the SCN to a readable date format.

SELECT empno, ename, ora_rowscn, scn_to_timestamp(ora_rowscn)
  FROM emp
 WHERE ename = 'SMITH' ;
 
EMPNO ENAME      ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
----- ---------- ---------- -----------------------------
 7369 SMITH       239054665 14/05/12 15:26:07,000000000

Note that when the SCN is too old, you could get the following error : ORA-08181: specified number is not a valid system change number

How to query on LONG datatype columns

You can't search LONG data types columns directly, due to its restrictions, Oracle recommends that you convert existing LONG columns to LOB columns.

In this example, I need to search all the mviews where query like '%foo%', since the column QUERY is a LONG, the first step is to re-create the Dictionary Data Table ALL_MVIEWS and convert using to_lob() function :

CREATE TABLE all_mviews_clob AS SELECT mview_name, to_lob(query) AS query 
FROM all_mviews ;

--now I am able to search on the QUERY column
SELECT * FROM all_mviews_clob WHERE upper(query) LIKE '%FOO%' ;