I have created a short video demonstrating a copy of selected objects from a 19c database accessible from my desktop to an Autonomous (ATP) Database in the Oracle Cloud using SQL Developer 20.2
27 August, 2020
18 August, 2020
ORA_ROWSCN and RowDependencies
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.
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).
< 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.
11 August, 2020
SCN_TO_TIMESTAMP
A quick demo of SCN_TO_TIMESTAMP in 19c
If you query for an older SCN, you would get an ORA-08181 error. What is an "older SCN" ?
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).
10 August, 2020
Real Time SQL Monitor using SQL Developer 20.2
Here are a few screenshots of using the Real Time SQL Monitor in SQL Developer 20.2 against a 19c database. I use the MONITOR hint explicitly in the SQL statements to force them to be visible in the SQL Monitor.
UPDATE 24-Oct-20 : A Video Demo of this feature is on my Youtube channel
Note : The "B" after the "18" and "20" for I/O requests in the first two screenshots is *not* "Billion"
This is an INSERT statement
This shows the Execution Plan of the INSERT statement
Here is more complicated query with Parallel Execution (all 3 panes : Plan Statistics, Plan and Parallel Execution)
Subscribe to:
Posts (Atom)