This is easily done with the date datatype or timestamp datatype.

Ex:

select empno,ename,to_char(hiredate,’dd mon yyyy’) as hiredate
from emp
where hiredate =( select max(hiredate) from emp );

EMPNO      ENAME      HIREDATE
———- ———- ———–
7876       ADAMS      23 may 1987

Unfortunately, if both the above mentioned datatype not available with same table then we use ‘ora_rowscn’.

ORA_ROWSCN: Is pseudo column & used to find-out scn of that row or multiple rows committed once in single block.
Consider following query in this we create table with ROWDEPENDENCIES. using scn_to_timestamp function we can find-out last inserted or updated query.

 

SQL> CREATE TABLE demo (n NUMBER (9)) ROWDEPENDENCIES;

Table created.

SQL> insert into demo values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select n,ora_rowscn,scn_to_timestamp(ora_rowscn) from demo;

N          ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
———- ———- —————————————————————-
1          1049810    13-AUG-12 01.49.16.000000000 PM

SQL> insert into demo values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select n,ora_rowscn,scn_to_timestamp(ora_rowscn) from demo;

N          ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
———- ———- ——————————————————————-
1          1049810    13-AUG-12 01.49.16.000000000 PM
2          1049951    13-AUG-12 01.51.10.000000000 PM

SQL> insert into demo values(3);

1 row created.

SQL> commit;
Commit complete.

SQL> select n,ora_rowscn,scn_to_timestamp(ora_rowscn) from demo;

N          ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
———- ———- ——————————————————————–
1          1049810    13-AUG-12 01.49.16.000000000 PM
2          1049951    13-AUG-12 01.51.10.000000000 PM
3          1050018    13-AUG-12 01.52.53.000000000 PM

Now create view on demo table for easily fetch record who recently inserted or updated.

SQL> create or replace view demoview

as select n,ora_rowscn,scn_to_timestamp(ora_rowscn) as timestamp

from demo;

View created.

Give following query to find-out latest updated or inserted record.

select n,ora_rowscn,timestamp
from demoview
where timestamp = ( select max(timestamp) from demoview )

N          ORA_ROWSCN TIMESTAMP
———- ———- ——————————————————————-
3          1050018    13-AUG-12 01.52.53.000000000 PM

With the help of ORA_ROWSCN column & scn_to_timestamp function we can easily find-out recently inserted row from any oracle table.
NOTE:

1. If single row are committed then we can find-out recent inserted or updated row.
2. If multiple rows are committed then we can not find-out exact row to be inserted or updated.

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.