Hemant's Oracle DBA Blog

14 August, 2020

ORA_ROWSCN -- 2 : Multi Table, Multiple Rows

Instead of the single-table, single-row demonstration in my previous blog post, this demonstrates how ORA_ROWSCN is presented when a single transaction (i.e. one COMMIT at the end of multiple DML statements) presents the same SCN for all the rows in all the tables that were involved.

oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 14 22:39:25 2020
Version 19.3.0.0.0

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

Last Successful login time: Fri Aug 14 2020 22:39:20 +08:00

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

22:39:25 SQL> create table t_1 (txn_scn number) rowdependencies;

Table created.

22:39:48 SQL> create table t_2(txn_scn number) rowdependencies;

Table created.

22:40:00 SQL> create table t_3 (txn_scn number) rowdependencies;

Table created.

22:40:07 SQL> create table t_4 (txn_scn number) rowdependencies;

Table created.

22:40:12 SQL>
22:40:12 SQL> insert into t_1
22:40:27   2  select current_scn
22:40:31   3  from v$database, dual
22:40:36   4  connect by level < 11
22:40:40   5  /

10 rows created.

22:40:40 SQL> insert into t_2
22:40:43   2  select current_scn
22:40:50   3  from v$database
22:40:53   4  /

1 row created.

22:40:53 SQL> !sleep 10

22:41:06 SQL> insert into t_3
22:41:14   2  select current_scn
22:41:18   3  from v$database, dual
22:41:22   4  connect by level < 6
22:41:28   5  /

5 rows created.

22:41:29 SQL> !sleep 30

22:42:01 SQL> select distinct (current_scn)
22:42:12   2
22:42:21 SQL>
22:42:21 SQL> select distinct (txn_scn)
22:42:25   2  from t_1
22:42:28   3  /

   TXN_SCN
----------
   6664390

22:42:28 SQL> select distinct (txn_scn)
22:42:36   2  from t_2
22:42:38   3  /

   TXN_SCN
----------
   6664419

22:42:40 SQL> select distinct (txn_scn)
22:42:44   2  from t_3
22:42:46   3  /

   TXN_SCN
----------
   6665530

22:42:46 SQL> insert into t_4
22:42:51   2  select * from t_1
22:42:53   3  /

10 rows created.

22:42:54 SQL> select distinct (txn_scn)
22:42:58   2  from t_4
22:43:00   3  /

   TXN_SCN
----------
   6664390

22:43:01 SQL> !sleep 60

22:44:05 SQL> update t_2
22:44:21   2  set txn_scn = (select current_scn from v$database)
22:44:36   3  /

1 row updated.

22:44:37 SQL> select txn_scn
22:44:42   2  from t_2
22:44:45   3  /

   TXN_SCN
----------
   6672139

22:44:46 SQL>
22:44:46 SQL>
22:45:07 SQL> select distinct(txn_scn) from t_1;

   TXN_SCN
----------
   6664390

22:45:27 SQL> select distinct(txn_scn) from t_2;

   TXN_SCN
----------
   6672139

22:45:33 SQL> select distinct(txn_scn) from t_3;

   TXN_SCN
----------
   6665530

22:45:41 SQL> select distinct(txn_scn) from t_4;

   TXN_SCN
----------
   6664390

22:45:45 SQL> insert into t_1
22:45:54   2  select current_scn from v$database;

1 row created.

22:46:04 SQL> update t_1
22:46:07   2  where txn_Scn != 6664390
22:46:16   3
22:46:20 SQL>
22:46:27 SQL> update t_1
22:46:30   2  st txn_scn = (select current_scn from v$database)
22:46:40   3  where txn_scn != 6664390
22:46:51   4  /
st txn_scn = (select current_scn from v$database)
   *
ERROR at line 2:
ORA-00971: missing SET keyword


22:46:52 SQL> update t_1
22:46:58   2  set txn_scn = (select current_scn from v$database)
22:47:03   3  where txn_scn != 6664390
22:47:09   4  /

1 row updated.

22:47:10 SQL> select txn_scn, count(*)
22:47:16   2  from t_1
22:47:18   3  group by txn_scn
22:47:22   4  /

   TXN_SCN   COUNT(*)
---------- ----------
   6683784          1
   6664390         10

22:47:23 SQL> !sleep 15
22:47:42 SQL>
22:47:54 SQL> commit;

Commit complete.

22:47:59 SQL>

The CURRENT_SCN that is inserted into each of the 4 tables is different (I had multiple other transactions running from other sessions to forcefully increment the SCNs).

What ORA_ROWSCN values do we see after the COMMIT ?

22:48:57 SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 14 22:49:05 2020
Version 19.3.0.0.0

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

Last Successful login time: Fri Aug 14 2020 22:48:57 +08:00

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

22:49:05 SQL> select txn_scn, ora_rowscn
22:49:27   2  from t_1
22:49:29   3  /

   TXN_SCN ORA_ROWSCN
---------- ----------
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6683784    6686983

11 rows selected.

22:49:30 SQL>  select txn_scn, ora_rowscn
22:49:40   2  from t_1
22:49:44   3  order by 1,2
22:49:48   4  /

   TXN_SCN ORA_ROWSCN
---------- ----------
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6683784    6686983

11 rows selected.

22:49:48 SQL>
22:49:48 SQL> select txn_scn, ora_rowscn
22:50:09   2  from t_2
22:50:11   3  order by 1,2
22:50:13   4  /

   TXN_SCN ORA_ROWSCN
---------- ----------
   6672139    6686983

22:50:14 SQL> select txn_scn, ora_rowscn
22:50:19   2  from t_3
22:50:21   3  order by 1,2
22:50:22   4  /

   TXN_SCN ORA_ROWSCN
---------- ----------
   6665530    6686983
   6665530    6686983
   6665530    6686983
   6665530    6686983
   6665530    6686983

22:50:23 SQL> select txn_scn, ora_rowscn
22:50:29   2  from t_4
22:50:32   3  order by 1,
22:50:34   4  2
22:50:36   5
22:50:37 SQL> /

   TXN_SCN ORA_ROWSCN
---------- ----------
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983
   6664390    6686983

10 rows selected.

22:50:37 SQL>

Every single row in all 4 tables has the same ORA_ROWSCN that was set when the COMMIT was issued. So, for any DML which spans more than 1 row and/or more than 1 table, *all* the rows affected will have the same ORA_ROWSCN, irrespective of the actual CURRENT_SCN at the beginning of the first DML statement and the CURRENT_SCN at the end of the last DML statement.

However, remember that DDL statement (CREATE, ALTER, DROP etc) cause a COMMIT to be issued immediately. So, if I had a DDL statement in the middle of the session, the ORA_ROWSCN for rows impacted after the DDL would be different from those for rows impacted before the DDL.

Note, however, if the client program had set AUTOCOMMIT ON, every statement would result in a COMMIT and fresh ORA_ROWSCN. So, the ORA_ROWSCN values for these same SQL operations could have varied if I were using a client program (.e.g a JDBC connection) that had AUTOCOMMIT ON. Also, session exit/disconnect/reconnect may issue a COMMIT depending on whether the client program issues a COMMIT on closing the session.

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.

What other operations can you think that would implicit commits, resulting in varying ORA_ROWSCNs ?

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.

11 August, 2020

SCN_TO_TIMESTAMP

A quick demo of SCN_TO_TIMESTAMP in 19c

 
 oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 11 21:59:56 2020
Version 19.3.0.0.0

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

Last Successful login time: Mon Aug 10 2020 16:08:38 +08:00

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

SQL> select scn_to_timestamp(5389994) from dual;

SCN_TO_TIMESTAMP(5389994)
---------------------------------------------------------------------------
11-AUG-20 09.53.44.000000000 PM

SQL>
SQL> select scn_to_timestamp(5389994-100000) from dual;

SCN_TO_TIMESTAMP(5389994-100000)
---------------------------------------------------------------------------
12-JUL-20 11.19.13.000000000 PM

SQL>
SQL> select scn_to_timestamp(32720) from dual;
select scn_to_timestamp(32720) from dual
       *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1


SQL>

If you query for an older SCN, you would get an ORA-08181 error. What is an "older SCN" ?

Technically, Oracle frequently inserts new rows into SYS.SMON_SCN_TIME and deletes older rows. This is the table that is queried by the SCN_TO_TIMESTAMP function. So, if you query for an SCN no longer present in the table, you get an ORA-08181 error.

Does Oracle insert every SCN into this table ? Of course not ! Else there would have been more 5million rows in the table in my database. It periodically inserts rows. When you run the SCN_TO_TIMESTAMP function, you get an approximate timestamp -- an estimate that Oracle derives from reading "nearby" rows.

Do not ever assume that SCN_TO_TIMETAMP returns an Exact Timestamp for that SCN.

For a range of potential SCNs, you can query V$ARCHIVED_LOG for FIRST_TIME (which is still in DATE format, not TIMESTAMP) and FIRST_CHANGE# (which is the first SCN recorded for that ArchiveLog).