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;
The
high_value
inall_tab_partitions
had data typelong
, 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 fortable_owner
,table_name
andpartition_name
, andhigh_value
now as avarchar2
value instead of along
.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 originallong
value - with the current date.Incidentally, since not all years have 365 days, you could look back 12 months instead with: