PL/SQL

How to Use Bind Variable in Oracle

Posted on
Shareable Cursor Using bind variables can make statements with minor differences become literally the same so as to share the same cursor when executions. That means, SQL engine doesn’t have to parse those similar statements every time they are executed, it reuses the shared cursor to service millions of executions. Unshareable Cursors In contrast, dynamic […]
PL/SQL

External Procedures May Consume a Lot of Memory

Posted on
External procedures are very special procedures stored outside of the database, and it can be coded by C or Java language. Since the external libraries were executed on OS-level, their behaviors are not managed and bounded by database anymore, so it could become a security leak. The users who have been granted the privilege to […]
PL/SQL

How to Identify Blocking Sessions

Posted on
Here I introduce a sample of PL/SQL code to monitor the blocking sessions every N minutes and output in a formatted report.First of all, edit the sql script.[oracle@primary01 ~]$ vi CheckBlockingSessions.sqlSET SERVEROUTPUT ON;ALTER SESSION SET NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’;DECLARE  V_MAX_COUNT NUMBER := 4;  V_WAIT_MIN  NUMBER := 5;  CURSOR C_BLOCKING  IS    SELECT BLOCKER.INST_ID,      BLOCKER.SID BLOCKER_SID […]