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 ?
No comments:
Post a Comment