Creating Older MUDR LP_INTERVALS Database Partitions

We were looking to retroactively load meter data from older MV90 meters up to 5 years into the past (3 years before we implemented energyIP 7.2).  I've not had extensive exposure to partitioning until taking on the administration for EnergyIP.  I'd created, dropped and truncated partitions mudr range partitions before but had never created a partition with a high date value in the past - always newer partitions to capture future data.

I'd thought this would be simple until I realized you can't add partitions in the past!  You have to split the oldest partition into two new ones.

To further complicate things I needed to do this for multiple tables and create an additional 60 partitions in each.  This would be a time consuming process to do manually so I decided to automate the process via a little PL/SQL.

For some reason oracle decided to store high value of each partition value in a long column instead of a varchar.  Not sure why but it causes some issues that were addressed using dbms_sql package tools.

Here is the anonymous PL/SQL block to add 60 monthly partitions in the past on the LP_INTERVALS table.  Modify accordingly to run on LP_BATCH, REGISTER_READS, etc.  USE AT YOUR OWN RISK.  TEST FIRST!


SET SERVEROUTPUT ON

DECLARE
   l_partition_name       VARCHAR2 (30 BYTE);
   l_hival                VARCHAR2 (4000);
   l_sql                  VARCHAR2 (4000);
   l_high_date            DATE;
   l_cursor               INTEGER DEFAULT DBMS_SQL.open_cursor;
   l_rows_back            NUMBER;
   l_lowval               DATE;
   l_new_partition_name   VARCHAR2 (30 BYTE);
BEGIN
   FOR i IN 1 .. (12 * 5) --number of months of partitions to create back in the past from the oldest partition
   LOOP
      -- partition position = 1 always grabs the "oldest" partition
      SELECT high_value, partition_name
        INTO l_hival, l_partition_name
        FROM dba_tab_partitions
       WHERE table_name = 'LP_INTERVALS' AND partition_position = 1;

      --scrape date from high_value stupid long column into varchar

      DBMS_SQL.parse (l_cursor,
                      'begin :retval := ' || l_hival || '; end;',
                      DBMS_SQL.native);
      --put scraped valued into date variable
      DBMS_SQL.bind_variable (l_cursor, ':retval', l_high_date);
      l_rows_back := DBMS_SQL.execute (l_cursor);
      DBMS_SQL.variable_value (l_cursor, ':retval', l_high_date);

      --generate datevariable  at which to split the partition
      l_lowval := ADD_MONTHS (l_high_date, -1);

      --generate name for newly created older partition resulting from split
      l_new_partition_name :=
         'LP_' || TO_CHAR (ADD_MONTHS (l_lowval, -1), 'MON_YYYY');

      --DBMS_OUTPUT.put_line (
      --'ALTER TABLE EMAPP.LP_INTERVALS SPLIT PARTITION '||l_partition_name||' AT (TO_DATE ('''||l_lowval||''', ''DD-MON-YYYY'')) INTO (PARTITION '||l_partition_name||' TABLESPACE DATA), PARTITION '||l_new_partition_name||' tablespace DATA) UPDATE GLOBAL INDEXES');

      --build alter statement to split the oldest partition into two
      l_sql :=
            'ALTER TABLE EMAPP.LP_INTERVALS SPLIT PARTITION '
         || l_partition_name
         || ' AT (TO_DATE ('''
         || TO_CHAR (l_lowval, 'DD-MON-YYYY')
         || ''', ''DD-MON-YYYY'')) INTO (PARTITION '
         || l_new_partition_name
         || ' TABLESPACE DATA, PARTITION '
         || l_partition_name
         || ' tablespace DATA) UPDATE GLOBAL INDEXES';

      DBMS_OUTPUT.put_line (l_sql);

      --split the oldest partition
      EXECUTE IMMEDIATE l_sql;
   END LOOP;
END;
/

have fun!