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



19 March, 2021

Patching -- opatch and datapatch in Oracle vs a single executable in SQL Server

 In the Oracle universe, when applying a Release Update patch, the DBA has to run "opatch" to patch the binaries and library files but also has to run a separate "datapatch" to update the data dictionary with SQL "Apply" Actions in each database.

In what seems to be a contrast, SQL Server patching requires only execution of the Patch exe file.  Does that mean that no SQL "Apply" Actions are required ?  See how it is done in SQL Server in my other blog post.