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).
< 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:
Post a Comment