Como cada año, toca hacer limpieza en las bases de datos a lo Marie Kondo.
Los administradores de bases de datos tenemos que historificar, comprimir o eliminar las particiones antiguas que ya no son necesarias. Evidentemente, las tablas particionadas (y sus índices locales) son el mejor sitio para borrar información que ha quedado antigua. Muchas regulaciones nos obligan a mantener los datos durante un cierto número de años, pero pasado este tiempo, esa información ocupa espacio y, en algunos casos, puede ser una molestia en coste si Oracle resuelve accesos a estas particiones obsoletas.
En cada empresa he visto un método de historificación distinto. En muchos casos era manual a principio de año, creando nuevas particiones y eliminando las del año que ya no era necesario.
Por la parte de añadir particiones, con la cláusula INTERVAL ya no es necesario realizar esta tarea, pues en una tabla creada con la siguiente definición solamente necesita la definición del nombre de una primera partición que tendrá todos los valores anteriores a esa fecha, y al insertar datos que corresponderían a particiones fuera del rango, Oracle va a crear automáticamente esas particiones por mes::
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
Claro, identificar estas particiones por el nombre puede ser complicado, y la recomendación sería renombrarlas con un ALTER TABLE.
SQL> ALTER TABLE TEST_PARTICIONADO_INTERVALO RENAME PARTITION sys_p5602 TO P1_TEST_PARTICIONADO_SEPT_2019;
Table altered.
En fin, que estamos hablando sólo de una tabla pero, ¿Qué ocurre cuando tienes muchas? ¿Qué pasa si los rangos son distintos (meses, días, trimestres,…)? ¿qué ocurre con el particionamiento compuesto en el que hay que revisar también subparticiones? ¿y si los campos de fecha no son solo DATE, sino también TIMESTAMP?
Pues, aunque pudiera parecer sencillo, hay una dificultad añadida: el campo HIGH_VALUE de la vista DBA_TAB_PARTITIONS y DBA_TAB_SUBPARTITIONS es de tipo LONG.
Los campos LONG, qué tiempos aquellos, en Oracle 7.
Oracle en la versión 8 recomendó que dejaran de usarse los campos de tipo LONG, a favor de los LOB, pero si hablamos del diccionario, eso ya es otra cosa. Numerosas tablas contienen LONG y lo cierto es que cada vez su versatilidad en SQL está más limitada.
Por ejemplo, no puedes hacer SUBSTR() de un campo LONG en SQL.
Tampoco puedes hacer un CAST para convertirlo a 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
Vaya ¿Y qué tal convertirlo a LOB con la función TO_LOB?
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
Bien, la solución pasa por implementar un procedure en PL/SQL que genere los comandos (y para DBAs valientes, pueden cambiar el DBMS_OUTPUT por un EXECUTE IMMEDIATE) y listo. En PL/SQL el tipo de dato LONG es mucho más versátil ya que no tiene las limitaciones de la implementación física vinculada a los LONG, VARCHAR2, CHAR, etc.
Es decir, desde PL/SQL es posible utilizar la función SUBSTR con campos LONG que devuelvan un VARCHAR2, y con ésto componer la sintaxis de mantenimiento de particiones.
El siguiente procedure hace lo siguiente: dado un usuario, una tabla, y una fecha específica, busca aquellas particiones o subparticiones anteriores a esa fecha y mediante un simple bucle FOR, genera los comandos de ALTER TABLE DROP|TRUNCATE PARTITION|SUBPARTITION que correspondan.
Siendo prudentes, y para no causar daños, he implementado el procedure para que genere la salida en DBMS_OUTPUT, y ya cada cual que ejecute a mano lo que considere. Por supuesto en este procedimiento se puede sustituir DBMS_OUTPUT por EXECUTE IMMEDIATE y que el mismo procedimiento realice la tarea de mantenimiento.
Este código está en el repositorio de GitHub de Café Database, y eres libre de crear un fork, de mejorarlo, optimizarlo, recomendarlo y compartirlo.
https://github.com/CafeDatabase/Old-Partition-Management
Hala! A disfrutarlo!. (Por supuesto, el código se ve mucho mejor en el repositorio de GitHub!)
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; /