Skip to Main Content
  • Questions
  • Order rows of a table in the same order they have inserted

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rahul.

Asked: April 22, 2017 - 7:02 am UTC

Last updated: April 22, 2017 - 9:10 am UTC

Version: oracle 11g

Viewed 1000+ times

You Asked

Hi Tom,

Is there any way to get the rows of a table in the same order they have inserted, without using any sequence number or timestamp (trying to find out if oracle has some inbuilt functionalities to perform the action)?

and Connor said...

Not really. If you create the table with the rowdependencies option, you'll get the SCN stamped on the rows, but any number of rows could have the same SCN because that is a transactional entity not a row entity, and of course there is a large difference between the moment you *inserted* a row, and the moment you *commit* a row.

SQL> create table t ( id int ) rowdependencies;

Table created.

SQL>
SQL> insert into t values (10) ;

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> insert into t values (20) ;

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> insert into t values (5) ;

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> insert into t values (40) ;

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> insert into t values (105);

1 row created.

SQL> insert into t values (106);

1 row created.

SQL> insert into t values (107);

1 row created.

SQL> insert into t values (108);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select ora_rowscn, t.* from t;

          ORA_ROWSCN         ID
-------------------- ----------
      14815791844798         10
      14815791844800         20
      14815791844802          5
      14815791844804         40
      14815791844806        105
      14815791844806        106
      14815791844806        107
      14815791844806        108

8 rows selected.



Is this answer out of date? If it is, please let us know via a Comment