Revisado por Francisco Riccio
Introducción
A lo largo de los años, la base de datos Oracle ha ido evolucionando y ofreciendo nuevas opciones para una mejor administración. Las funcionalidades con mayor impacto para la labor de los DBAs han sido las operaciones Online. Gracias a las actividades online, podemos evitar largas ventanas de mantenimiento sobre ambientes productivos en horarios nocturnos o de fin de semana.
Ya desde la versión Oracle 9i tuvimos acceso a operaciones online como:
- CREATE INDEX.
- REBUILD INDEX.
En Oracle 12c Release 1, Oracle continúa mejorando las opciones online y añade nuevas operaciones de gran ayuda como:
- DROP INDEX.
- DROP CONSTRAINT.
- ALTER INDEX UNUSABLE.
- SET COLUMN UNUSED.
- MOVE PARTITION & SUBPARTITION.
Sin embargo, es a partir de Oracle 12c Release 2 donde se agregan las operaciones online de mayor impacto como:
- MOVE TABLE.
- SPLIT & MERGE PARTITION.
- CONVERT NON-PARTITION TABLE TO PARTITION TABLE.
En este artículo quiero explicar en detalle las operaciones online para Oracle 12cR2 y 18c; los casos de uso más comunes y algunas restricciones para cada tipo de operación.
MOVE TABLE ONLINE.
El comando nos permite mover una tabla de manera online agregando la opción ONLINE sobre ALTER TABLE.
Lo mejor de esta operación es que no afecta el estado de los índices; por lo que es posible realizar desfragmentación de tablas sin la necesidad de una ventana de mantenimiento.
Para nuestro ejemplo, creamos una tabla y le agregamos una gran cantidad de datos. Posteriormente creamos su Primary Key y un índice sobre el campo código.
SQL> create table music.BEATLES (id number, codigo number, fec_registro date, miembro
varchar2(42)) tablespace USERS;
Table created.
SQL> insert into music.BEATLES (
select rownum, mod(rownum,1000), sysdate - mod(rownum,100),
decode(mod(rownum,10),1,'PAUL',2,'JOHN',3,'RINGO',4,'GEORGE',5,'STUART',6,'PETE',7,'NORMAN',
8,'TOMMY',9,'JIMMIE','THE BEATLES')
from dual connect by level <= 1700000);
1700000 rows created.
SQL> commit;
Commit complete.SQL> alter table music.BEATLES ADD CONSTRAINT PK_BEATLES PRIMARY KEY (id);
Table altered.
SQL> create index music.IDX1_BEATLES on music.BEATLES (codigo) tablespace USERS;
Index created.Finalmente verificamos las características de la tabla y sus índices.
SQL> select owner, table_name, tablespace_name from dba_tables where table_name='BEATLES';
OWNER TABLE_NAME TABLESPACE_NAME COMPRESS
------- -------------- ---------------- --------
MUSIC BEATLES USERS DISABLED
SQL> select OWNER,INDEX_NAME,STATUS, CLUSTERING_FACTOR, NUM_ROWS, TABLESPACE_NAME from
dba_indexes where table_name='BEATLES';
OWNER INDEX_NAME STATUS CLUSTERING_FACTOR NUM_ROWS TABLESPACE_NAME
------- -------------- -------- ----------------- ---------- ----------------
MUSIC PK_BEATLES VALID 6800 1700000 USERS
MUSIC IDX1_BEATLES VALID 1700000 1700000 USERSProcedemos a realizar el movimiento de la tabla al tablespace DATA y verificamos que los índices se mantienen validos después del movimiento.
SQL> ALTER TABLE music.BEATLES MOVE ONLINE tablespace DATA;
Table altered.
SQL> select owner, table_name, tablespace_name from dba_tables where table_name='BEATLES';
OWNER TABLE_NAME TABLESPACE_NAME
-------- -------------- ----------------
MUSIC BEATLES DATA
SQL> select OWNER,INDEX_NAME,STATUS, CLUSTERING_FACTOR, NUM_ROWS, TABLESPACE_NAME from
dba_indexes where table_name='BEATLES';
OWNER INDEX_NAME STATUS CLUSTERING_FACTOR NUM_ROWS TABLESPACE_NAME
-------- -------------- -------- ----------------- ---------- --------
MUSIC PK_BEATLES VALID 13524 1700000 USERS
MUSIC IDX1_BEATLES VALID 1579897 1700000 USERSPodemos utilizar el movimiento de una tabla de manera Online para activar la compresión e incluir la reconstrucción de los índices (sobre diferentes tablespaces). Todo ejecutado desde el mismo comando con la opción UPDATE INDEXES.
SQL> ALTER TABLE music.BEATLES MOVE ONLINE COMPRESS tablespace DATA
UPDATE INDEXES (
music.PK_BEATLES tablespace DATA,
music.IDX1_BEATLES tablespace USERS);
Table altered.
SQL> select owner, table_name, tablespace_name,COMPRESSION from dba_tables where
table_name='BEATLES';
OWNER TABLE_NAME TABLESPACE_NAME COMPRESS
--------- ------------- ------------------ --------
MUSIC BEATLES DATA ENABLED
SQL> select OWNER,INDEX_NAME,STATUS, CLUSTERING_FACTOR, NUM_ROWS, TABLESPACE_NAME from
dba_indexes where table_name='BEATLES';
OWNER INDEX_NAME STATUS CLUSTERING_FACTOR NUM_ROWS TABLESPACE_NAME
-------- -------------- -------- ----------------- ---------- ---------------
MUSIC PK_BEATLES VALID 10846 1700000 DATA
MUSIC IDX1_BEATLES VALID 1386859 1700000 USERSFinalmente podemos reorganizar la tabla de tal manera que valor de Clustering Factor se reduzca para columnas indexadas (diferentes al Primary Key). Utilizamos el concepto de CLUSTERING ATTRIBUTE.
SQL> alter table music.BEATLES ADD CLUSTERING by linear order(codigo) without
materialized zonemap;
Table altered.
SQL> alter table music.BEATLES MOVE ONLINE NOCOMPRESS tablespace DATA UPDATE INDEXES;
Table altered.
SQL> select OWNER,INDEX_NAME,STATUS, CLUSTERING_FACTOR, NUM_ROWS, TABLESPACE_NAME from
dba_indexes where table_name='BEATLES';
OWNER INDEX_NAME STATUS CLUSTERING_FACTOR NUM_ROWS TABLESPACE_NAME
-------- -------------- -------- ----------------- ---------- ---------------
MUSIC PK_BEATLES VALID 1687292 1700000 DATA
MUSIC IDX1_BEATLES VALID 6140 1700000 USERSEs importante indicar que el movimiento de tablas Online tiene las siguientes restricciones:
- No es posible ejecutar este comando sobre tablas particionada de tipo IOT y tablas que contengan índices de tipo Domain.
- No se puede realizar DML con paralelismo e inserciones masivas (direct path) mientras se ejecuta el movimiento Online.
- No es posible ejecutar este comando para tablas de tipo IOT con columnas LOB, VARRAY o de tipo User-Defined.
CONVERTIR NON-PARTITIONED TABLE TO PARTITIONED TABLE.
En versiones anteriores es posible convertir una tabla no particionada a particionada con comandos como Exchange Partition o DBMS_Redefinition, sin embargo, estos métodos pueden llegar a ser complejos y con validaciones previas a la conversión.
A partir de Oracle 12cR2 es posible convertir una tabla no particionada a particionada a través de un solo comando y de manera Online. Podemos definir diferentes características para cada partición que se vaya a crear.
SQL> alter table music.BEATLES MODIFY PARTITION BY RANGE (fec_registro) (
partition T1_07_2018 values less than (TO_DATE('01-AUG-2018','DD-MON-YY
YY')) tablespace USERS COMPRESS,
partition T2_08_2018 values less than (TO_DATE('01-SEP-2018','DD-MON-YY
YY')) tablespace DATA RESULT_CACHE (mode default),
partition T3_09_2018 values less than (TO_DATE('01-OCT-2018','DD-MON-YY
YY')) tablespace USERS,
partition T4_10_2018 values less than (TO_DATE('01-NOV-2018','DD-MON-YY
YY')) tablespace DATA
) ONLINE;
Table altered.Si la tabla contiene índices, es posible definir el particionamiento de cada índice con la opción UPDATE INDEXES. Si esta opción no se define (como en el comando anterior) los índices son redefinidos de la siguiente manera:
- Índices NonUnique se convierten en LOCAL INDEXES.
- Índices Unique y que forman parte del rango de partición se convierten en LOCAL INDEXES.
- Índices Unique y que no forman parte del rango de partición se convierten en GLOBAL INDEXES.
Para nuestro ejemplo vamos a definir diferentes tipos de particionamiento por cada índice.
SQL> alter table music.BEATLES MODIFY PARTITION BY RANGE (fec_registro) (
partition T1_07_2018 values less than (TO_DATE('01-AUG-2018','DD-MON-YY
YY')) tablespace USERS READ ONLY,
partition T2_08_2018 values less than (TO_DATE('01-SEP-2018','DD-MON-YY
YY')) tablespace DATA READ ONLY,
partition T3_09_2018 values less than (TO_DATE('01-OCT-2018','DD-MON-YY
YY')) tablespace USERS,
partition T4_10_2018 values less than (TO_DATE('01-NOV-2018','DD-MON-YY
YY')) tablespace DATA
) ONLINE
UPDATE INDEXES(
music.PK_BEATLES GLOBAL PARTITION BY HASH (ID) PARTITIONS 4,
music.IDX1_BEATLES LOCAL
);
Table altered.Finalmente es posible tomar solo una parte de la información de la tabla para el particionamiento. Usamos la opción INCLUDING ROWS. Esta opción borra los registros que no cumplen con la condición definida.
Para nuestro ejemplo vamos a particionar la tabla, pero solo incluimos los valores a partir de agosto 2018. Se puede observar que los valores antes de dicha fecha son eliminados de la tabla.
SQL> alter table music.BEATLES MODIFY PARTITION BY RANGE (fec_registro) (
partition T2_08_2018 values less than (TO_DATE('01-SEP-2018','DD-MON-YY
YY')) tablespace DATA COMPRESS,
partition T3_09_2018 values less than (TO_DATE('01-OCT-2018','DD-MON-YY
YY')) tablespace USERS,
partition T4_10_2018 values less than (TO_DATE('01-NOV-2018','DD-MON-YY
YY')) tablespace USERS
) ONLINE
UPDATE INDEXES(
music.PK_BEATLES GLOBAL,
music.IDX1_BEATLES LOCAL
)
INCLUDING ROWS WHERE fec_registro >= TO_DATE('01-AUG-2018','DD-MON-YYYY');
Table altered.
SQL> select fec_registro, count(1)
from music.BEATLES
where fec_registro < TO_DATE('01-AUG-2018','DD-MON-YYYY')
group by fec_registro;
no rows selectedLa conversión a tabla particionada de manera Online también tiene algunas restricciones como:
- No es posible ejecutar este comando sobre tablas de tipo IOT.
- No se pude utilizar el comando sobre tablas que poseen índices de tipo Domain.
- No es posible particionar una tabla hija de tipo Reference-Partitioned de manera Online.
MERGE & SPLIT ONLINE
Una vez que se tiene una tabla particionada, es posible realizar tareas de mantenimiento como MERGE o SPLIT de manera online.
Estas operaciones requieren de un bloqueo de tipo X DML sobre las particiones involucradas, es por ello que ambas operaciones son completadas sólo cuando todos los DML, iniciados antes de la operación online, finalizan.
Verificamos las particiones de nuestra tabla.
SQL> select TABLE_OWNER, TABLE_NAME, PARTITION_NAME,PARTITION_POSITION, NUM_ROWS from
dba_tab_partitions where TABLE_NAME='BEATLES';
TABLE_OWNE TABLE_NAME PARTITION_ PARTITION_POSITION NUM_ROWS
---------- -------------------- ---------- ------------------ ----------
MUSIC BEATLES T1_07_2018 1 510000
MUSIC BEATLES T2_08_2018 2 527000
MUSIC BEATLES T3_09_2018 3 510000
MUSIC BEATLES T4_10_2018 4 153000Desde la versión Oracle 12cR1 es posible realizar SPLIT y MERGE de múltiples particiones. A partir de Oracle 12cR2 es posible realizar dichas operaciones múltiples de manera Online.
La operación SPLIT se puede realizar Online a partir de Oracle 12cR2, mientras que la operación MERGE solo se puede realizar de manera Online a partir de la versión 18c.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> alter table music.BEATLES
MERGE PARTITIONS T1_07_2018, T2_08_2018 INTO PARTITION T12_07_08_2018
ONLINE UPDATE INDEXES;
ONLINE UPDATE INDEXES
*
ERROR at line 3:
ORA-14020: this physical attribute may not be specified for a table partition
SQL> alter table music.BEATLES
MERGE PARTITIONS T1_07_2018, T2_08_2018 INTO PARTITION T12_07_08_2018;
Table altered.
SQL> alter table music.BEATLES SPLIT PARTITION T12_07_08_2018 at (TO_DATE(
'01-AUG-2018','DD-MON-YYYY'))
INTO (PARTITION T1_07_2018, PARTITION T2_08_2018)
ONLINE UPDATE INDEXES;
Table altered.Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
SQL> alter table music.BEATLES
MERGE PARTITIONS T1_07_2018, T2_08_2018 INTO PARTITION T12_07_08_2018
ONLINE UPDATE INDEXES;
Table altered.EXTRA: MOVE DATAFILE ONLINE
La opción de mover datafiles de manera Online es una funcionalidad presente desde la versión Oracle 12c Release 1, sin embargo, me pareció importante agregarla como una funcionalidad extra debido su importancia en tareas de mantenimiento como:
- Migración de Filesystem a ASM o viceversa.
- Reorganización de datafiles en diferentes Filesystems o Diskgroups.
SQL> alter database move datafile '+DATA/APEXDB/DATAFILE/sysaux.304.977269941' to
'/u02/oradata/apexdb/sysaux_01.dbf';
Database altered.
SQL> alter database move datafile '/u02/oradata/apexdb/sysaux_01.dbf' to '+DATA';
Database altered.- Renombramiento de datafiles.
SQL> alter database move datafile '+DATA/APEXDB/DATAFILE/data.263.989013263' to
'+DATA/APEXDB/DATAFILE/DATA_01.dbf';
Database altered.Conclusión
En este articulo hemos podido explicar las diferentes operaciones online en cada una de las versiones de base de datos Oracle. Sin embargo, es importante resaltar que las operaciones Online a partir de Oracle 12cR2 tienen un impacto muy importante en las tareas de mantenimiento que los DBAs realizamos en ambientes Críticos que no permiten indisponibilidad.
Ing. Jorge Zorrilla. Es un especialista IT en tecnologías Oracle e instructor de cursos oficiales de certificación Oracle. Con más de 9 años de experiencia en soluciones con tecnología Oracle como Alta Disponibilidad, Continuidad de negocios y Modernización de la infraestructura. Fue uno de los primeros especialistas en Latinoamérica en obtener la certificación Oracle Maximum Availability 12c.
En la actualidad Jorge Zorrilla se dedica a mantener relaciones estratégicas con sus clientes en Perú mediante su empresa IDB Consulting.
Este artículo ha sido revisado por el equipo de productos Oracle y se encuentra en cumplimiento de las normas y prácticas para el uso de los productos Oracle.