Note: all tests are done in Oracle 12.1.0.2.0.
Update (2020-October-27):
If we use flashback_transaction_query (which requires minimum supplemental logging and archivelogs), we can also get the exact COMMIT_SCN.
select xid, start_scn, start_timestamp, commit_scn, commit_timestamp
from flashback_transaction_query
where table_name = 'TEST_TAB' and rownum = 1;
XID START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP
---------------- ----------------- ------------------- ----------------- -------------------
550002009EC10000 9,714,099,942,712 27-10-2020:10:46:14 9,714,099,942,724 27-10-2020:10:46:20
1. Setup
drop table test_tab;
drop table commitscn_gtt;
create table test_tab(id number, sid number);
create global temporary table commitscn_gtt(dummy)
on commit preserve rows
as select -1 from dual;
create or replace procedure push_commitscn as
begin
delete from commitscn_gtt;
insert into commitscn_gtt (dummy) values (-1);
end;
/
create or replace function get_commitscn return number as
l_commitscn number;
begin
select ora_rowscn into l_commitscn from commitscn_gtt where rownum=1;
return l_commitscn;
end;
/
2. Test
insert into test_tab (id, sid) values (1, sys.dbms_support.mysid);
-- run immediately before commit
exec push_commitscn;
commit;
set numformat 999,999,999,999,999
-- get exact commitscn after commit
select get_commitscn from dual;
GET_COMMITSCN
--------------------
9,183,757,165,446
3. Comparing with Other Approaches
Test below comprises 5 different commit scn getting methods.
drop table test_tab2;
create table test_tab2(id number, sid number, scn number) rowdependencies;
insert into test_tab2 (id, sid, scn) values (1, sys.dbms_support.mysid, userenv('commitscn'));
exec push_commitscn;
commit;
set numformat 999,999,999,999,999
select scn from test_tab2 where id = 1;
SCN
--------------------
9,183,757,165,468
select ora_rowscn from test_tab2 where id = 1;
ORA_ROWSCN
--------------------
9,183,757,165,469
select get_commitscn from dual;
GET_COMMITSCN
--------------------
9,183,757,165,469
select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
9,183,757,165,471
select current_scn from v$database;
CURRENT_SCN
--------------------
9,183,757,165,472
Method 1. USERENV('COMMITSCN')
select scn from test_tab2 where id = 1;→ USERENV('COMMITSCN') is the actual COMMIT SCN minus 1, i.e immediately scn before commit.
Each commit creates 1 new number (for commit Record).
→ Undocumented
→ 12c USERENV deprecated
Method 2. ORA_ROWSCN
select ora_rowscn from test_tab2 where id = 1;→ Oracle Database SQL Language Reference - ORA_ROWSCN Pseudocolumn said:
Whether at the block level or at the row level, the ORA_ROWSCN should not be considered to be an exact SCN.
If a block is queried twice, then it is possible for the value of ORA_ROWSCN to change between the queries
even though rows have not been updated in the time between the queries. The only guarantee is that the value of
ORA_ROWSCN in both queries is greater than the commit SCN of the transaction that last modified that row.
(Note: "greater than" seems an Oracle Document error. It should be "not less than")
So ORA_ROWSCN is not deterministic because returned scn also depends on query start scn
due to preious discussed Delayed Block Cleanout.
Hence it can decrease even there are not any user updates on the selected table.
It can increase if there are any committed updates between two queries as demonstrated in
How can ORA_ROWSCN change between queries when no update? at Jul 8, 2017 9:20 PM.
→ For each row, ORA_ROWSCN returns the conservative upper bound SCN of the most recent change to the row.
For example, "delayed block cleanout".
→ It increases the size of each row by 6 bytes.
→ Oracle BUG: INSERT to a Table With ROWDEPENDENCIES Failed With ORA-00600 [kdtgsph-row] (Doc ID 2210391.1)
Method 3. Commit SCN of this Blog
select get_commitscn from dual;→ Exact commit SCN.
Once a commit is executed, this single row (single block) is stamped with that commit SCN.
Since the one-row GTT is session local, it can never be selected by other sessions
and is hardly to imagine a "delayed block cleanout". Therefore it never be changed.
Method 4. System functions
select dbms_flashback.get_system_change_number from dual; select current_scn from v$database;→ Upper bound system change number (SCN), not precise.
4. Commit SCN exposed
Blog: PL/SQL Function Result Cache Invalidation observed that commit accomplishes invalidating Result Cache before returns, and interpreted:
The above Stack Trace shows that when a transaction user session calls commit command,
commit takes a detour to visit Result Cache along its code path in order to perform
the invalidation before publishing the confirmed and consistent news to the world.
The following test will demonstrate that Commit SCN is exposed into V$RESULT_CACHE_OBJECTS.SCN during Result Cache Invalidation.
drop table rc_tab;
create table rc_tab (id number, val number);
insert into rc_tab select level, level*10 from dual connect by level <= 3;
commit;
create or replace function get_val (p_id number) return number result_cache as
l_val number;
begin
select val into l_val from rc_tab where id = p_id;
return l_val ;
end;
/
create or replace procedure run_test as
l_val number;
begin
for i in 1 .. 3 loop
l_val := get_val(i);
end loop;
end;
/
exec dbms_result_cache.flush;
exec run_test;
column name format a13
select id, type, status, name, namespace, creation_timestamp, scn
from v$result_cache_objects ro
order by scn desc, type, id;
ID TYPE STATUS NAME NAMESPACE CREATION_TIMESTAMP SCN
-- ---------- --------- ------------- ---------- -------------------- --------------------
0 Dependency Published K.GET_VAL 2017*JUL*12 07:06:43 9,198,309,753,651
2 Dependency Published K.RC_TAB 2017*JUL*12 07:06:43 9,198,309,753,651
1 Result Published "K"."GET_VAL" PLSQL 2017*JUL*12 07:06:43 9,198,309,753,651
3 Result Published "K"."GET_VAL" PLSQL 2017*JUL*12 07:06:43 9,198,309,753,651
4 Result Published "K"."GET_VAL" PLSQL 2017*JUL*12 07:06:43 9,198,309,753,651
update rc_tab set val = -2 where id = 2;
exec push_commitscn;
commit;
select get_commitscn from dual;
GET_COMMITSCN
------------------
9,198,309,753,654
select id, type, status, name, namespace, creation_timestamp, scn
from v$result_cache_objects ro
order by scn desc, type, id;
ID TYPE STATUS NAME NAMESPACE CREATION_TIMESTAMP SCN
-- ---------- --------- ------------- ---------- -------------------- --------------------
2 Dependency Published K.RC_TAB 2017*JUL*12 07:06:43 9,198,309,753,654
0 Dependency Published K.GET_VAL 2017*JUL*12 07:06:43 9,198,309,753,651
1 Result Invalid "K"."GET_VAL" PLSQL 2017*JUL*12 07:06:43 9,198,309,753,651
3 Result Invalid "K"."GET_VAL" PLSQL 2017*JUL*12 07:06:43 9,198,309,753,651
4 Result Invalid "K"."GET_VAL" PLSQL 2017*JUL*12 07:06:43 9,198,309,753,651
9,198,309,753,654
is exposed in V$RESULT_CACHE_OBJECTS.SCN as invalidation SCN.
Oracle 12c Database Reference said about V$RESULT_CACHE_OBJECTS.SCN:
Build SCN (TYPE = Result) or invalidation SCN (TYPE = Dependency)
There are also a few Oracle Tables/Views containing "commitscn" or "commit_scn" columns, which could point to its usage.
