Forem: Pranav Bakare

[Boost]

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

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

UWABOR KING COLLINS ・ Sep 30 '24

#devops #cloudcomputing #terraform #tutorial

mrcaption49 | Pranav Bakare

Pranav Bakare — Wed, 25 Feb 2026 01:28:41 +0000

4.6+ Years Oracle PL/SQL Developer With: Enterprise-scale database systems (218M+ users – Vodafone Idea) Airline domain product platform (Accelya – 200+ global carriers) Advanced performance tuning (AWR, ASH, ADDM) Bulk processing (BULK COLLECT, FORALL) Advanced security (VPD, FGAC, DBMS_RLS) CI/CD for Database (Liquibase + GitLab + Gradle) ETL frameworks, SQL*Loader, External Tables Scheduler automation (DBMS_SCHEDULER + Cron) Materialized Views (Fast Refresh) Partitioning & Archival Framework PRAGMA usage & Autonomous Transactions

Here is your ~500-word professional summary

Pranav Bakare is a results-driven Oracle PL/SQL Developer with over 4.6 years of hands-on experience in designing, developing, and optimizing enterprise-grade database systems. He possesses deep expertise in Oracle Database development, performance tuning, and high-volume transaction processing across telecom and airline industry domains. His professional journey spans large-scale production environments supporting millions of users, where performance, scalability, and data integrity are mission-critical.
Currently working as a Software Engineer at Nexsys IT Consulting, contributing to Accelya’s airline cargo platform trusted by 200+ global carriers, Pranav plays a key role in engineering modular, high-performance PL/SQL solutions. He has designed and revamped over 40 parameterized PL/SQL packages, procedures, and functions using advanced concepts such as overloading, encapsulation, PRAGMA directives, and autonomous transactions. His work emphasizes maintainability, modularity, and execution efficiency in complex enterprise environments.
He has strong expertise in performance engineering using Oracle’s diagnostic tools such as AWR (Automatic Workload Repository), ASH (Active Session History), and ADDM (Automatic Database Diagnostic Monitor). Through systematic SQL tuning, optimizer analysis, indexing strategies, and partitioning techniques, he has achieved measurable improvements including 30–40% faster query performance and significant storage optimization. His experience includes working extensively with BULK COLLECT, FORALL, Global Temporary Tables (GTTs), and pipelined table functions to optimize large-scale data processing and reduce context switching.
Pranav has also implemented secure database frameworks using Oracle Virtual Private Database (VPD), DBMS_RLS for fine-grained access control (FGAC), and audit triggers leveraging PRAGMA AUTONOMOUS_TRANSACTION to ensure tamper-proof transaction logging. His contributions have strengthened data governance, compliance, and row-level security enforcement in enterprise applications.
Beyond core PL/SQL development, he has engineered automated ETL pipelines using staging tables, MERGE statements, SQL*Loader (direct-path loading), External Tables, and UTL_FILE for efficient data import/export operations. He has also built metadata-driven archival frameworks using dynamic SQL (EXECUTE IMMEDIATE) and Oracle data dictionary views to automate schema-agnostic DDL/DML processes.
A strong advocate of DevOps for databases, Pranav has architected multi-stage CI/CD pipelines using GitLab, Liquibase, and Gradle to enable version-controlled, automated database deployments across multiple environments including UAT, TRG, and PROD. His structured deployment strategy has resulted in zero post-production failures for extended periods.
Earlier at Tata Consultancy Services, he supported one of India’s largest telecom platforms serving over 218 million users, where he engineered high-performance database solutions, automated job scheduling using DBMS_SCHEDULER and Cron, and reduced debugging time by 40% through a centralized exception-handling framework.
In addition to database specialization, Pranav possesses full-stack capabilities in Python/Django, along with cloud exposure to AWS, GCP, Docker, and Kubernetes, allowing him to understand end-to-end application architecture. This hybrid expertise enables him to design database solutions aligned with modern distributed systems and scalable cloud-native environments.
Overall, Pranav is positioned as a Senior Oracle PL/SQL Developer with strong expertise in performance optimization, database architecture, security implementation, CI/CD automation, and enterprise-scale system engineering.

Payment Integration Built with Django and PayPal | mrcaption49

Pranav Bakare — Mon, 26 Jan 2026 06:52:42 +0000

A Secure Payment Integration Built with Django and PayPal

Django-based payment integration application

This project is a Django-based payment integration application developed to demonstrate how online payments can be securely processed within a web application. The platform integrates PayPal as a payment gateway, enabling users to initiate and complete transactions through a smooth and reliable checkout flow.
The application is built using Django’s Model–View–Template (MVT) architecture, ensuring a well-structured and maintainable backend. Payment-related logic is handled on the server side, maintaining security and data integrity throughout the transaction lifecycle.
Users are redirected to PayPal’s secure payment interface to complete transactions, after which they are returned to the application with appropriate success or failure responses. This flow ensures a safe payment experience while following standard industry practices for third-party payment gateways.
The project demonstrates proper handling of payment requests, transaction status validation, and response management. It also lays the foundation for future enhancements such as order history, payment logs, refunds, and user authentication.
The application is deployed on Render, showcasing real-world deployment, environment configuration, and production readiness. Overall, this project represents a practical implementation of payment gateway integration using Django, combining backend security, third-party service integration, and scalable architecture.

🔗 Live Demo: https://django-paypal49.onrender.com/

10-Line Descriptive Summary

This Django application demonstrates secure payment gateway integration using PayPal. It follows Django’s Model–View–Template architecture for clean and maintainable code. The platform enables users to initiate and complete online payments securely. PayPal handles the transaction process through a trusted external interface. Server-side logic ensures proper payment flow and response handling. The application maintains security and data integrity throughout transactions. The backend is structured for scalability and future enhancements. Deployment on Render highlights production-level configuration. The project reflects real-world payment integration practices. A strong example of Django-based payment processing.

Blogging Platform Built with Django | mrcaption49

Pranav Bakare — Mon, 26 Jan 2026 06:50:01 +0000

A Clean and Scalable Blogging Platform Built with Django

This project is a Django-based blogging platform designed to demonstrate core content management principles using a clean, scalable, and maintainable backend architecture. The application enables users to browse blog posts in a well-structured layout, navigate seamlessly between articles, and access each post through a dedicated detail page for an uninterrupted reading experience.

Built using Django’s Model–View–Template (MVT) architecture, the application ensures a clear separation of concerns between data models, business logic, and presentation layers. This architectural approach improves code readability, simplifies maintenance, and allows the application to scale efficiently as new features are introduced.

Blog posts are rendered using optimized Django templates that emphasize readability, consistency, and user experience. The structured layout ensures content is easy to consume, while clean template design supports reusable components and faster rendering.

Each blog post opens on its own standalone page, providing focused content delivery and creating a strong foundation for future enhancements such as tagging systems, commenting features, search functionality, and user authentication. These extensibility options make the platform adaptable to evolving requirements.

The application is deployed on Render, highlighting real-world deployment practices, environment configuration, and production readiness. Overall, this project reflects a practical implementation of a blogging system using Django, combining backend logic, structured content flow, and modern deployment techniques.

🔗 Live Demo: https://djangoblog49.onrender.com/

10-Line Descriptive Summary

This Django blogging platform showcases a practical implementation of content management using a clean and scalable backend. It is built using Django’s Model–View–Template architecture to ensure maintainability and clarity. The application presents blog posts in a structured and readable layout. Users can navigate content smoothly across the platform. Each post opens on a dedicated detail page for focused reading. Django templates are optimized for consistency and user experience. Backend logic is designed to be clean and efficient. The platform supports easy extensibility for future features. Deployment on Render demonstrates production-level readiness. Overall, the project reflects real-world Django development practices.

A Simple Photo Album Built with Django | mrcaption4

Pranav Bakare — Mon, 26 Jan 2026 06:39:52 +0000

A Simple Photo Album Built with Django

Photo Album application using Django and Python

Summary -

This project is a Django-based Image Gallery Application designed to showcase real-world media handling and scalable web architecture. It enables users to upload images, add descriptions, and organize content using dynamic category management. The application supports category-based filtering, allowing users to view all images or narrow results efficiently. Each image is rendered on a dedicated detail page for focused viewing. Built using Django’s Model–View–Template (MVT) architecture, the project ensures clean code separation and maintainability. The user interface is developed with Bootstrap 5, delivering a responsive and consistent UI with minimal custom CSS. Django forms handle secure file uploads using multipart/form-data and CSRF protection. Images are managed using Django’s ImageField with Pillow for processing. For production readiness, the project integrates AWS S3 via django-storages and boto3 to provide scalable cloud-based media storage. Proper static and media file configuration supports both development and deployment environments. The system efficiently manages database relationships using foreign keys. Overall, this project demonstrates backend development expertise, cloud integration, and production-ready Django practices.

Project Explaination-

I built a category-based Photo Album application using Django and Python to explore real-world media handling and clean backend architecture. The application allows users to browse photos by category, view them in a gallery layout, and open each image on a dedicated detail page.
The project follows Django’s Model–View–Template (MVT) pattern, ensuring clear separation of concerns and maintainable code. Categories improve navigation, while thumbnail previews keep the interface fast and visually clean.
Each photo opens on its own page, creating a focused viewing experience and laying the foundation for future features like likes, views, or comments. The application is deployed on Render, demonstrating production-level configuration and public hosting.
This project reflects practical Django development—combining structured backend logic, media handling, and real-world deployment into a clean, scalable web application.

🔗 Live Demo: https://django-photoalbum-2025.onrender.com/

10-Line Insightful Summary

This Django Photo Album application demonstrates real-world media handling using a clean and structured backend. Photos are organized into categories for better navigation and filtering. The gallery layout ensures fast and intuitive browsing. Each image opens on a standalone detail page for focused viewing. The project follows Django’s MVT architecture for maintainability. Thumbnail previews enhance performance and UI clarity. Deployment on Render showcases production readiness. The application is simple, scalable, and extendable. It reflects strong Django fundamentals. A solid portfolio project with real-world relevance.

CICD YML file Learnings!

Pranav Bakare — Sun, 28 Sep 2025 05:34:37 +0000

CICD YML file Learnings!

In our project, we automated database deployments by integrating Liquibase with GitLab CI/CD. We structured the repository with environment-specific folders and a master changelog, ensuring version-controlled schema changes. The pipeline runs nightly with three stages — Pre-DB (backups, validations, SQL preview), DB (Liquibase update & tagging), and Post-DB (data fixes, stats gathering, recompiling invalid objects). Database credentials and JDBC drivers are securely managed through CI variables and artifacts. Each changeset includes rollback scripts and is previewed using liquibase updateSQL before execution, reducing production risks. Scheduled pipelines are configured with proper rules, and logs and previews are stored as artifacts for auditing. This approach minimized manual effort, improved reliability, and ensured consistent database deployments across environments.

Gitlab cicd pipeline Build automation

In GitLab, a scheduled pipeline allows automated execution of jobs at specific times using cron expressions. The .gitlab-ci.yml file defines the pipeline’s structure, including stages, jobs, and the scripts to run. Each job can specify rules or only: [schedules] to control when it runs, ensuring scheduled jobs don’t trigger on pushes or merge requests. When a schedule is created in the GitLab UI, it targets a branch and defines the timing and optional variables. At the scheduled time, GitLab triggers a pipeline on the selected branch with CI_PIPELINE_SOURCE="schedule". The runner picks up jobs whose rules match the scheduled context and executes the defined scripts. Job logs and statuses are captured in GitLab for monitoring and debugging. Variables defined in the schedule are passed into the pipeline, allowing dynamic configuration. Using stages ensures jobs execute in the intended order even in complex pipelines. Overall, this integration provides a reliable, automated mechanism for running repetitive tasks without manual intervention. By combining .gitlab-ci.yml rules and GitLab schedules, teams can fully control what runs and when, maintaining flexibility and efficiency in CI/CD workflows.

Summary-

Constructed and refined GitLab CI/CD pipelines (YAML-based) to automate execution of PL/SQL scripts and database changes, eliminating manual interventions.

Understanding GitLab Scheduled Pipelines and YAML Configuration

Pranav Bakare — Sat, 27 Sep 2025 06:54:28 +0000

Understanding GitLab Scheduled Pipelines and YAML Configuration

In GitLab,

A scheduled pipeline allows automated execution of jobs at specific times using cron expressions. The .gitlab-ci.yml file defines the pipeline’s structure, including stages, jobs, and the scripts to run. Each job can specify rules or only: [schedules] to control when it runs, ensuring scheduled jobs don’t trigger on pushes or merge requests. When a schedule is created in the GitLab UI, it targets a branch and defines the timing and optional variables.
At the scheduled time, GitLab triggers a pipeline on the selected branch with CI_PIPELINE_SOURCE="schedule". The runner picks up jobs whose rules match the scheduled context and executes the defined scripts.
Job logs and statuses are captured in GitLab for monitoring and debugging. Variables defined in the schedule are passed into the pipeline, allowing dynamic configuration. Using stages ensures jobs execute in the intended order even in complex pipelines.
Overall, this integration provides a reliable, automated mechanism for running repetitive tasks without manual intervention. By combining .gitlab-ci.yml rules and GitLab schedules, teams can fully control what runs and when, maintaining flexibility and efficiency in CI/CD workflows.

Here’s the basic and minimal .gitlab-ci.yml file structure you need to schedule your job every day at 11:11 AM from GitLab.

This is the simplest version (no workflow, no extra variables):

stages:
  - run

scheduled_job:
  stage: run
  script:
    - echo "Scheduled job running at $(date)"
    - echo "Put your script logic here"
  rules:
    # Run only when the pipeline is triggered by a schedule
    - if: '$CI_PIPELINE_SOURCE == "schedule"'
      when: always
    # Prevent job from running on push/merge
    - when: never

Steps to Schedule in GitLab:

Go to Project → CI/CD → Schedules.
Click New schedule.
Enter:
Interval pattern: 11 11 * * *
Target branch: the branch where the above .gitlab-ci.yml exists (e.g., main)
Timezone: your local timezone
Click Save.

Now this job will run every day at 11:11 AM and execute the commands you placed in script:

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');

Test VS CODE Snippet - CodeSnap

Pranav Bakare — Sat, 20 Sep 2025 06:47:05 +0000

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

Comments

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.