DROP an one year old partition of a table in Oracle

Question

I had to drop a partition of a table which is one year old. Now, in the all_tab_partitions , the HIGH_VALUE column is of LONG datatype and my table is partitioned on RANGE (date column) . Hence, I had to figure out a way to read this column and then determine whether the partition is an year old. I was able to get the following script from somewhere and used it as per my requirement.However, I am not able to understand what it does. Kindly help me understand this piece of code:

WITH xml AS (
           SELECT XMLTYPE(
                     DBMS_XMLGEN.GETXML('select partition_name,table_name,table_owner,high_value from all_tab_partitions where table_owner=''VOYAGER''
                      ')
                    ) AS xml
            FROM   dual
            )
   ,    parsed_xml 
        AS (SELECT EXTRACTVALUE (xs.object_value, '/ROW/TABLE_NAME')
                      AS table_name,
                   EXTRACTVALUE (xs.object_value, '/ROW/HIGH_VALUE')
                      AS high_value,
                   EXTRACTVALUE (xs.object_value, '/ROW/PARTITION_NAME')
                      AS partition_name,
                   EXTRACTVALUE (xs.object_value, '/ROW/TABLE_OWNER')
                      AS table_owner
              FROM xml x,
                   TABLE (XMLSEQUENCE (EXTRACT (x.xml, '/ROWSET/ROW'))) xs)
   SELECT PARTITION_NAME ,table_owner , table_name 
     FROM parsed_xml
    WHERE   --  TABLE_OWNER = 'VOYAGER' 
             TRUNC (SYSDATE)
              - TO_DATE (SUBSTR (HIGH_VALUE, 11, 10), 'YYYY-MM-DD') >= 365;

Answer 1

The high_value in all_tab_partitions had data type long, which is a pain to work with.

The first CTE here is using the dbms_xmlgen package to get an XML representation of the data in the table, which means that long column is implicitly converted to a normal text node value.

The second CTE then uses XML manipulation, including the deprecated extractvalue function, to convert that XML back to a relational form; essentially giving you a 'table' with the same values for table_owner, table_name and partition_name, and high_value now as a varchar2 value instead of a long.

Finally that relational data can be used to compare the high value - which you can now refer to easily as a string, and use in functions like substr where you could not use the original long value - with the current date.

Incidentally, since not all years have 365 days, you could look back 12 months instead with:

WHERE TO_DATE (SUBSTR (HIGH_VALUE, 11, 10), 'YYYY-MM-DD')
  < ADD_MONTHS(TRUNC(SYSDATE), -12);