order of a record in oracle

Question

Is it possible to find, in oracle, which record was entered first, which second, and so on? This is not necessarily the SCN of the record, since I don't care about the exact time. I just want to order the records by the order in which they were entered.

Answer 1

During all my years with Oracle, I have trusted more on my own developed logic and coding practices. In this particular scenario, rather than (ab)using the database, it would be a good practice, to have two DATE columns. One for DDL changes and another for DML changes.

I usually name the COLUMNS as DT_TM_STAMP and DT_TM_STAMP_CRTE for DML and DDL changes respectively. The datetimestamp create column is not going to change that frequently as compared to the datetimestamp column. Since, we all expect DML activity to be more than DDL . At any moment, if we want to know when was a particular record inserted, updated or deleted, all we need to do is look for the datetimestamp column.

Update I was not clear with my explanation about the two datetime columns. Thanks to Jeffrey Kemp to point it out that all I was talking about was DML and not DDL.

Test case

There are two columns, dt_tm_stamp_crte to store the datetime of insertion of row, and dt_tm_stamp to keeep a track of all DML changes to the row. So, the first time when a row is inserted, both the columns would have same datetime, however, from next DML operation, only the dt_tm_stamp column would be updated with current datetime.

SQL> alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

Session altered.

SQL> DROP TABLE t;

Table dropped.

SQL> CREATE TABLE t AS SELECT * FROM emp WHERE 1 = 2;

Table created.

SQL> ALTER TABLE t ADD (dt_tm_stamp DATE, dt_tm_stamp_crte DATE);

Table altered.

SQL> INSERT INTO t SELECT e.*, SYSDATE, SYSDATE FROM emp e;

14 rows created.

SQL> SELECT ename, dt_tm_stamp, dt_tm_stamp_crte FROM t;

ENAME      DT_TM_STAMP         DT_TM_STAMP_CRTE
---------- ------------------- -------------------
SMITH      10/10/2014 13:19:53 10/10/2014 13:19:53
ALLEN      10/10/2014 13:19:53 10/10/2014 13:19:53
WARD       10/10/2014 13:19:53 10/10/2014 13:19:53
JONES      10/10/2014 13:19:53 10/10/2014 13:19:53
MARTIN     10/10/2014 13:19:53 10/10/2014 13:19:53
BLAKE      10/10/2014 13:19:53 10/10/2014 13:19:53
CLARK      10/10/2014 13:19:53 10/10/2014 13:19:53
SCOTT      10/10/2014 13:19:53 10/10/2014 13:19:53
KING       10/10/2014 13:19:53 10/10/2014 13:19:53
TURNER     10/10/2014 13:19:53 10/10/2014 13:19:53
ADAMS      10/10/2014 13:19:53 10/10/2014 13:19:53
JAMES      10/10/2014 13:19:53 10/10/2014 13:19:53
FORD       10/10/2014 13:19:53 10/10/2014 13:19:53
MILLER     10/10/2014 13:19:53 10/10/2014 13:19:53

14 rows selected.

SQL> BEGIN
  2    DBMS_LOCK.sleep(10);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> UPDATE t SET ename = 'LALIT', dt_tm_stamp = SYSDATE WHERE ename = 'SCOTT';

1 row updated.

SQL> SELECT ename, dt_tm_stamp, dt_tm_stamp_crte FROM t WHERE ename = 'LALIT';

ENAME      DT_TM_STAMP         DT_TM_STAMP_CRTE
---------- ------------------- -------------------
LALIT      10/10/2014 13:20:03 10/10/2014 13:19:53

SQL>

The dt_tm_stamp column update could be done via TRIGGER. I have just demonstrated the significance of the columns, how to achieve it is up to the developer.

Answer 2

In general, not unless you have some column that provides that information (e.g. create_date).

If you can assume that rows are never updated and your table was built with ROWDEPENDENCIES, you could order by the ORA_ROWSCN. But it's pretty rare that you would have such a table so that's not a particularly useful answer in general.