As every year, it’s time to clean up the databases Marie Kondo style.
Database administrators have to historicize, compress or delete old partitions that are no longer needed.
Obviously, partitioned tables (and their local indexes) are the best place to delete old information. Many regulations require us to keep data for a certain number of years, but after this time, that information takes up space and, in some cases, can be a cost nuisance if Oracle resolves access to these outdated partitions.
In each company I have seen a different historicification method. In many cases it was done manually at the beginning of the year, creating new partitions and deleting those of the year that were no longer necessary.
On the part of adding partitions, with the INTERVAL clause it is no longer necessary to carry out this task, since in a table created with the following definition, it only needs the definition of the name of a first partition that will have all the values prior to that date, and at insert data that would correspond to partitions outside the range, Oracle will automatically create those partitions by month:
create table test_particionado_intervalo(
id number constraint pk_test_particionado_intervalo primary key,
fecha date,
valor varchar2(40))
partition by range (fecha) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION P1_TEST_PARTICIONADO_ABRIL_2018
VALUES LESS THAN (TO_DATE('01-MAY-2018', 'DD-MON-YYYY')));
SQL> insert into test_particionado_intervalo
values (1,TO_DATE('15-APR-2018','DD-MON-YYYY'),'Dato Abril 2018');
1 row created.
SQL> insert into test_particionado_intervalo
values (2,TO_DATE('15-SEP-2018','DD-MON-YYYY'),'Dato Sept 2018');
1 row created.
SQL> insert into test_particionado_intervalo
values (3,TO_DATE('15-SEP-2017','DD-MON-YYYY'),'Dato Sept 2017');
1 row created.
SQL> insert into test_particionado_intervalo
values (4,TO_DATE('15-SEP-2019','DD-MON-YYYY'),'Dato Sept 2019');
1 row created.
SQL> column partition_name format a40
SQL> select partition_name, high_value from DBA_TAB_PARTITIONS 2 where table_name = 'TEST_PARTICIONADO_INTERVALO' and table_owner='LABORATORIO';
PARTITION_NAME HIGH_VALUE
---------------------------------------- ---------------------------------------------------
P1_TEST_PARTICIONADO_ABRIL_2018 TO_DATE(' 2018-05-01 00:00:00', 'SYYYY-MM-DD
SYS_P5601 TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD
SYS_P5602 TO_DATE(' 2019-10-01 00:00:00', 'SYYYY-MM-DD
Sure, identifying these partitions by name can be tricky, and the recommendation would be to rename them with an ALTER TABLE command like this.
SQL> ALTER TABLE TEST_PARTICIONADO_INTERVALO RENAME PARTITION sys_p5602 TO P1_TEST_PARTICIONADO_SEPT_2019;
Table altered.
Anyway, we are only talking about one table, but what happens when you have many? What happens if the ranges are different (months, days, quarters,…)? what about composite partitioning where you have to check subpartitions as well? what if the date fields are not just DATE, but also TIMESTAMP?
Well, although it might seem simple, there is an added difficulty: the HIGH_VALUE field of the DBA_TAB_PARTITIONS and DBA_TAB_SUBPARTITIONS views is of the LONG type.
LONG fields, old times, in Oracle 7.
Oracle in version 8 recommended that LONG fields be stopped being used, in favor of LOBs, but if we talk about the dictionary, that’s something else. Many tables contain LONG and the truth is that its versatility in SQL is increasingly limited.
For example, you can’t SUBSTR() a LONG field in SQL. You also can’t do a CAST to convert it to VARCHAR2.
SQL> select partition_name, cast(HIGH_VALUE as VARCHAR2(100)) high_value 2 from DBA_TAB_PARTITIONS 3 where table_name = 'TEST_PARTICIONADO_INTERVALO' and table_owner='LABORATORIO'; select partition_name, cast(HIGH_VALUE as VARCHAR2(100)) high_value * ERROR at line 1: ORA-00932: inconsistent datatypes: expected CHAR got LONG
Ouch… And how about converting it to LOB with the TO_LOB function?
SQL> select partition_name, to_lob(HIGH_VALUE) high_value 2 from DBA_TAB_PARTITIONS 3 where table_name = 'TEST_PARTICIONADO_INTERVALO' and table_owner='LABORATORIO'; select partition_name, to_lob(HIGH_VALUE) high_value * ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got LONG
Well, the solution is to implement a procedure in PL/SQL that generates the commands (and for brave DBAs, they can change the DBMS_OUTPUT to an EXECUTE IMMEDIATE) and that’s it.
In PL/SQL the LONG data type is much more versatile since it does not have the limitations of the physical implementation linked to LONG, VARCHAR2, CHAR, etc.
That is, from PL/SQL it is possible to use the SUBSTR function with LONG fields that return a VARCHAR2, and with this compose the partition maintenance syntax.
The following procedure does the following: given a user, a table, and a specific date, it looks for those partitions or subpartitions prior to that date and through a simple FOR loop, generates the corresponding ALTER TABLE DROP|TRUNCATE PARTITION|SUBPARTITION commands.
Being prudent, and in order not to cause damage, I have implemented the procedure so that it generates the output in DBMS_OUTPUT, and each one can execute by hand what they consider. Of course, in this procedure you can replace DBMS_OUTPUT with EXECUTE IMMEDIATE and have the same procedure perform the maintenance task.
This code is in the Café Database GitHub repository, and you are free to fork, improve, optimize, recommend and share it.
https://github.com/CafeDatabase/Old-Partition-Management
Heyyaa! Enjoy it. (The code is much well formated in the GitHub site!)
create or replace procedure manage_old_partitions (v_owner varchar2,
v_table varchar2, v_date date, v_operation varchar2 default 'drop')
is v_texto varchar2(32000);
v_mascara varchar2(32000);
var_operation varchar2(32000);
v_size number;
BEGIN
v_size:=0;
dbms_output.enable(1000000);
if lower(v_operation) not in ('drop','truncate') then
var_operation:='drop';
else var_operation:=v_operation;
end if;
for x in (select partition_name, high_value, blocks
from all_tab_partitions
where table_owner=v_owner and table_name=v_table)
loop if substr(x.high_value,1,7)='TO_DATE' then
v_texto:=substr(x.high_value,11,19); v_mascara:=substr(x.high_value,34,22); -- dbms_output.put_line(' fecha '||v_texto||' mascara '||v_mascara); if to_date(v_texto,v_mascara)<v_date then dbms_output.put_line('alter table '||v_owner||'.'|| v_table||' '||var_operation||' partition '||x.partition_name||'; --> before '||v_date); v_size:=v_size+nvl(x.blocks*8,0); end if; elsif substr(x.high_value,1,9)='TIMESTAMP' then v_texto:=substr(x.high_value,12,19); -- dbms_output.put_line(' fecha '||v_texto); if to_date(v_texto,'SYYYY-MM-DD HH24:MI:SS')<v_date then dbms_output.put_line('alter table '||v_owner||'.'|| v_table||' '||var_operation||' partition '||x.partition_name||'; --> before '||v_date); v_size:=v_size+nvl(x.blocks*8,0); end if; end if; end loop; for x in (select partition_name, subpartition_name, high_value, blocks from all_tab_subpartitions where table_owner=v_owner and table_name=v_table) loop if substr(x.high_value,1,7)='TO_DATE' then v_texto:=substr(x.high_value,11,19); v_mascara:=substr(x.high_value,34,22); -- dbms_output.put_line(' fecha '||v_texto||' mascara '||v_mascara); if to_date(v_texto,v_mascara)<v_date then dbms_output.put_line('alter table '||v_owner||'.'||v_table||' ' ||var_operation||' subpartition '||x.subpartition_name||'; --> before '||v_date); v_size:=v_size+nvl(x.blocks*8,0); end if; elsif substr(x.high_value,1,9)='TIMESTAMP' then v_texto:=substr(x.high_value,12,19); -- dbms_output.put_line(' fecha '||v_texto); if to_date(v_texto,'SYYYY-MM-DD HH24:MI:SS')<v_date then dbms_output.put_line('alter table '||v_owner||'.'||v_table ||' '||var_operation||' subpartition '||x.subpartition_name||'; --> before '||v_date); v_size:=v_size+nvl(x.blocks*8,0); end if; end if; end loop; dbms_output.put_line('-- The amount of '||to_char(round(v_size/1024)) ||' MB will be released.'); exception when no_data_found then dbms_output.put_line('Table '||v_owner||'.'||v_table|| ' is not partitioned or doesn''t exist in schema.'); END; /