[ORACLE] select last rows

oracle table에서 마지막 변경된 row와 그 변경 시간 찾기.

16:49:53 SQL> create table check_dml_time     
16:50:22   2  (
16:50:24   3   fix_column varchar2(10)
16:50:38   4  ,test_column varchar2(10)
16:50:49   5  )
16:50:50   6  ;

Table created.

Elapsed: 00:00:00.02
16:50:51 SQL> insert into check_dml_time values ('FIX','FIRST');

1 row created.

Elapsed: 00:00:00.01
16:51:21 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
16:51:23 SQL> select created, last_ddl_time from user_objects where object_name = upper('check_dml_time');

CREATED           LAST_DDL_TIME
----------------- -----------------
20100726 16:50:51 20100726 16:50:51

1 row selected.

Elapsed: 00:00:00.02
16:52:01 SQL> select ora_rowscn from check_dml_time;

          ORA_ROWSCN
--------------------
      11466381392945

1 row selected.

Elapsed: 00:00:00.01
16:52:31 SQL> select scn_to_timestamp(ora_rowscn) from check_dml_time;

SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
2010/07/26 16:51:23

1 row selected.

Elapsed: 00:00:00.01
16:53:31 SQL> update check_dml_time 
16:53:43   2     set test_column = 'UPDATED'
16:53:50   3  ;

1 row updated.

Elapsed: 00:00:00.01
16:53:55 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
16:54:00 SQL> select scn_to_timestamp(ora_rowscn) from check_dml_time;

SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
2010/07/26 16:53:59

1 row selected.

Elapsed: 00:00:00.00

Tested : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

원문 링크 : http://laurentschneider.com/wordpress/2006/08/select-last-rows.html