DEV Community: Pranav Bakare

[Boost]

Pranav Bakare — Sun, 15 Mar 2026 03:06:01 +0000

UWABOR KING COLLINS

Sep 30 '24

The Ultimate Terraform Tutorial: From Beginner to Advanced (2025 Guide)

#devops #cloudcomputing #terraform #tutorial

20 min read

Finding All Child Tables Referencing a Parent Table in Oracle

Pranav Bakare — Thu, 25 Sep 2025 11:36:00 +0000

Child Tables Referencing a Parent Table in Oracle

In Oracle databases, understanding foreign key relationships is essential before altering or dropping a parent table. Using the views ALL_CONSTRAINTS and ALL_CONS_COLUMNS, you can identify which child tables reference a specific parent table and the exact columns involved.
A simple SQL query filtering constraint_type = 'R' (foreign keys) provides this mapping clearly. The output shows child tables, child columns, parent tables, and their constraints. This quick analysis helps avoid errors and ensures safe schema changes.

When working with relational databases, it’s often necessary to understand how tables are related through foreign keys. For example, if you want to drop or modify a parent table, you’ll need to know which child tables reference it.
In Oracle, you can discover these relationships using the data dictionary views ALL_CONSTRAINTS and ALL_CONS_COLUMNS

SELECT
    a.table_name      AS child_table,
    a.constraint_name AS child_constraint,
    a.column_name     AS child_column,
    c_pk.table_name   AS parent_table,
    c_pk.constraint_name AS parent_constraint,
    c_pk.column_name  AS parent_column
FROM all_cons_columns a
JOIN all_constraints c 
     ON a.owner = c.owner
    AND a.constraint_name = c.constraint_name
JOIN all_cons_columns c_pk 
     ON c.r_owner = c_pk.owner
    AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'  -- 'R' = Foreign Key
  AND c_pk.table_name = UPPER('MST_IRR');

Automating Partition Cleanup in Oracle with PL/SQL and DBMS Scheduler

Pranav Bakare — Sat, 20 Sep 2025 06:39:57 +0000

Automating Partition Cleanup in Oracle with PL/SQL and DBMS Scheduler

The procedure begins by reading from the configuration table CMN_DROP_PARTITION, which defines which partitioned tables need cleanup, how many partitions should be retained, and which partitions must be excluded.
For each active table entry, it retrieves the table owner, table name, and retention count. Next, it queries the Oracle data dictionary views (dba_tab_partitions) to fetch all available partitions for that table in ascending order by position. Once the partitions are listed, it checks the configured exclusion partition so that it is never dropped, even if it falls outside the retention period.
It then calculates how many partitions must be kept, ensuring the most recent ones (based on partition position) remain untouched. The procedure identifies the older partitions that are beyond the retention limit and marks them as candidates for dropping. Using dynamic SQL, it generates and executes ALTER TABLE DROP PARTITION statements for each of these outdated partitions.
Error handling is usually included to skip over issues like locked partitions or dependencies. Finally, this process ensures partition cleanup is automated, data-driven, and consistent, improving database performance and storage efficiency without manual intervention.

The solution automates partition maintenance in Oracle by combining a PL/SQL procedure, a configuration table, and a scheduled job. The procedure CMN_DROP_PARTITIONS_PR dynamically identifies and drops partitions older than the retention rules defined in CMN_DROP_PARTITION.
The configuration table controls how many partitions to keep, which partitions to exclude, and which tables are active for cleanup, making the logic fully data-driven. A DBMS Scheduler job then executes the procedure every Monday at 3 AM, ensuring cleanup happens consistently without manual effort.
Together, these components deliver a reliable, automated, and flexible way to keep partitioned tables lean and manageable.

Managing partitioned tables efficiently is crucial in large-scale databases, especially when dealing with high-volume transactional or logging data.
If older partitions are not purged regularly, the database grows uncontrollably, impacting performance, storage, and maintenance overhead.
To address this challenge, we implemented an automated partition cleanup mechanism in Oracle. The solution involves three components working together:
(1) a PL/SQL procedure that drops old partitions beyond a configurable retention period,
(2) a configuration table that defines retention rules and exceptions per table, and
(3) a DBMS Scheduler job that triggers the cleanup procedure at regular intervals without manual intervention. This approach ensures that database housekeeping is automated, safe, and consistent, reducing operational effort while keeping only the required data available.

1. The Cleanup Procedure —

At the core of this solution is the PL/SQL procedure CMN_DROP_PARTITIONS_PR.
It scans all partitioned tables configured for cleanup, checks how many partitions should be retained, and automatically issues ALTER TABLE DROP PARTITION commands for older partitions.
Any errors are logged but don’t interrupt the overall execution, ensuring that one bad partition doesn’t stop the cleanup process.

CREATE OR REPLACE PROCEDURE CMN_OWNR.CMN_DROP_PARTITIONS_PR IS
    v_Dropstr_Sql varchar2(4000);
BEGIN
    FOR i IN (
        SELECT * FROM (
            SELECT u.table_name,
                   partition_name,
                   partition_position,
                   RANK() OVER(PARTITION BY u.table_name ORDER BY u.partition_position DESC) rnk,
                   cdp.no_of_part_to_retn retn_prt_days
            FROM user_tab_partitions u, cmn_drop_partition cdp
            WHERE cdp.table_name = u.table_name
              AND cdp.exclude_partition != u.partition_name
              AND cdp.active = 'Y')
        WHERE rnk > retn_prt_days
        ORDER BY rnk
    ) LOOP
        BEGIN
            v_Dropstr_Sql := 'ALTER TABLE ' || i.table_name || ' DROP PARTITION ' || i.partition_name || ' UPDATE INDEXES';
            EXECUTE IMMEDIATE v_Dropstr_Sql;
            v_Dropstr_Sql := '';
        EXCEPTION WHEN OTHERS THEN
            ngcs_util.db_err_log_pr('CMN','CMN_DROP_PARTITIONS_PR',i.table_name||':'||i.partition_name,NULL,SQLERRM,NULL,NULL);
        END;
    END LOOP;

ngcs_util.db_err_log_pr('CMN','CMN_DROP_PARTITIONS_PR','Drop Partition',NULL,'CMN PARTITIONS DROP JOB GOT COMPLETED SUCCESSFULLY AT:'||systimestamp,NULL,NULL);
EXCEPTION WHEN OTHERS THEN
    ngcs_util.db_err_log_pr('CMN','CMN_DROP_PARTITIONS_PR','Main EXCP',NULL,SQLERRM,NULL,NULL);
END CMN_DROP_PARTITIONS_PR;
/

2. Configuration Table —

The cleanup rules are defined in the CMN_DROP_PARTITION table. For each table, we can specify how many partitions to retain, which partitions to exclude from deletion, and whether the rule is active.
This makes the procedure flexible and data-driven.

CREATE TABLE CMN_DROP_PARTITION (
  table_name         VARCHAR2(100),
  exclude_partition  VARCHAR2(100),
  table_owner        VARCHAR2(15),
  no_of_part_to_retn NUMBER(5),
  active             VARCHAR2(1),
  created_date       TIMESTAMP(6),
  created_by         VARCHAR2(30),
  modified_date      TIMESTAMP(6),
  modified_by        VARCHAR2(30)
);


-- Sample entries
INSERT INTO CMN_DROP_PARTITION (table_name, exclude_partition, 
active, table_owner, no_of_part_to_retn, created_date, created_by)
VALUES('CMN_UPLD_DATA', 'P_PREDEC24','Y', 'CMN_OWNR',31, SYSDATE, 'ACCELYA-IT');
INSERT INTO CMN_DROP_PARTITION (table_name, exclude_partition, 
active, table_owner, no_of_part_to_retn, created_date, created_by)
VALUES('CMN_LOG_REQ_RESP', 'P_PRE24','Y', 'CMN_OWNR',7, SYSDATE, 'ACCELYA-IT');
COMMIT;

In this example:

For CMN_UPLD_DATA, the last 31 partitions are retained, but partition P_PREDEC24 is excluded from deletion.
For CMN_LOG_REQ_RESP, the last 7 partitions are retained, excluding P_PRE24.

3. Scheduling with DBMS Scheduler —

Finally, the procedure is scheduled using Oracle’s DBMS Scheduler.
We created a job that runs the cleanup process every Monday at 3 AM, ensuring weekly housekeeping without manual effort.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'CMN_DROP_PARTITION_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'cmn_drop_partitions_pr;',
    start_date      => systimestamp,
    repeat_interval => 'FREQ=WEEKLY; BYDAY=MON; BYHOUR=03; BYMINUTE=00; BYSECOND=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'JOB To drop older partition from CMN tables');
END;
/

This job guarantees the cleanup procedure runs at a fixed schedule, keeping the database lean and partitions under control.

Summary —

With this implementation, we achieved a robust and automated partition cleanup strategy. The procedure ensures that only the latest required partitions are kept, the configuration table provides flexibility and control without code changes, and the DBMS Scheduler job automates execution at off-peak hours. Together, these three components create a reliable maintenance framework that improves performance, reduces storage costs, and minimizes manual intervention in partition management.

Partitioning

Pranav Bakare — Fri, 19 Sep 2025 16:33:39 +0000

Pranav Bakare

Sep 18 '25

Automating Old Partition Cleanup in Oracle with PL/SQL

#automation #database #sql

3 min read

Automating Old Partition Cleanup in Oracle with PL/SQL

Pranav Bakare — Thu, 18 Sep 2025 12:03:30 +0000

Automating Old Partition Cleanup in Oracle with PL/SQL

This PL/SQL block is designed to automate partition maintenance for all tables owned by the schema CMN_OWNR. It loops through each table partition (skipping ones with INIT in their name), extracts the partition boundary date from the high_value column, and checks if it is on or before 1st June 2025.
If the condition is met, it tries to drop the partition along with updating indexes; however, if the partition cannot be dropped due to being the last one (ORA-14758), it instead truncates the partition to remove data while keeping structure intact.
This ensures that only old partitions are purged while preserving the latest required ones. In short, it’s a safe cleanup mechanism for managing historical data in partitioned tables.

DECLARE
  v_date DATE;
  v_sql  VARCHAR2(4000);
BEGIN
  FOR i IN (SELECT dp.table_name,
                   dp.partition_name,
                   dp.high_value,
                   dp.partition_position
              FROM dba_tab_partitions dp
             WHERE dp.table_owner = 'CMN_OWNR'
               and dp.partition_name not like '%INIT%'
            --AND table_name = 'CMN_LOG'
             ORDER BY dp.table_name, partition_position) LOOP
    v_date := TO_DATE(substr(i.high_value, 12, 10), 'RRRR-MM-DD');
    IF v_date <= to_date('01-JUN-2025', 'dd-mon-yyyy') THEN
      v_sql := 'ALTER TABLE CMN_OWNR.' || i.table_name ||
               ' DROP PARTITION ' || i.partition_name || ' UPDATE INDEXES';
      BEGIN
        EXECUTE IMMEDIATE v_sql;
      EXCEPTION
        WHEN OTHERS THEN
          IF SQLCODE = -14758 THEN
            v_sql := 'ALTER TABLE CMN_OWNR.' || i.table_name ||
                     ' TRUNCATE PARTITION ' || i.partition_name ||
                     ' UPDATE INDEXES';
            EXECUTE IMMEDIATE v_sql;
          ELSE
            dbms_output.put_line(SQLERRM);
            dbms_output.put_line(v_sql);
          END IF;
      END;
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;

🔎 Step-by-step explanation

1. Loop through all partitions

FOR i IN (
   SELECT dp.table_name,
          dp.partition_name,
          dp.high_value,
          dp.partition_position
   FROM dba_tab_partitions dp
   WHERE dp.table_owner = 'CMN_OWNR'
     AND dp.partition_name NOT LIKE '%INIT%'
   ORDER BY dp.table_name, partition_position
) LOOP

Finds all partitions of all tables owned by CMN_OWNR.
Skips partitions whose name contains INIT.
Loops through each partition in order.

2. Extract partition boundary date

v_date := TO_DATE(SUBSTR(i.high_value, 12, 10), 'RRRR-MM-DD');

dba_tab_partitions.high_value is a text expression like:
TO_DATE(' 2025-06-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', ...)
SUBSTR(...,12,10) picks 2025-06-01.
TO_DATE(...,'RRRR-MM-DD') converts that to an Oracle DATE → 01-JUN-2025.

3. Check if partition is old

IF v_date <= TO_DATE('01-JUN-2025','DD-MON-YYYY') THEN

If the partition’s upper boundary date is before or equal to 01-JUN-2025, it’s considered old → purge it.

4. Try to drop the partition

v_sql := 'ALTER TABLE CMN_OWNR.' || i.table_name ||
         ' DROP PARTITION ' || i.partition_name || 
' UPDATE INDEXES';
EXECUTE IMMEDIATE v_sql;

Builds dynamic SQL to drop the old partition.
UPDATE INDEXES ensures local/global indexes remain usable.

5. Handle errors

EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -14758 THEN
      v_sql := 'ALTER TABLE CMN_OWNR.' || i.table_name ||
               ' TRUNCATE PARTITION ' || i.partition_name ||
               ' UPDATE INDEXES';
      EXECUTE IMMEDIATE v_sql;
    ELSE
      dbms_output.put_line(SQLERRM);
      dbms_output.put_line(v_sql);
    END IF;

If DROP fails with ORA-14758 (cannot drop the last partition in a range), it falls back to:
ALTER TABLE ... TRUNCATE PARTITION ...
→ keeps the partition structure but deletes its data.
Any other error is logged with dbms_output.

6. Global error handling

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;

If the whole block fails for some reason, the error is printed.

Summary —

This PL/SQL script automatically manages old partitions in Oracle tables owned by CMN_OWNR. It checks each partition’s HIGH_VALUE date and, if it is on or before 01-JUN-2025, it attempts to drop the partition. If dropping is not allowed (SQL error -14758), it instead truncates the partition while updating indexes. This helps keep partitioned tables lean by cleaning up historical data without manual intervention.

Audit Trigger for Tracking User Updates and Deletions in table

Pranav Bakare — Thu, 18 Sep 2025 11:57:51 +0000

Audit Trigger for Tracking User Updates and Deletions in table

This trigger audits every update or delete on the GEN_MST_USR_LST table.
It records the old data into an audit table with action type (U or D) and logs transaction details for traceability.

Summary of working of Trigger

The trigger AUD_ULT_AR_UD is designed to maintain an audit trail whenever a row in the GEN_MST_USR_LST table is updated or deleted. It fires after each row change and excludes actions performed by the system user PRG_USER.
When triggered, it first calls the gen_pkg.get_user_context procedure to fetch the current session's user, role, city, and transaction details. If no transaction detail ID (v_dtl_id) is available, it generates one using a sequence (dtl_seq.NEXTVAL) and inserts a record into the GEN_DB_TRANSACTION_LOG table, capturing information about the user, role, host machine, and module.
For a delete operation, the trigger inserts the entire old row into GEN_MST_USR_LST_AUD, marking the action as 'D' (delete) along with the new transaction detail ID.
For an update operation, it similarly inserts the old row values into the audit table but marks the action as 'U' (update). This way, the trigger ensures that all previous states of the data are preserved for auditing, enabling traceability of both user activity and data modifications in the system.
Audit Trigger for Tracking User Updates and Deletions in GEN_MST_USR_LST

Implementation of Trigger

create or replace TRIGGER AUD_ULT_AR_UD
AFTER DELETE OR UPDATE ON  GEN_MST_USR_LST
FOR EACH ROW

DECLARE
 v_user_id            gen_db_transaction_log.username%TYPE DEFAULT 'TC01052';
 v_trns_id            gen_db_transaction_log.transaction_id%TYPE;
 v_city               gen_db_transaction_log.city%TYPE;
 v_role               gen_db_transaction_log.user_role%TYPE;
 v_transaction_name   gen_db_transaction_log.transaction_id%TYPE;
 v_dtl_id             gen_db_transaction_log.dtl_id%TYPE;
 v_sessionid          gen_db_transaction_log.transaction_id%TYPE;

BEGIN
IF USER <> 'PRG_USER' THEN
  gen_pkg.get_user_context ( v_user_id, v_role, v_city, v_transaction_name, v_trns_id, v_dtl_id, v_sessionid );

   IF v_dtl_id IS NULL THEN
      SELECT dtl_seq.NEXTVAL into v_dtl_id FROM DUAL;
      INSERT INTO gen_db_transaction_log ( dtl_id, username, user_role, apps_server, transaction_id, business_tans_id, created_date, created_by)
      VALUES ( v_dtl_id, USER, SYS_CONTEXT('USERENV', 'OS_USER'), SUBSTR(SYS_CONTEXT('USERENV', 'HOST'), 1, 50), SYS_CONTEXT('USERENV', 'MODULE'), 555, SYSDATE, SYS_CONTEXT('USERENV', 'OS_USER'));
   END IF;
   IF DELETING THEN
      INSERT INTO GEN_MST_USR_LST_AUD
      (ULT_ID, OWNR_CARR_CODE, USR_NAME, FIRST_NAME, LAST_NAME, USR_TYP, USR_DSGN, SALUTATION, DOB, MAX_SESNS, CRNT_SESNS, LANGUAGE, APLN_ADMIN_IND, SYS_ADMIN_IND, LAST_LOGIN_DATT, LAST_LOGOUT_DATT, GRACE_LOGINS_RMNG, ONLINE_IND, USR_PWD, USR_PWD_CODE, USR_PWD_CODE_VAL, REG_MODE, ACNT_ENABLED, PWD_EXPRY_DT, PWD_EXPRY_ALRT_DT, TRACE_ENABLED, STAFF_ID, DFLT_APLN_CLASSIFIER, MGR_ID, ADRS_ID, VALID_UNTIL, INACTIVE, DTL_ID, CREATED_DATE, CREATED_BY, MODIFIED_DATE, MODIFIED_BY, DESKTOP_ALOWD_IND, USR_ROLE, LOCKED_DATE, INVLD_ATMPTS, OTP, OTP_VALID_UNTIL, USR_ACT_KEY, USR_ACT_KEY_VLDTY_DAT, EMAIL_VERIFIED, USR_BCRPT_PWD, IDP_NUMBER, ACTION, NEW_DTL_ID )
      VALUES
      (:OLD.ULT_ID, :OLD.OWNR_CARR_CODE, :OLD.USR_NAME, :OLD.FIRST_NAME, :OLD.LAST_NAME, :OLD.USR_TYP, :OLD.USR_DSGN, :OLD.SALUTATION, :OLD.DOB, :OLD.MAX_SESNS, :OLD.CRNT_SESNS, :OLD.LANGUAGE, :OLD.APLN_ADMIN_IND, :OLD.SYS_ADMIN_IND, :OLD.LAST_LOGIN_DATT, :OLD.LAST_LOGOUT_DATT, :OLD.GRACE_LOGINS_RMNG, :OLD.ONLINE_IND, :OLD.USR_PWD, :OLD.USR_PWD_CODE, :OLD.USR_PWD_CODE_VAL, :OLD.REG_MODE, :OLD.ACNT_ENABLED, :OLD.PWD_EXPRY_DT, :OLD.PWD_EXPRY_ALRT_DT, :OLD.TRACE_ENABLED, :OLD.STAFF_ID, :OLD.DFLT_APLN_CLASSIFIER, :OLD.MGR_ID, :OLD.ADRS_ID, :OLD.VALID_UNTIL, :OLD.INACTIVE, :OLD.DTL_ID, :OLD.CREATED_DATE, :OLD.CREATED_BY, SYSDATE, v_user_id, :OLD.DESKTOP_ALOWD_IND, :OLD.USR_ROLE, :OLD.LOCKED_DATE, :OLD.INVLD_ATMPTS, :OLD.OTP, :OLD.OTP_VALID_UNTIL, :OLD.USR_ACT_KEY, :OLD.USR_ACT_KEY_VLDTY_DAT, :OLD.EMAIL_VERIFIED, :OLD.USR_BCRPT_PWD, :OLD.IDP_NUMBER, 'D', v_dtl_id
      );
   END IF;

   IF UPDATING THEN
      INSERT INTO GEN_MST_USR_LST_AUD
      (ULT_ID, OWNR_CARR_CODE, USR_NAME, FIRST_NAME, LAST_NAME, USR_TYP, USR_DSGN, SALUTATION, DOB, MAX_SESNS, CRNT_SESNS, LANGUAGE, APLN_ADMIN_IND, SYS_ADMIN_IND, LAST_LOGIN_DATT, LAST_LOGOUT_DATT, GRACE_LOGINS_RMNG, ONLINE_IND, USR_PWD, USR_PWD_CODE, USR_PWD_CODE_VAL, REG_MODE, ACNT_ENABLED, PWD_EXPRY_DT, PWD_EXPRY_ALRT_DT, TRACE_ENABLED, STAFF_ID, DFLT_APLN_CLASSIFIER, MGR_ID, ADRS_ID, VALID_UNTIL, INACTIVE, DTL_ID, CREATED_DATE, CREATED_BY, MODIFIED_DATE, MODIFIED_BY, DESKTOP_ALOWD_IND, USR_ROLE, LOCKED_DATE, INVLD_ATMPTS, OTP, OTP_VALID_UNTIL,  USR_ACT_KEY, USR_ACT_KEY_VLDTY_DAT,EMAIL_VERIFIED, USR_BCRPT_PWD, IDP_NUMBER, ACTION, NEW_DTL_ID )
      VALUES
      (:OLD.ULT_ID, :OLD.OWNR_CARR_CODE, :OLD.USR_NAME, :OLD.FIRST_NAME, :OLD.LAST_NAME, :OLD.USR_TYP, :OLD.USR_DSGN, :OLD.SALUTATION, :OLD.DOB, :OLD.MAX_SESNS, :OLD.CRNT_SESNS, :OLD.LANGUAGE, :OLD.APLN_ADMIN_IND, :OLD.SYS_ADMIN_IND, :OLD.LAST_LOGIN_DATT, :OLD.LAST_LOGOUT_DATT, :OLD.GRACE_LOGINS_RMNG, :OLD.ONLINE_IND, :OLD.USR_PWD, :OLD.USR_PWD_CODE, :OLD.USR_PWD_CODE_VAL, :OLD.REG_MODE, :OLD.ACNT_ENABLED, :OLD.PWD_EXPRY_DT, :OLD.PWD_EXPRY_ALRT_DT, :OLD.TRACE_ENABLED, :OLD.STAFF_ID, :OLD.DFLT_APLN_CLASSIFIER, :OLD.MGR_ID, :OLD.ADRS_ID, :OLD.VALID_UNTIL, :OLD.INACTIVE, :OLD.DTL_ID, :OLD.CREATED_DATE, :OLD.CREATED_BY, :OLD.MODIFIED_DATE, :OLD.MODIFIED_BY, :OLD.DESKTOP_ALOWD_IND, :OLD.USR_ROLE, :OLD.LOCKED_DATE, :OLD.INVLD_ATMPTS, :OLD.OTP, :OLD.OTP_VALID_UNTIL,  :OLD.USR_ACT_KEY, :OLD.USR_ACT_KEY_VLDTY_DAT,:OLD.EMAIL_VERIFIED, :OLD.USR_BCRPT_PWD, :OLD.IDP_NUMBER, 'U', v_dtl_id
      );
   END IF;
END IF;
END;
/

Here's a concise 8-line point-wise explanation:

Trigger fires AFTER DELETE or UPDATE on GEN_MST_USR_LST for each row.
Ignores actions by the system user PRG_USER.
Calls gen_pkg.get_user_context to fetch user, role, city, and transaction details.
If no transaction detail ID exists, generates one using dtl_seq.NEXTVAL and logs it in GEN_DB_TRANSACTION_LOG.
On DELETE, inserts the old row into GEN_MST_USR_LST_AUD with action 'D'.
On UPDATE, inserts the old row into GEN_MST_USR_LST_AUD with action 'U'.
Captures all column values along with timestamps and user info for auditing.
Ensures a complete history of user master changes for traceability and accountability.

High-impact PL/SQL resume

Pranav Bakare — Sun, 14 Sep 2025 12:03:03 +0000

Pranav Bakare

Sep 14 '25

Resume Crafting Guide - PLSQL dev

#oracle #data #database #datascience

4 min read

Resume Crafting Guide - PLSQL dev

Pranav Bakare — Sun, 14 Sep 2025 11:59:27 +0000

Resume Crafting Guide

High-Impact Resume Framework for PL/SQL Professionals -

Begin with project context and business impact, then highlight Agile delivery, Change Requests, and defect fixes. Emphasize key achievements in security (SSO, MFA, FGAC) and core PL/SQL development with modular, high-performance solutions. Showcase performance tuning, batch processing, ETL, reporting, and automation using Oracle tools and DBMS_SCHEDULER/Cron. Conclude with deployment across environments, ensuring security, compliance, and maintainability for a robust, scalable platform.

1. Project Context / High-Level Impact

Pioneered Accelya's Airline-First Software Platform, trusted by 200+ global carriers - including Emirates (EK), United Airlines (UA), Swiss International Air Lines (LX), Finnair (AY), Virgin Atlantic (VS), Scandinavian Airlines (SAS), Flydubai (FZ), and Aeroméxico (AM) - to streamline end-to-end cargo operations and drive revenue growth through modular systems like SkyChain and Offer & Order.

2. Agile & Change Management / Key Delivery Achievements

Drove defect remediation and product enhancements by actively engaging in sprint planning and cross-team coordination, successfully handling 25+ Change Requests (CRs) and defect fixes to ensure on-time delivery within Agile SDLC frameworks.
Worked across structured environments - TF (MFA, SSO), TF2 (defects & change requests), UAT, TRG, and PROD - to segregate tasks, validate changes, and ensure smooth, risk-free deployments, contributing to stable, high-quality code delivery and zero post-deployment failures for over a year.
Rationale: This emphasizes your ability to deliver reliably in Agile environments and handle complex change requests, while integrating with structured deployment processes.

3. Security & Authentication (SSO/MFA)

Engineered database-level integration for Single Sign-On (SSO) and Multi-Factor Authentication (MFA) using schema objects, event-driven triggers, and PL/SQL modules, enabling secure session orchestration, token management, and seamless enterprise-grade authentication across applications.
Conceived FGAC (fine-grained access control) with Oracle VPD and DBMS_RLS, enforcing dynamic row-level security based on user roles and session context, ensuring fortified data protection and compliance.
Propelled Audit trigger development using PRAGMA AUTONOMOUS_TRANSACTION to ensure tamper-proof DML logging and full traceability, safeguarding critical transactional data.
Rationale: Frontloads the most visible achievement in terms of enterprise security and compliance.

4. PL/SQL Development & Modularization

Revamped and mobilized 40+ parametrized, autonomous, and layered PL/SQL Packages, Procedures, and Functions with overloading and encapsulation, delivering modular, reusable, and maintainable database solutions.
Formulated the orchestration of Oracle PL/SQL PRAGMAs (Autonomous Transaction, Exception Init, Serially Reusable, Inline), ensuring real-time logging with zero rollback impact, faster debugging through custom error codes, reduced PGA memory consumption, and 5–10% faster execution of compute-heavy modules.
Devised Dynamic SQL using EXECUTE IMMEDIATE with bind variables in PL/SQL to create reusable, secure, and scalable procedures, enabling runtime handling of tables and operations unknown at compile time.

5. Performance Optimization & SQL Tuning

Spearheaded PL/SQL performance optimization initiatives by leveraging Oracle Performance Reports - ASH for real-time session monitoring, AWR for historical workload analysis, and ADDM for actionable recommendations - achieving 30–40% faster query execution and significantly improving system efficiency.
Capitalized on Oracle Performance Views (V$SESSION, V$SQL, V$LOCK) to analyze and optimize PL/SQL code, tune SQL queries, and resolve session/contention issues, reducing execution time and enhancing application throughput.
Fortified process integrity and end-to-end observability for high-volume batch jobs processing millions of transactions by leveraging BULK COLLECT, FORALL, and Global Temporary Tables (GTTs), ensuring efficient session-level processing.

6. Reporting, Data Handling & ETL

Accelerated data retrieval and reporting by implementing Materialized Views (MViews) to cache precomputed results and Pipelined Table Functions for efficient sequential processing, ensuring reliable and scalable reporting workflows.
Forged robust ETL data import/export pipelines leveraging SQL*Loader (direct-path) for fast bulk data loads, External Tables for direct querying of flat files, and UTL_FILE for reliable file handling and error logging - automating exports and improving data accuracy.

7. Job Scheduling & Automation

Curated seamless workflows by designing and orchestrating enterprise-level job scheduling with DBMS_SCHEDULER and Cron jobs, automating 90% of recurring system tasks and reducing manual intervention significantly.

High-Impact Resume Framework for PL/SQL Professionals -
When crafting a high-impact PL/SQL resume section, start with the project context and business impact to immediately set the scale and significance of your work. Next, highlight Agile delivery and change management, including handling of Change Requests (CRs) and defect fixes, to showcase your reliability in team-based environments. Follow this with key achievements in security and authentication, such as SSO and MFA implementations, along with fine-grained access control, emphasizing enterprise-level compliance and data protection. Then, detail core PL/SQL development work, including modularization, Packages, Procedures, Functions, PRAGMAs, and Dynamic SQL, to show technical depth and maintainability. After that, focus on performance optimization and SQL tuning, leveraging tools like ASH, AWR, ADDM, and performance views, demonstrating measurable improvements. Include batch processing and high-volume transaction handling using BULK COLLECT, FORALL, and GTTs for efficient data workflows. Next, cover reporting, ETL, and data handling, such as Materialized Views, Pipelined Table Functions, SQL*Loader, External Tables, and UTL_FILE. Highlight job scheduling and automation with DBMS_SCHEDULER and Cron jobs, showing operational efficiency. Ensure that deployment and environment management are mentioned to indicate risk-free delivery across UAT, TRG, and PROD. Finally, wrap up by emphasizing security, compliance, and maintainability, tying all contributions together to demonstrate a robust, high-quality, and scalable PL/SQL platform.

✅ Summary of Rearrangement Logic

Keep project context first to set the scale and impact.
Highlight Agile, CRs, and defect handling immediately after to show delivery reliability.
Put SSO/MFA & security next, as it's a major achievement.
Move into core PL/SQL development, performance optimization, and data handling - technical depth comes after business impact.
End with automation & job scheduling, showing operational efficiency.

Understanding PRAGMA SERIALLY_REUSABLE in Oracle PL/SQL: A Complete Guide

Pranav Bakare — Tue, 26 Aug 2025 17:22:41 +0000

🔄 Understanding PRAGMA SERIALLY_REUSABLE in Oracle PL/SQL: A Complete Guide

PRAGMA SERIALLY_REUSABLE in Oracle PL/SQL

When working with Oracle PL/SQL packages, memory management and session state become crucial, especially in high-concurrency environments like web-based applications, batch processing systems, or shared server configurations. One powerful yet often overlooked feature for memory optimization is the PRAGMA SERIALLY_REUSABLE directive. This blog aims to break down this concept from scratch, providing both simple explanations and practical use cases for developers ranging from 5 to 15+ years of experience.

📘 What is PRAGMA SERIALLY_REUSABLE?

PRAGMA SERIALLY_REUSABLE is a PL/SQL compiler directive that tells Oracle not to retain global package variables between calls within the same session. In short:

🔹 Without pragma: Global variables persist for the entire session
🔹 With pragma: Global variables reset after each call, saving PGA memory

It is declared in both the package specification and package body, like so:

PRAGMA SERIALLY_REUSABLE;

🧠 Why Was This Introduced?

Oracle introduced this pragma to solve the problem of excessive PGA memory usage caused by persistent global states in packages, especially when the packages are used briefly and don’t need to retain data across session calls.

- In shared server environments (like Oracle’s MTS or web apps), session memory is stored in the User Global Area (UGA), and memory cleanup becomes crucial. Without the pragma, unused variables linger in memory, wasting resources.

📦 A Simple Package WITHOUT PRAGMA

CREATE OR REPLACE PACKAGE DemoPkg IS
PROCEDURE set_val(p_val NUMBER);
PROCEDURE get_val;
END DemoPkg;
/

CREATE OR REPLACE PACKAGE BODY DemoPkg IS
g_val NUMBER;

PROCEDURE set_val(p_val NUMBER) IS
BEGIN
g_val := p_val;
END;

PROCEDURE get_val IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Value: ' || g_val);
END;
END DemoPkg;
/

🔎 Test Execution:

BEGIN
DemoPkg.set_val(99);
DemoPkg.get_val; -- Outputs: Value: 99
END;

Here, the variable g_val retains its value (99) between calls because it is globally persistent.

📦 Same Package WITH PRAGMA SERIALLY_REUSABLE

CREATE OR REPLACE PACKAGE DemoPkg IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE set_val(p_val NUMBER);
PROCEDURE get_val;
END DemoPkg;
/

CREATE OR REPLACE PACKAGE BODY DemoPkg IS
PRAGMA SERIALLY_REUSABLE;
g_val NUMBER;

PROCEDURE set_val(p_val NUMBER) IS
BEGIN
g_val := p_val;
END;

PROCEDURE get_val IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Value: ' || g_val);
END;
END DemoPkg;
/

🔎 Test Execution:

BEGIN
DemoPkg.set_val(99);
DemoPkg.get_val; -- Outputs: Value: (NULL or empty)
END;

Because of the pragma, g_val is reset after set_val ends. So get_val sees no value assigned.

🔍 What’s Happening Behind the Scenes?

Oracle instantiates the package in UGA (User Global Area) rather than PGA
When the call finishes, the package memory is discarded
On the next call, the package is reloaded and reinitialized
Global variables don’t retain their values across calls
Improves memory efficiency at the cost of state persistence

✅ Where Should You Use It?

Use PRAGMA SERIALLY_REUSABLE when:
Your package has global variables used temporarily
You're building stateless modules like APIs, batch processes
You're operating in shared server or web app environments
You want to reduce PGA memory usage

❌ Where Should You Avoid It?

Avoid it when:
You need global state persistence across procedure calls
Your logic relies on maintaining session data
You're in dedicated server environments with abundant memory

🧑‍💼 Real-Time Example

Let’s say you have a package that performs temporary logging or counters in batch jobs. You don’t need to retain those counters after the procedure ends. Applying PRAGMA SERIALLY_REUSABLE here ensures those temporary values don’t bloat session memory—especially if many sessions are calling it simultaneously.

🔑 Keywords Summary

Keyword Meaning

PRAGMA SERIALLY_REUSABLE Oracle directive to discard global state after each call
Global Variable Declared in package body, persists unless pragma is applied
PGA Private memory for session-level data
UGA Shared memory when using shared server
Stateless Session A session where variables do not retain state

🏁 Final Thoughts

Using PRAGMA SERIALLY_REUSABLE is a smart choice when stateless, memory-efficient design is more important than maintaining global variables. Understanding how memory works behind the scenes (PGA vs UGA) and being deliberate about memory optimization is what separates senior developers from others.

Whether you’re building a large-scale enterprise app, a RESTful API, or a batch processor, this pragma can help you write scalable and efficient PL/SQL.

ASH | AWR | ADDM in Oracle Database

Pranav Bakare — Tue, 26 Aug 2025 17:18:28 +0000

ASH (Active Session History)

Think of ASH like a CCTV camera recording live activity inside the database. It captures what each active session is doing at any given second — which SQL_ID is running, what wait events are happening, and if there’s any blocking session. For example, if a PL/SQL batch job is running now and users complain that the system has slowed down, ASH can immediately show that the job is waiting on a row lock in a specific table or consuming heavy I/O. This makes ASH the first tool to check for real-time bottlenecks.

AWR (Automatic Workload Repository)

AWR is more like a daily ledger or periodic report that the database maintains automatically. It takes snapshots every hour and records workload history — top SQL queries, CPU usage, I/O patterns, and execution plans. For example, if a data migration procedure ran fine yesterday but became slow today, AWR reports can compare the two snapshots and reveal that the execution plan changed, or the query started doing full table scans instead of using indexes. AWR is best for analyzing past performance issues and long-term trends.

ADDM (Automatic Database Diagnostic Monitor)

ADDM acts like a financial auditor or expert advisor who reviews the ledger (AWR snapshots) and gives clear recommendations. Instead of just showing numbers, it tells you the impact and what actions to take. For example, ADDM may analyze an AWR report and highlight: “SQL_ID xyz is consuming 75% of DB time, suggest creating an index on column A to improve performance by 50%.” This makes ADDM extremely useful for a PL/SQL developer because it converts raw data into actionable tuning advice that directly addresses performance bottlenecks.

PRAGMA SERIALLY_REUSABLE in PL/SQL

Pranav Bakare — Tue, 26 Aug 2025 17:16:37 +0000

PRAGMA SERIALLY_REUSABLE in PL/SQL —

In PL/SQL, we often use packages to hold variables, cursors, and procedures. Normally, package variables maintain their state throughout a user’s session, meaning that once you set a value, it stays until you disconnect. But sometimes, this behavior can waste memory when thousands of sessions are open, and each is holding unnecessary package state. To address this, Oracle provides PRAGMA SERIALLY_REUSABLE, which tells the compiler that the package state should only exist for the duration of a single call. After the call ends, Oracle automatically erases the package’s memory, ensuring efficient memory usage.

📌 The Whiteboard Analogy

Think of PRAGMA SERIALLY_REUSABLE as a whiteboard in a classroom.
During a lecture (a PL/SQL block execution), the teacher writes notes (package variables).
Students use this information while the lecture is ongoing.
Once the lecture ends (execution finishes), the board is wiped clean (memory released).
When the next lecture begins (a new call), the board is blank again, ready for fresh notes.
This analogy perfectly explains how Oracle treats packages marked with SERIALLY_REUSABLE: temporary storage, automatically reset after each call.

📌 Practical Example

Let’s create a package that demonstrates this behavior:

-- Package with PRAGMA SERIALLY_REUSABLE
CREATE OR REPLACE PACKAGE temp_pkg
PRAGMA SERIALLY_REUSABLE AS
g_message VARCHAR2(100) := 'Initial';
PROCEDURE set_message(p_msg VARCHAR2);
PROCEDURE show_message;
END temp_pkg;
/

CREATE OR REPLACE PACKAGE BODY temp_pkg
PRAGMA SERIALLY_REUSABLE AS
PROCEDURE set_message(p_msg VARCHAR2) IS
BEGIN
g_message := p_msg;
END;

PROCEDURE show_message IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Message = ' || g_message);
END;
END temp_pkg;
/

📌 Execution and Output

BEGIN
temp_pkg.set_message('Hello');
temp_pkg.show_message; -- Output: Message = Hello
END;
/

BEGIN
temp_pkg.show_message; -- Output: Message = Initial (state reset, like a cleaned whiteboard)
END;
/

🔎 Explanation:

In the first block, we updated g_message to “Hello,” and it printed correctly.
But once the block ended, Oracle wiped the memory used by the package.
In the next block, the variable returned to its default value (Initial), proving that the package does not persist its state across calls.

📌 When to Use

When package variables are temporary and do not need to persist across calls.
To reduce memory usage in systems with many users accessing packages.
For short-lived values like counters, temporary logs, or small caches.

📌 Seven-Liner Summary

PRAGMA SERIALLY_REUSABLE is a PL/SQL compiler directive that makes package variables temporary instead of session-persistent.
It allocates memory only during a call and frees it immediately once the call ends.
This prevents memory bloat in large, multi-user systems where many sessions access the same package.
Unlike normal packages, state is not carried forward between different calls.
A perfect analogy is a classroom whiteboard that is erased after each lecture.
Each execution starts with a fresh package state, initialized with default values.
Thus, it is ideal for temporary, non-persistent tasks where maintaining state is unnecessary.

PRAGMA SERIALLY_REUSABLE in PL/SQL is a compiler directive that ensures the package memory is allocated only during a single program call and released as soon as the call ends. This has a significant impact on memory management because package variables, which normally persist for the entire user session, no longer occupy memory unnecessarily across multiple executions. Its ideal feature is that it prevents the accumulation of unused session state, making it very useful in systems with thousands of users where conserving memory is critical. Each new call to such a package begins with freshly initialized variables, ensuring that no previous state is carried forward. This makes it best suited for temporary operations where maintaining state across calls is neither required nor desirable.

For example, consider a package that maintains a message variable or a counter. If the package is defined as serially reusable, setting a message or incrementing the counter during one block execution will only hold for that call. The moment the block ends, Oracle releases the memory and the variables revert to their default values. When the next block executes, the package begins afresh as though nothing was stored before, ensuring no state leakage from the past. This is much like a classroom whiteboard: notes written during the lecture are useful while it lasts, but once the lecture ends, the board is erased, ready for the next session. In the same way, PRAGMA SERIALLY_REUSABLE guarantees that each call starts with a clean state, optimizing memory usage while maintaining clarity and consistency of execution.

— — — — —

PRAGMA SERIALLY_REUSABLE

Audit Triggers + PRAGMA AUTONOMOUS_TRANSACTION

Pranav Bakare — Tue, 26 Aug 2025 17:12:22 +0000

Audit Triggers + PRAGMA AUTONOMOUS_TRANSACTION

There is a main transactional table where all business operations are recorded. To track changes and maintain accountability, a separate audit table is created to capture the data state changes of the main table. Whenever a DML operation such as INSERT, UPDATE, or DELETE occurs on the main table, a trigger mechanism fires and inserts a corresponding entry into the audit table. To ensure that these audit entries are recorded reliably, even if the main transaction is rolled back, the insertion logic is encapsulated within a PRAGMA AUTONOMOUS_TRANSACTION procedure. This ensures that the audit insertion operates as an independent transaction, committing its changes regardless of the success or failure of the main table operation. As a result, every change to the main table is logged persistently in the audit table, providing a robust, tamper-proof auditing mechanism that guarantees data traceability and accountability. This combination of triggers and autonomous transactions ensures that auditing is handled automatically and consistently, without depending on the outcome of the main transaction.

💡 Summary:

Trigger = detect event

Autonomous procedure = persist audit record independently

Trigger + PRAGMA AUTONOMOUS_TRANSACTION = robust, tamper-proof auditing

Let’s make a simple, fully integrated example with main table, audit table, trigger, and PRAGMA AUTONOMOUS_TRANSACTION procedure so it’s crystal clear.

1️⃣ Main Table (Domain Table)

CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
salary NUMBER,
dept_id NUMBER
);

This is the table where normal business operations occur.

2️⃣ Audit Table

CREATE TABLE employees_audit (
audit_id NUMBER PRIMARY KEY,
emp_id NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
dept_id NUMBER,
action_type VARCHAR2(10),
action_time DATE
);

Stores all changes (INSERT/UPDATE/DELETE) from the main table.

3️⃣ Audit Procedure with PRAGMA AUTONOMOUS_TRANSACTION

CREATE OR REPLACE PROCEDURE log_employee_audit(
p_emp_id NUMBER,
p_emp_name VARCHAR2,
p_salary NUMBER,
p_dept_id NUMBER,
p_action VARCHAR2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO employees_audit (
audit_id, emp_id, emp_name, salary, dept_id, action_type, action_time
) VALUES (
employees_audit_seq.NEXTVAL, p_emp_id, p_emp_name, p_salary, p_dept_id, p_action, SYSDATE
);
COMMIT; -- Independent commit ensures audit entry persists
END;

PRAGMA AUTONOMOUS_TRANSACTION makes this independent of main table transactions.

4️⃣ Trigger on Main Table

CREATE OR REPLACE TRIGGER trg_employee_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
IF INSERTING THEN
log_employee_audit(:NEW.emp_id, :NEW.emp_name, :NEW.salary, :NEW.dept_id, 'INSERT');
ELSIF UPDATING THEN
log_employee_audit(:NEW.emp_id, :NEW.emp_name, :NEW.salary, :NEW.dept_id, 'UPDATE');
ELSIF DELETING THEN
log_employee_audit(:OLD.emp_id, :OLD.emp_name, :OLD.salary, :OLD.dept_id, 'DELETE');
END IF;
END;

Trigger automatically detects DML operations on employees.

Calls the autonomous procedure to log the change independently.

5️⃣ How It Works

User inserts/updates/deletes a row in employees.
trg_employee_audit fires automatically.
Trigger calls log_employee_audit, which writes a record to employees_audit.
Even if the main transaction rolls back, the audit entry is persisted because the procedure has an autonomous transaction.
Result → tamper-proof, reliable audit trail for all critical operations.

Behind-the-scenes explanation of what happens step by step when a record is inserted or updated into the main table (employees) with your audit trigger + PRAGMA AUTONOMOUS_TRANSACTION setup.

1️⃣ User performs a DML operation on the main table

Example:

INSERT INTO employees (emp_id, emp_name, salary, dept_id)
VALUES (101, 'Pranav', 50000, 10);

Oracle receives the INSERT command.

The database engine begins a transaction for this operation.

The row is added to the buffer, but it is not yet committed to disk.

2️⃣ Trigger fires automatically

Since trg_employee_audit is defined as AFTER INSERT OR UPDATE OR DELETE, Oracle detects the change and fires the trigger after the DML operation is performed (but still within the same transaction).

The trigger identifies the type of operation (INSERT, UPDATE, or DELETE).

3️⃣ Trigger calls the audit procedure

log_employee_audit(:NEW.emp_id, :NEW.emp_name, :NEW.salary, :NEW.dept_id, 'INSERT');

The procedure log_employee_audit is executed.

Inside the procedure, PRAGMA AUTONOMOUS_TRANSACTION tells Oracle:

“Start a separate transaction for this block, independent of the main transaction.”

4️⃣ Autonomous transaction inserts into the audit table

The procedure inserts a record into employees_audit with:

Employee ID, name, salary, department

Action type (‘INSERT’)

Timestamp (SYSDATE)

Then the procedure commits this transaction immediately, independently of the main transaction.

5️⃣ Control returns to the main transaction

Oracle continues processing the original INSERT in employees.

The main transaction is still active and has not committed yet.

If the main transaction rolls back, the insert in employees is undone, but the audit log remains because it was committed in the autonomous transaction.

6️⃣ End result

Audit table always contains a record of the attempted operation, even if the main table operation fails.

Main table contains the new record only if the transaction commits successfully.

This ensures:

Tamper-proof logging

Full accountability

Traceability of operations

In short:

Main table DML → Trigger fires → Calls autonomous procedure → Audit table entry committed independently → Control returns to main transaction → Main transaction commits or rolls back