12 August, 2020

ORA_ROWSCN

 As a follow up to my previous post on SCN_TO_TIMESTAMP, here is a demo of the ORA_ROWSCN function.

I have two different sessions and two different tables where I insert one row each.  I then delay the COMMIT in each session.


This is the first session :

oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 12 18:02:47 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Wed Aug 12 2020 18:02:31 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

18:02:47 SQL> create table table_a
18:02:51   2  (table_name varchar2(32), insert_scn number, insert_timestamp timestamp);

Table created.

18:03:10 SQL> insert into table_a
18:03:14   2  select 'TABLE_A', current_scn, systimestamp from v$database
18:03:32   3  /

1 row created.

18:03:33 SQL> select * from table_a
18:03:37   2  /

TABLE_NAME                       INSERT_SCN INSERT_TIMESTAMP
-------------------------------- ---------- ---------------------------------------------------------------------------
TABLE_A                             6580147 12-AUG-20 06.03.33.263180 PM

18:03:38 SQL>
18:05:16 SQL> !sleep 120

18:07:21 SQL>
18:07:26 SQL> commit;

Commit complete.

18:07:28 SQL>


And this is the second session :

oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 12 18:04:27 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Wed Aug 12 2020 18:03:32 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

18:04:27 SQL> create table table_b
18:04:36   2  (table_name varchar2(32), insert_scn number, insert_timestamp timestamp);

Table created.

18:04:46 SQL> insert into table_b
18:04:51   2  select 'TABLE_B',current_scn, systimestamp from v$database;

1 row created.

18:05:03 SQL> select * from table_b
18:05:09   2  /

TABLE_NAME                       INSERT_SCN INSERT_TIMESTAMP
-------------------------------- ---------- ---------------------------------------------------------------------------
TABLE_B                             6581390 12-AUG-20 06.05.03.813011 PM

18:05:10 SQL>
18:05:24 SQL> !sleep 30

18:06:00 SQL>
18:06:07 SQL>
18:06:13 SQL> commit;

Commit complete.

18:06:16 SQL>


So, the second session, against TABLE_B did the INSERT after the first session but issued a COMMIT before the first session.  (TABLE_B has a higher INSERT_SCN and INSERT_TIMESTAMP than TABLE_A).

Let's see what ORA_ROWSCN shows :
< br />
SQL> select table_name, insert_scn, insert_timestamp, scn_to_timestamp(ora_rowscn)
  2  from table_a
  3  /

TABLE_NAME                       INSERT_SCN INSERT_TIMESTAMP
-------------------------------- ---------- ---------------------------------------------------------------------------
SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
TABLE_A                             6580147 12-AUG-20 06.03.33.263180 PM
12-AUG-20 06.07.26.000000000 PM


SQL> select table_name, insert_scn, insert_timestamp, scn_to_timestamp(ora_rowscn)
  2  from table_b
  3  /

TABLE_NAME                       INSERT_SCN INSERT_TIMESTAMP
-------------------------------- ---------- ---------------------------------------------------------------------------
SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
TABLE_B                             6581390 12-AUG-20 06.05.03.813011 PM
12-AUG-20 06.06.14.000000000 PM


SQL>


The actual INSERT into TABLE_B was after that in TABLE_A  (higher INSERT_SCN and INSERT_TIMESTAMP)  but SCN_TO_TIMESTAMP of the ORA_ROWSCN implies that the row in TABLE_B is earlier than that in TABLE_A !

SQL> select table_name, insert_scn, insert_timestamp, ora_rowscn
  2  from table_a
  3  /

TABLE_NAME                       INSERT_SCN INSERT_TIMESTAMP                                                            ORA_ROWSCN
-------------------------------- ---------- --------------------------------------------------------------------------- ----------
TABLE_A                             6580147 12-AUG-20 06.03.33.263180 PM                                                   6586905

SQL> select table_name, insert_scn, insert_timestamp, ora_rowscn
  2  from table_b
  3  /

TABLE_NAME                       INSERT_SCN INSERT_TIMESTAMP                                                            ORA_ROWSCN
-------------------------------- ---------- --------------------------------------------------------------------------- ----------
TABLE_B                             6581390 12-AUG-20 06.05.03.813011 PM                                                   6584680

SQL>


The actual SCN recorded is that of the COMMIT time, *not* the INSERT time.

A database session gets an SCN for the Transaction it does when it COMMITs.
So, even though the INSERT into TABLE_A was earlier, it has a higher SCN simply because the COMMIT was issued later.


Does it matter if I use the ROWDEPENDENCIES extended attribute for the table  ? Without ROWDEPENDENCIES, ORA_ROWSCN actually uses the SCN in the block header -- irrespective of when each row in the block was inserted / updated.
In my scenario, I had a new table with only 1 row, so there would be no difference.

Nevertheless, I repeat the experiment with ROWDEPENDENCIES.


oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 12 18:17:57 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Wed Aug 12 2020 18:17:47 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

18:17:57 SQL> create table table_a
18:18:10   2
18:18:10 SQL> create table table_a
18:18:14   2  (table_name varchar2(32), insert_scn number, insert_timestamp timestamp) rowdependencies;

Table created.

18:18:31 SQL> insert into table_a
18:18:40   2  select 'TABLE_A', current_scn, systimestamp from v$database
18:18:50   3  /

1 row created.

18:18:51 SQL>
18:20:11 SQL> !sleep 60

18:21:13 SQL>
18:21:15 SQL> commit;

Commit complete.

18:21:16 SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>



and


oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 12 18:19:30 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Wed Aug 12 2020 18:19:04 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

18:19:30 SQL> create table table_b
18:19:33   2  (table_name varchar2(32), insert_scn number, insert_timestamp timestamp) rowdependencies;

Table created.

18:19:40 SQL> insert into table_b
18:19:52   2  select 'TABLE_B',current_scn, systimestamp from v$database;

1 row created.

18:20:00 SQL>
18:20:16 SQL> !sleep 30

18:20:49 SQL>
18:20:51 SQL> commit;

Commit complete.

18:20:52 SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>



resulting in :


SQL> select table_name, insert_scn, insert_timestamp, ora_rowscn
  2  from table_a
  3  /

TABLE_NAME                       INSERT_SCN INSERT_TIMESTAMP                                                            ORA_ROWSCN
-------------------------------- ---------- --------------------------------------------------------------------------- ----------
TABLE_A                             6612380 12-AUG-20 06.18.51.562927 PM                                                   6618886

SQL> select table_name, insert_scn, insert_timestamp, ora_rowscn
  2  from table_b
  3  /

TABLE_NAME                       INSERT_SCN INSERT_TIMESTAMP                                                            ORA_ROWSCN
-------------------------------- ---------- --------------------------------------------------------------------------- ----------
TABLE_B                             6614592 12-AUG-20 06.20.00.141122 PM                                                   6617807

SQL>


Bottom line : A row that is inserted (or updated) earlier can still have a higher SCN (and, therefore, show a higher SCN_TO_TIMESTAMP)  simply because the user or the application program issued the COMMIT later.   Even an application or batch job may run multiple queries or DMLs before finally issuing a COMMIT.

Caveat : ORA_ROWSCN does not necessarily return the *exact* SCN as of the time of the COMMIT. Because of the complexities of the SCN, the actual value returned might still be slightly different.

No comments: