The last three posts in this blog have been on SCNs and the ORA_ROWSCN function with brief mentions about the RowDependencies extended attribute of a table defnition.
Here is a quick demonstration of how RowDependencies works.
I setup two tables, without and with RowDepedencies, with 5 rows each (ensuring that all 5 fit into 1 datablock) and read the SCN values returned by ORA_ROWSCN
This is the first table where I don't specify RowDependencies :
22:39:35 SQL> create table norowdep (id number, inserted_scn number, inserted_timestamp timestamp);
Table created.
22:40:09 SQL> insert into nororwdep
22:40:21 2 select 1, current_scn, systimestamp from v$database;
insert into nororwdep
*
ERROR at line 1:
ORA-00942: table or view does not exist
22:40:37 SQL> insert into norowdep
22:40:45 2 select 1, current_scn, systimestamp from v$database;
1 row created.
22:40:52 SQL> commit;
Commit complete.
22:40:54 SQL> !sleep 5
22:41:02 SQL>
22:41:09 SQL> insert into norowdep
22:41:10 2 select 2, current_scn, systimestamp from v$database;
1 row created.
22:41:19 SQL> commit;
Commit complete.
22:41:22 SQL> !sleep 5
22:41:30 SQL> insert into norowdep
22:41:34 2 select 3, current_scn, systimestamp from v$database;
1 row created.
22:41:42 SQL> commit;
Commit complete.
22:41:47 SQL> !sleep 5
22:41:54 SQL> insert into norowdep
22:41:56 2 select 4, current_scn, systimestamp from v$database;
1 row created.
22:42:05 SQL> commit;
Commit complete.
22:42:10 SQL> !sleep 5
22:42:18 SQL> insert into norowdep
22:42:19 2 select 5, current_scn, systimestamp from v$database;
1 row created.
22:42:29 SQL> commit;
Commit complete.
22:42:31 SQL> !sleep 5
22:42:41 SQL> select id, inserted_scn, inserted_timestamp, ora_rowscn
22:43:09 2 from norowdep
22:43:16 3 order by 1
22:43:19 4 /
ID INSERTED_SCN INSERTED_TIMESTAMP ORA_ROWSCN
---------- ------------ --------------------------------------------------------------------------- ----------
1 6780419 18-AUG-20 10.40.52.802908 PM 6785773
2 6782540 18-AUG-20 10.41.19.887569 PM 6785773
3 6783619 18-AUG-20 10.41.42.647586 PM 6785773
4 6784694 18-AUG-20 10.42.05.374764 PM 6785773
5 6785769 18-AUG-20 10.42.29.422846 PM 6785773
22:43:20 SQL>
Here Oracle returns the same SCN value as ORA_ROWSCN for all 5 rows -- it has read this from the Block, instead of reading for each row.
This is the second table with RowDependencies :
22:47:31 SQL> create table rowdependencies (id number, inserted_scn number, inserted_timestamp timestamp) rowdependencies;
Table created.
22:47:56 SQL>
22:48:07 SQL> insert into rowdependencies
22:48:13 2 select 1, current_scn, systimestamp from v$database;
1 row created.
22:48:17 SQL> commit;
Commit complete.
22:48:19 SQL> !sleep 5
insert into rowdependencies
22:48:27 SQL>
22:48:30 2
22:48:31 SQL> insert into rowdependencies
22:48:33 2 select 2, current_scn, systimestamp from v$database;
1 row created.
22:48:42 SQL> commit;
Commit complete.
22:48:43 SQL> !sleep 5
22:48:51 SQL> insert into rowdependencies
22:48:53 2 select 3,, current_scn, systimestamp from v$database;
select 3,, current_scn, systimestamp from v$database
*
ERROR at line 2:
ORA-00936: missing expression
22:49:00 SQL> insert into rowdependencies
22:49:06 2 select 3, current_scn, systimestamp from v$database;
1 row created.
22:49:12 SQL> commit;
Commit complete.
22:49:14 SQL> !sleep 5
22:49:21 SQL> insert into rowdependencies
22:49:22 2 select 4, current_scn, systimestamp from v$database;
1 row created.
22:49:35 SQL> commit;
Commit complete.
22:49:37 SQL> insert into rowdependencies
22:49:47 2 select 5, current_scn, systimestamp from v$database;
1 row created.
22:49:56 SQL> commit;
Commit complete.
22:49:57 SQL> !sleep 5
22:50:06 SQL> select id, inserted_scn, inserted_timestamp, ora_rowscn
22:50:17 2 from rowdependencies
22:50:23 3 order by id
22:50:26 4 /
ID INSERTED_SCN INSERTED_TIMESTAMP ORA_ROWSCN
---------- ------------ --------------------------------------------------------------------------- ----------
1 6804210 18-AUG-20 10.48.17.485841 PM 6804228
2 6805303 18-AUG-20 10.48.42.081454 PM 6805308
3 6806387 18-AUG-20 10.49.12.242874 PM 6806394
4 6807475 18-AUG-20 10.49.35.500547 PM 6807478
5 6808541 18-AUG-20 10.49.56.098645 PM 6808546
22:50:26 SQL>
For this table, eachrow has a different ORA_ROWSCN, although it is not the actual database SCN at the time of the INSERT. (I have already explained the reason for this in my previous posts here and here).
ORA_ROWSCN does *not* return the actual SCN of the database as of the time of the INSERT or UPDATE DML but only as of the time of the COMMIT. However, if you don't specify RowDependencies at the table level, it will return the block level SCN.
Note further : 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