27 August, 2020

Using SQL Developer to Copy Data to the Oracle Cloud

 

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






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).

Let's see what ORA_ROWSCN shows :
< 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

 
 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)