25 April, 2021

OJVM : Loading Java Code and Running it from a Database Session

 Oracle's JVM in the database, available since 8i and also known as "OJVM" allows you (Developer or DBA) to load Java code into the database and execute it from a database session.  Note that any OS calls that it makes will be from the server process, not the client process. Thus, if the code tries to read or write to a filesystem or to connect to an external website, the call will be executed by the database server process, not your client (sqlplus or any other program).


Here is a simple example :



SQL> -- grant Java Code permission to only READ only /home/oracle/tmp
SQL> -- this should have been provided by the DBA
SQL>
SQL> begin
  2     dbms_java.grant_permission('HEMANT',
  3                                      'SYS:java.io.FilePermission',
  4                                      '/home/oracle/tmp',  'read');
  5
  6     dbms_java.grant_permission( 'HEMANT',
  7                                      'SYS:java.io.FilePermission',
  8                                       '/home/oracle/tmp/*',
  9                                       'read' );
 10
 11     dbms_java.grant_permission( 'HEMANT',
 12                                      'SYS:java.lang.RuntimePermission',
 13                                       'getFileSystemAttributes',
 14                                       '' );
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL>
SQL> -- here is where I load the Java code into my schema
SQL> connect hemant/hemant@orclpdb1
Connected.
SQL>
SQL> -- load the java code into the database
SQL> create or replace and compile java source named "readOSDirectory"
  2      as
  3  /* using  java.io */
  4      import java.io.*;
  5
  6  /* create the main class */
  7      public class readOSDirectory
  8      {
  9  /* create the class to be executed from a procedure */
 10       public static void getList(String directory)
 11       {
 12  /* use File class from java.io */
 13         File directoryPath = new File( directory );
 14         File filesList[] = directoryPath.listFiles();
 15
 16  /* read till end of list */
 17         for (File file : filesList)
 18           {
 19             System.out.println("File Name: "+file.getName()
 20                                + "  File Size:  "+file.length());
 21           }
 22        }
 23       }
 24  /

Java created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> /* create a PL/SQL procedure to call the getList class */
SQL> create or replace
  2     procedure Read_Directory( p_directory in varchar2 )
  3     as language java
  4     name 'readOSDirectory.getList( java.lang.String )';
  5  /

Procedure created.

SQL>
SQL> /* enable output to screen-- serveroutput is for my sqlplus session */
SQL> SET SERVEROUTPUT ON SIZE 100000
SQL> CALL dbms_java.set_output (100000);

Call completed.

SQL>
SQL> begin
  2  Read_Directory('/home/oracle/tmp');
  3  end;
  4  /
File Name: File_1.txt  File Size:  13
File Name: File_2.txt  File Size:  25
File Name: Run_Sequence_Loop.sql  File Size:  1047
File Name: List_LongOps.sql  File Size:  303
File Name: create_db_HEMANT.sql  File Size:  1530
File Name: create_db_HEMANT.lst  File Size:  47

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2  Read_Directory('/home/oracle');
  3  end;
  4  /
Exception in thread "Root Thread" java.security.AccessControlException: the Permission ("java.io.FilePermission" "/home/oracle"
"read") has not been granted to HEMANT. The PL/SQL to grant this is dbms_java.grant_permission( 'HEMANT',
'SYS:java.io.FilePermission', '/home/oracle', 'read' )
        at java.security.AccessControlContext.checkPermission(AccessControlContext.java)
        at java.security.AccessController.checkPermission(AccessController.java:926)
        at java.lang.SecurityManager.checkPermission(SecurityManager.java:551)
        at oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java:210)
        at java.lang.SecurityManager.checkRead(SecurityManager.java:890)
        at java.io.File.list(File.java:1117)
        at java.io.File.listFiles(File.java:1207)
        at readOSDirectory.getList(readOSDirectory:11)
begin
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission
("java.io.FilePermission" "/home/oracle" "read") has not been granted to HEMANT. The PL/SQL to grant this is
dbms_java.grant_permission( 'HEMANT', 'SYS:java.io.FilePermission', '/home/oracle', 'read' )
ORA-06512: at "HEMANT.READ_DIRECTORY", line 1
ORA-06512: at line 2


SQL>
SQL> !ls -l /home/oracle/tmp
total 24
-rw-r--r--. 1 oracle oinstall   47 Apr 25 18:46 create_db_HEMANT.lst
-rw-r--r--. 1 oracle oinstall 1530 Apr 25 18:46 create_db_HEMANT.sql
-rw-r--r--. 1 oracle oinstall   13 Apr 25 18:07 File_1.txt
-rw-r--r--. 1 oracle oinstall   25 Apr 25 18:34 File_2.txt
-rw-r--r--. 1 oracle oinstall  303 Apr 25 18:46 List_LongOps.sql
-rw-r--r--. 1 oracle oinstall 1047 Apr 25 18:46 Run_Sequence_Loop.sql

SQL>


(Note : The Java call reads from the OS's directory object "as is" returned by the directory.  The "ls -l" command sorts the output, so you may see a difference in the sorted output)

Since OJVM runs in a database server process, strong permission controls are enforced.  So, the first set of calls show permissions being granted by the DBA to the "HEMANT" schema to only read from /home/oracle/tmp

Then, the user (HEMANT) connects and loads his Java code into the database.  (Note : The first time you run this, it may take some time to load the java classes).  Then he creates a PL/SQL procedure as a "wrapper" that can execute the Java code.

Finally, he executes the java code to read the target directory /home/oracle/tmp

I also demonstrate how his attempt to read /home/oracle fails as he has not been granted explicit permission on this directory

21 April, 2021

Primary and Standby in the same RMAN Catalog

 A quick demo of an RMAN Catalog shared by both Primary and Standby databases

For this demo, the RMAN Catalog is in the "rmanschema" account in the "rmancat" database, accessible from both Primary and Standby

At the Primary :



oracle19c>echo $ORACLE_SID
ORCLCDB
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:22:02 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCLCDB   READ WRITE

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      ORCLCDB
SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:22:54 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCLCDB (DBID=2778483057)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> quit


Recovery Manager complete.
oracle19c>


Now, at the Standby



oracle19c>echo $ORACLE_SID
STDBYDB
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:25:13 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCLCDB   MOUNTED

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      STDBYDB
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:26:03 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCLCDB (DBID=2778483057, not open)
connected to recovery catalog database

RMAN> register database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of register command at 04/21/2021 22:26:19
RMAN-01005: Mounted control file type must be CURRENT to register the database

RMAN> quit


Recovery Manager complete.
oracle19c>


(Note that the Standby has the same NAME and DBID, but a distinct DB_UNIQUE_NAME.)

It is NOT necessary to register the Standby as long as the DB_UNIQUE_NAME is different and is not currently "known to the Catalog" (i.e. is not already registered for some other database).

So, how do I run RMAN Backups and have them saved in the Catalog ?

On the Primary, I run a FULL Backup.


oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:31:08 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCLCDB (DBID=2778483057)
connected to recovery catalog database

RMAN> backup as compressed backupset database;

Starting backup at 21-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=280 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/ORCLCDB/system01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 21-APR-21
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_2: starting piece 1 at 21-APR-21
channel ORA_DISK_2: finished piece 1 at 21-APR-21
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7v9_.bkp tag=TAG20210421T223134 comment=NONE

...
...
...
Starting Control File and SPFILE Autobackup at 21-APR-21
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2021_04_21/o1_mf_s_1070490815_j80fy0hv_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-APR-21

RMAN> quit


Recovery Manager complete.
oracle19c>


Then, I run a separate backup at the Standby :


oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:36:37 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCLCDB (DBID=2778483057, not open)
connected to recovery catalog database

RMAN> backup as compressed backupset database;

Starting backup at 21-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=253 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=249 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/STDBYDB/system01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/STDBYDB/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 21-APR-21
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/opt/oracle/oradata/STDBYDB/sysaux01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/STDBYDB/users01.dbf
channel ORA_DISK_2: starting piece 1 at 21-APR-21
channel ORA_DISK_2: finished piece 1 at 21-APR-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4j55_.bkp tag=TAG20210421T223703 comment=NONE

...
...
...
Starting Control File and SPFILE Autobackup at 21-APR-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_04_21/o1_mf_s_1070490998_j80g8k1q_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-APR-21

RMAN> quit


Recovery Manager complete.
oracle19c>


So, I now have two different database backups of the same NAME and DBID registered in one catalog.

How do I distinguish them ?  From the RMAN command lime query  I can verify in this manner (querying only for datafile 1 for the purpose of this demo)

At the Primary :
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:43:42 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCLCDB (DBID=2778483057)
connected to recovery catalog database

RMAN> list backup of datafile 1 completed after "sysdate-1";


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1245    Full    231.82M    DISK        00:00:38     21-APR-21
        BP Key: 1255   Status: AVAILABLE  Compressed: YES  Tag: TAG20210421T223134
        Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7pb_.bkp
  List of Datafiles in backup set 1245
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 11072202   21-APR-21              NO    /opt/oracle/oradata/ORCLCDB/system01.dbf

RMAN> quit


Recovery Manager complete.
oracle19c>


And at the Standby :

oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:45:03 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCLCDB (DBID=2778483057, not open)
connected to recovery catalog database

RMAN> list backup of datafile 1 completed after "sysdate-1";


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1300    Full    241.08M    DISK        00:00:40     21-APR-21
        BP Key: 1308   Status: AVAILABLE  Compressed: YES  Tag: TAG20210421T223703
        Piece Name: /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp
  List of Datafiles in backup set 1300
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 11061162   21-APR-21 11072277     NO    /opt/oracle/oradata/STDBYDB/system01.dbf

RMAN> quit


Recovery Manager complete.
oracle19c>


RMAN doesn't show the database hostname (and a RAC database could be on multiple hosts, with rman backup channels running from multiple hosts concurrently, backups could be written to tape instead of disk).

But the listing does show that 

the Primary database datafile /opt/oracle/oradata/ORCLCDB/system01.dbf  is in the backup- piece /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7pb_.bkp 

while  the Standby database datafile /opt/oracle/oradata/STDBYDB/system01.dbf is in the backup-piece /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp.

You might also notice that the Checkpoint SCNs are different.



Can I run an SQL query in the RMAN Catalog schema itself ?

oracle19c>sqlplus rmanschema/rmanschema@rmancat

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:49:02 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Wed Apr 21 2021 22:45:04 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select db_key, dbid, name from rc_database;

    DB_KEY       DBID NAME
---------- ---------- --------
         1 2778483057 ORCLCDB

SQL>
SQL> col db_unique_name format a16
SQL> select site_key, db_key, database_role, db_unique_name from rc_site;

  SITE_KEY     DB_KEY DATABAS DB_UNIQUE_NAME
---------- ---------- ------- ----------------
         3          1 PRIMARY ORCLCDB
       804          1 STANDBY STDBYDB

SQL>
SQL> l
  1  select site.site_key, site.database_role, site.db_unique_name,
  2  bs.bs_key, bs.backup_type
  3  from rc_site site, rc_backup_set bs
  4  where site.site_key=bs.site_key
  5  and site.db_key=bs.db_key
  6  and bs.start_time > sysdate-1
  7* and bs.bs_key in (1245,1300)
SQL> /

  SITE_KEY DATABAS DB_UNIQUE_NAME       BS_KEY B
---------- ------- ---------------- ---------- -
         3 PRIMARY ORCLCDB                1245 D
       804 STANDBY STDBYDB                1300 D

SQL>
SQL> l
  1  select site.site_key, site.database_role, site.db_unique_name,
  2  df.bs_key, df.file#, df.checkpoint_change#
  3  from rc_site site, rc_backup_set bs, rc_backup_datafile df
  4  where site.site_key=bs.site_key
  5  and site.db_key=bs.db_key
  6  and site.db_key=df.db_key
  7  and bs.bs_key=df.bs_key
  8  and bs.start_time > sysdate-1
  9  and bs.bs_key in (1245,1300)
 10* and df.file#=1
SQL> /

  SITE_KEY DATABAS DB_UNIQUE_NAME       BS_KEY      FILE# CHECKPOINT_CHANGE#
---------- ------- ---------------- ---------- ---------- ------------------
         3 PRIMARY ORCLCDB                1245          1           11072202
       804 STANDBY STDBYDB                1300          1           11061162


Here we can set that RC_DATABASE has only 1 record for the actual database.  But we can distinguish the Primary and Standby from RC_SITE. SITE_KEY is 3 is for the Primary and 804 is for the Standby.

BS_KEY is the "BS Key" in the "LIST BACKUP" command output from RMAN.  Today's backup of Datafile#1 at the Primary was in Backup Set  1245 while that of the same datafile at the Standby was in Backup Set 1300.  (Yes, the Primary and Standby can have different Backup Set numbers and the Standby may well have higher Backup Set numbers if I have run more frequent Backups at the Standby !)
You can also compare the CHECKPOINT_CHANGE# as the "Ckp SCN" in the LIST BACKUP output. The SYSTEM datafile#1 was at SCN 11072202 in the Primary Backup and SCN 11061162 in the Standby Backup.

There are many more RC_%  Tables and Views in the RMAN Schema that you can query.


11 April, 2021

Python with Oracle using sqlalchemy and cx_oracle

 Here is a simple demo of using Python and the sqlalchemy and cx_oracle libraries


This is the code :

#import required libraries
import pandas as pd
import cx_Oracle
import sqlalchemy
from sqlalchemy.exc import SQLAlchemyError

#setup connection
try:
    oracle_pdb = sqlalchemy.create_engine("oracle+cx_oracle://hemant:hemant@localhost/?service_name=orclpdb1", arraysize=100)
except SQLAlchemyError as e:
   print(e)
   
#setup query and pandas dataframe for results
try:
    employees_query = """SELECT * FROM hr.employees order by employee_id""";   
    df_employees = pd.read_sql(employees_query, oracle_pdb)
except SQLAlchemyError as e:
   print(e)

#Info on the dataframe
print(df_employees.info())
#the first five rows
print(df_employees.head())

#create a new dataframe with a subset of columns
df_emp_selected_cols=df_employees
df_emp_selected_cols.drop(['email','phone_number','salary','commission_pct','manager_id','department_id'],axis=1, inplace=True)
print(df_emp_selected_cols.head())


And here is the output from my database :

Info on the dataframe

RangeIndex: 108 entries, 0 to 107
Data columns (total 11 columns):
 #   Column          Non-Null Count  Dtype         
---  ------          --------------  -----         
 0   employee_id     108 non-null    int64         
 1   first_name      108 non-null    object        
 2   last_name       108 non-null    object        
 3   email           108 non-null    object        
 4   phone_number    107 non-null    object        
 5   hire_date       108 non-null    datetime64[ns]
 6   job_id          108 non-null    object        
 7   salary          107 non-null    float64       
 8   commission_pct  35 non-null     float64       
 9   manager_id      106 non-null    float64       
 10  department_id   107 non-null    float64       
dtypes: datetime64[ns](1), float64(4), int64(1), object(5)
memory usage: 9.4+ KB
None
The first 5 rows
   employee_id first_name last_name  ... commission_pct manager_id department_id
0          100     Steven      King  ...            NaN        NaN          90.0
1          101      Neena   Kochhar  ...            NaN      100.0          90.0
2          102        Lex   De Haan  ...            NaN      100.0          90.0
3          103  Alexander    Hunold  ...            NaN      102.0          60.0
4          104      Bruce     Ernst  ...            NaN      103.0          60.0

[5 rows x 11 columns]
With selected columns only
   employee_id first_name last_name  hire_date   job_id
0          100     Steven      King 2003-06-17  AD_PRES
1          101      Neena   Kochhar 2005-09-21    AD_VP
2          102        Lex   De Haan 2001-01-13    AD_VP
3          103  Alexander    Hunold 2006-01-03  IT_PROG
4          104      Bruce     Ernst 2007-05-21  IT_PROG


Once you are familiar with this method, you can use numpy, matplotlib and a host of other python libraries with the dataset.

This article by Yuli Vasiliev is a good starter.


03 April, 2021

Using the SESSION_LONGOPS view with DBMS_APPLICATION_INFO

 I have, in the past, demonstrated DBMS_APPLICATION_INFO   here    and   here.

Also, I have demonstrated how V$SESSION_LONGOPS can be used (and misunderstood !!) 


Here is a demo of how you can use both features to enable monitoring of a (batch ?) job run.

The job is modified to include calls to DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS.

The DBA can then monitor the job from V$SESSION_LONGOPS.


The job is to update a PRODUCTS table with new PRODUCT_IDs, after some validation.  Since, this can take some time to run, the DBA wants to monitor it via V$SESSION_LONGOPS.


Here is the PL/SQL code for the batch (job) run :(look up the documentaion on DBMS_APPLICATION_INFO in the PL/SQL Developers Guide for our version -- I know that is available since at least 10.2, although this demonstation below is in 19c)



DECLARE
rindex    BINARY_INTEGER;
slno      BINARY_INTEGER;
target_count  number;
updated_count     number;

product_row  products%rowtype;
check_flag varchar2(8);

BEGIN
rindex := dbms_application_info.set_session_longops_nohint;
updated_count := 0;
select num_rows into target_count   --- this is an approximation based on last updated statistics on the table
from user_tables
where table_name = 'PRODUCTS';

-- begin the updates here
for product_row in (select product_id, product_name from products)
loop

 -- validate_for_update is a function that checks if this product should get a new PRODUCT_ID
 select validate_for_update(product_row.product_id, product_row.product_name) into check_flag from dual ;

 if check_flag='OK' then
   update products set product_id = product_id+10000 where product_id=product_row.product_id;
   insert into update_run_log values (product_row.product_id,systimestamp);
   updated_count := updated_count+1;
   dbms_application_info.set_session_longops(rindex,
    slno,
    op_name=>'New_Product_IDs',
    target=>0,   -- default, not used by me
    context=>0,   -- default, not used by me
    sofar=>updated_count,
    totalwork=>target_count,
    target_desc=>'Table : PRODUCTS',
    units=>'rows');
 else
   null;
 end if;

end loop;
END;
/


commit;



And here is the DBA monitoring the job as it is running :



23:22:15 SQL> l
  1  select sid, opname, target_desc, sofar, totalwork, units, start_time, elapsed_seconds, time_remaining
  2  from v$session_longops
  3* where username = 'HEMANT'
23:22:15 SQL> /

no rows selected

23:22:16 SQL>
23:22:21 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS          3        500 rows   03-APR-21 23:22:18               2            331

23:22:22 SQL>
23:22:30 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS         12        500 rows   03-APR-21 23:22:18              12            488

23:22:31 SQL>
23:22:44 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS         26        500 rows   03-APR-21 23:22:18              26            474

23:22:45 SQL>
23:23:26 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS         67        500 rows   03-APR-21 23:22:18              69            446

23:23:27 SQL>
23:26:28 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS        245        500 rows   03-APR-21 23:22:18             251            261

23:26:30 SQL>
23:28:31 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS        364        500 rows   03-APR-21 23:22:18             373            139

23:28:32 SQL>
23:29:24 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS        416        500 rows   03-APR-21 23:22:18             426             86

23:29:25 SQL>
23:30:16 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS        467        500 rows   03-APR-21 23:22:18             478             34

23:30:17 SQL>
23:30:43 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS        493        500 rows   03-APR-21 23:22:18             505              7

23:30:44 SQL>
23:30:48 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS        497        500 rows   03-APR-21 23:22:18             509              3

23:30:49 SQL>
23:30:50 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS        500        500 rows   03-APR-21 23:22:18             512              0

23:30:51 SQL>
23:31:04 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS        500        500 rows   03-APR-21 23:22:18             512              0

23:31:05 SQL>


(In this demo, all 500 rows actually do pass the "VALIDATE_FOR_UPDATE" check) 
 Normally, we expect V$SESSION_LONGOPS to be populated by Long Running Operations (I specify Operations because of the "misinterpretation" that I demonstrate here).  Operations that are Full Table Scans  or Parallel Execution or RMAN Operations --- all of these are cases where the view is populated "automatically" by Oracle.

However, as I have demonstrated above, you can use your own code to populate this view so that it can be used to monitor Long Running "Operations" that you have defined (or, rather, worked with the Developers to define if you are the DBA).