Como particionar una tabla en linea?

Supongamos que tenemos una tabla, esta puede estar o no particionada y la idea en este caso puntual es volver a particionarla por mes con creación de particiones en forma automática. Para esta tarea vamos a utilizar el pakage DBMS_REDEFINITION.

paso 1.

El primer paso es chequear si la tabla se puede redefinir online, si la salida del script mostrado debajo no da ningún error, entonces podemos hacer uso del packages DBMS_REDEFINITION.

DECLARE
ndummy NUMBER;
begin
DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA','TABLA_ORIGINAL');
EXCEPTION
WHEN OTHERS THENRAISE;
end;
/

Paso 2.

Creamos la tabla temporal con la nueva estructura, en mi caso sólo la particioné por otro campó y ademas lo hice por mes utilizando la clausula "INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))" provista en 11g la cual hace que las partiones se creen en forma automática.

NOTA:
- Para no generar archive's cambiar a modo NOLOGGING tanto la definición de la tabla como el clob.
- Crear las particiones necesarias para alojar la tabla completa.
- Hay que asegurarnos de tener espacio en el tablespace, mínimo el tamaño de la tabla original y un poco más.
- Debe tener la misma PK que la Original.
- En este punto no se debe crear ningún indice ni constraint ni trigger que no sea la PK.
- La tabla debe ser creada con los cambios deseados. - La tabla temporal debe ser creada con esos cambios.

--prompt creo la tabla temporal

CREATE TABLE SCHEMA.DOCUMENTOS_TEMP

(

IDNS_DOCUMENTO NUMBER NOT NULL,

SD_ANIO NUMBER(4) NOT NULL,

SD_ADUANA VARCHAR2(3 CHAR) NOT NULL,

SD_DOCUMENTOTIPO VARCHAR2(4 CHAR) NOT NULL,

SD_DOCUMENTONRO VARCHAR2(6 CHAR) NOT NULL,

SD_LETRA VARCHAR2(1 CHAR) NOT NULL,

SG_DOCUMENTO BLOB NOT NULL,

SD_CAJA VARCHAR2(6 CHAR) NOT NULL,

NS_DESPACHANTE NUMBER NOT NULL,

SD_CUITDESPACHANTE VARCHAR2(11 CHAR) NOT NULL,

SD_DESPACHANTE VARCHAR2(100 CHAR) NOT NULL,

NS_IMPORTADOREXPORTADOR NUMBER NOT NULL,

SD_CUITIMPORTADOREXPORTADOR VARCHAR2(11 CHAR) NOT NULL,

SD_IMPORTADOREXPORTADOR VARCHAR2(100 CHAR) NOT NULL,

H_ALTA DATE DEFAULT SYSDATE NOT NULL,

NC_PAGINAS NUMBER(4) NOT NULL,

NC_PAGINASLOTE NUMBER(4),

NC_BYTESPDF NUMBER,

H_UPD DATE

)

TABLESPACE DATA_CLOB

NOLOGGING

PARTITION BY RANGE (H_ALTA)

INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))

(

PARTITION VALUES LESS THAN (TO_DATE(' 2009-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

NOLOGGING

NOCOMPRESS

TABLESPACE DATA_CLOB

LOB (SG_DOCUMENTO) STORE AS

( TABLESPACE DATA_CLOB

ENABLE STORAGE IN ROW

CHUNK 8192

RETENTION

NOCACHE

NOLOGGING

),

PARTITION VALUES LESS THAN (TO_DATE(' 2009-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

NOLOGGING

NOCOMPRESS

TABLESPACE DATA_CLOB

LOB (SG_DOCUMENTO) STORE AS

( TABLESPACE DATA_CLOB

ENABLE STORAGE IN ROW

CHUNK 8192

RETENTION

NOCACHE

NOLOGGING

),

PARTITION VALUES LESS THAN (TO_DATE(' 2009-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

NOLOGGING

NOCOMPRESS

TABLESPACE DATA_CLOB

LOB (SG_DOCUMENTO) STORE AS

( TABLESPACE DATA_CLOB

ENABLE STORAGE IN ROW

CHUNK 8192

RETENTION

NOCACHE

NOLOGGING

),

PARTITION VALUES LESS THAN (TO_DATE(' 2009-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

NOLOGGING

NOCOMPRESS

TABLESPACE DATA_CLOB

LOB (SG_DOCUMENTO) STORE AS

( TABLESPACE DATA_CLOB

ENABLE STORAGE IN ROW

CHUNK 8192

RETENTION

NOCACHE

NOLOGGING

)

)

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

Paso3.

Asociamos la tabla temporal con la tabla a redefinir.

prompt Iniciar el proceso de redefinición

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE ('SCHEMA','DOCUMENTOS','DOCUMENTOS_TEMP',

'IDNS_DOCUMENTO IDNS_DOCUMENTO,

SD_ANIO SD_ANIO,

SD_ADUANA SD_ADUANA,

SD_DOCUMENTOTIPO SD_DOCUMENTOTIPO,

SD_DOCUMENTONRO SD_DOCUMENTONRO,

SD_LETRA SD_LETRA, SG_DOCUMENTO

SG_DOCUMENTO, SD_CAJA SD_CAJA,

NS_DESPACHANTE NS_DESPACHANTE,

SD_CUITDESPACHANTE SD_CUITDESPACHANTE,

SD_DESPACHANTE SD_DESPACHANTE,

NS_IMPORTADOREXPORTADOR NS_IMPORTADOREXPORTADOR, SD_CUITIMPORTADOREXPORTADOR SD_CUITIMPORTADOREXPORTADOR, SD_IMPORTADOREXPORTADOR SD_IMPORTADOREXPORTADOR,

H_ALTA H_ALTA,

NC_PAGINAS NC_PAGINAS,

NC_PAGINASLOTE NC_PAGINASLOTE,

NC_BYTESPDF NC_BYTESPDF,

H_UPD H_UPD',

dbms_redefinition.cons_use_pk);
END;
/
Paso4.

Copia las dependencias de la tabla original a la tabla temporal.

prompt Copiar el objeto dependiente

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCHEMA', 'DOCUMENTOS','DOCUMENTOS_TEMP', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

Paso5.

Hacemos un chequeo de errores.
prompt Consultar la vista DBA_REDEFINITION_ERRORS para saber si hubo algún error.

select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
Paso6.
Mantenemos sincronizada la tabla temporal con los datos de la tabla original.

prompt Sincronizar la tabla temporal o intermedia

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SRD', 'DOCUMENTOS','DOCUMENTOS_TEMP');
END;
/

Paso7.

Registra la dependendia de objetos ( indices, trigger o constraint ) y finaliza la redefinición.

prompt Completar la redefinicion

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('SRD', 'DOCUMENTOS','DOCUMENTOS_TEMP');
END;
/
Paso8.

Volver a poner en modo LOGGING a la tabla.

alter table SCHEMA.DOCUMENTOS LOGGING ;
alter table SCHEMA.DOCUMENTOS MODIFY LOB (SG_DOCUMENTO) (CACHE LOGGING);
NOTA:

En caso de dar error y querer realizar rollback de la operacion se puede ejecutar lo siguente

BEGIN dbms_redefinition.abort_redef_table('SRD', 'DOCUMENTOS','DOCUMENTOS_TEMP');
END;
/

Como mover datos de la partición default a una nueva?

Cuando tenemos una tabla particionada, en este caso por un rango de fechas y nos damos cuenta que los registros se están creando en la partición default, la solución es crear una nueva particion con el rango adecuado. Para este caso se puede usar la opción SPLIT la cual crea una nueva partición, en mi caso es la partición DCT_DOC2009.

ALTER TABLE dct_documentos
SPLIT PARTITION DCT_DOCMAX at (TO_DATE(' 2010-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
INTO (PARTITION DCT_DOC2009,
PARTITION DCT_DOCMAX)
UPDATE GLOBAL INDEXES;

Podemos usar la opción UPDATE GLOBAL INDEXES la cual recrea los indices globales de la tabla en cuestión o podriamos eliminar la opción de la claúsula y en un segundo paso recrear los indices con estado UNSUABLE.

Para hacer el reubuild de los indices usamos la siguiente query:

SELECT 'alter index 'owner'.'index_name ' REBUILD;'
FROM dba_indexes
WHERE status='UNUSABLE' ;

Para ver como quedaron las particiones se puede usar el siguiente query:

SELECT partition_name, high_value
FROM user_tab_partitions
WHERE table_name = 'DCT_DOCUMENTOS'
ORDER BY partition_name;

Para confirmar que ahora la partición tiene datos usamos:

SELECT * FROM dct_documentos PARTITION(DCT_DOC2009);

Par confirmar que la particion default en mi caso se llama DCT_DOCMAX, esta query debería no retornar filas.

SELECT * FROM dct_documentos PARTITION(DCT_DOCMAX);

Como calcular el tamaño realmente usado de un segmento

Con este script se puede calcular el tamaño realmente usado por un segmento.


PROMPT
PROMPT SEGMENT SPACE ALLOCATION
PROMPT

ACCEPT own PROMPT "Segment owner: "
ACCEPT obj PROMPT "Segment name: "
ACCEPT typ PROMPT "Segment type ((T)able, (I)ndex or (C)luster) or (L)ob: "

SET HEADING OFF
SELECT ' ' FROM sys.dual;

SET SERVEROUTPUT ON

DECLARE

op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
obj_type VARCHAR2(10);
file_name VARCHAR2(255);
ts_name VARCHAR2(30);

BEGIN

IF UPPER('&typ') = 'T' THEN

obj_type := 'TABLE';

ELSIF UPPER('&typ') = 'I' THEN

obj_type := 'INDEX';

ELSIF UPPER('&typ') = 'C' THEN

obj_type := 'CLUSTER';

ELSIF UPPER('&typ') = 'L' THEN

obj_type := 'LOB';

ELSE
dbms_output.put_line('Invalid object type');
RETURN;
END IF;

dbms_space.unused_space(
UPPER('&own'),
UPPER('&obj'),
obj_type,
op1, op2, op3, op4, op5, op6, op7
);

dbms_output.put_line('Total Blocks = ' || op1);
dbms_output.put_line('Total Bytes = ' || op2);
dbms_output.put_line('Unused Blocks = ' || op3);
dbms_output.put_line('Unused Bytes = ' || op4);

SELECT file_name, tablespace_name
INTO file_name, ts_name
FROM dba_data_files
WHERE file_id = op5;

dbms_output.put_line('Segment Tablespace = ' || ts_name);
dbms_output.put_line('Last Used Extent File ID = ' || op5);
dbms_output.put_line('Last Used Extent File = ' || file_name);
dbms_output.put_line('Last Used Extent Block ID = ' || op6);
dbms_output.put_line('Last Used Block = ' || op7);

END;
/

UNDEFINE obj
UNDEFINE own
UNDEFINE typ

Como reducir el tamaño del tablespace UNDO

El procedimiento que muestro a continuación, muestra los pasos a seguir cuando tenemos que recrear el tablespace UNDO en un ambiente RAC.

Estamos trabajando con un cluster de dos nodos que contienen dos Instancias de bases de datos.

Los tablespaces de UNDO de una de las instancias han crecido más de la cuenta y nos han llenado el disco.

NOTA:Una base de datos solo puede tener un tablespace de UNDO activo en un momento determinado.

En un entorno RAC, tenemos un tablespace de UNDO para cada instancia.

Un tablespace no se puede reducir de tamaño. Lo que voy a hacer es eliminarlo y volver a crearlo con un tamaño adecuado, añadiendo además un límite a su crecimiento. El problema que tengo ahora es que estoy tratando con un tablespace de UNDO, el cual tiene un tratamiento especial.

No se puede poner offline y menos aún borrarlo mientras tenga segmentos activos. De modo que, lo primero es crear un nuevo tablespace de undo, las instrucciones que indico a continuación las ejecuto como usuario SYS:


Paso 1.

CREATE UNDO TABLESPACE UNDO_TEMPORAL DATAFILE SIZE 100M;

Ahora en la primera instancia del cluster (el orden da igual) se indica el parámetro que indica el tablespace de undo por defecto.

En este caso, se trata de un parámetro dinámico, y por lo tanto no hace falta parar la instancia para poder modificarlo:

ALTER SYSTEM SET UNDO_TABLESPACE='UNDO_TEMPORAL' SID='DB1';

Si ahora reviso los parámetros que hacen referencia al UNDO en esta instancia, me encuentro con lo siguiente:

Paso2.

sqlplus> show parameter undo

NAME TYPE VALUE
—————— ———– ——————–
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO_TEMPORAL

En teoría, ya puedo borrar el tablespace de undo que me estaba dando el problema.

Si se diera la condición de que todos los segmentos estuvieran en estado offline, no haría ni siquiera falta poner offline el tablespace; le podría hacer un drop sin más complicaciones. El problema viene cuando hay algún segmento que no está offline; teniendo claro que no quiero hacer rollback, ni recuperar ningún tipo de transacción, y que puedo prescindir totalmente de estos datos, lo que voy a hacer es forzar el estado offline de los segmentos que no lo estén para poder borrar el tablespace de forma limpia. ¿Cómo compruebo esto? Con la siguiente select:


Paso3.

SELECT SEGMENT_NAME,TABLESPACE_NAME,STATUS
FROM DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME='UNDOTBS1';

Que me dará un resultado como el que sigue:

SEGMENT_NAME TABLESPACE_NAME STATUS
——————– ————————- —————-
_SYSSMU13$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU14$ UNDOTBS1 OFFLINE
_SYSSMU15$ UNDOTBS1 OFFLINE
_SYSSMU16$ UNDOTBS1 OFFLINE
_SYSSMU17$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU18$ UNDOTBS1 OFFLINE
_SYSSMU19$ UNDOTBS1 OFFLINE
_SYSSMU20$ UNDOTBS1 OFFLINE

Cualquier status diferente de OFFLINE necesitará que le apliquemos el procedimiento que a continuación describo.

Paso4.

Lo siguiente es bajar la instancia; por suerte, estoy en un entorno RAC, por lo tanto no va a haber pérdida de servicio.

Como tengo que arrancar con un PFILE modificado a partir del SPFILE (con el que estoy trabajando), voy a crear un PFILE antes de bajar la instancia:


CREATE PFILE='/tmp/pfile.ora' from SPFILE;

Ahora bajo la instancia y edito el PFILE. Tengo que modificar el parámetro UNDO_MANAGEMENT al valor 'MANUAL' y añadir un parámetro oculto de ORACLE

para poder forzar el OFFLINE de los segmentos UNDO que no lo estén. Para el caso que describo, y según la anterior SELECT, yo insertaría esta línea

en mi PFILE:

*._offline_rollback_segments="_SYSSMU13$,_SYSSMU17quot;

Lo siguiente es levanter la instancia con el PFILE modificado:

STARTUP PFILE='/tmp/pfile.ora'

Si se ejecuta la anterior select, el resultado va a ser el mismo. Pero ahora ya se puede actuar sobre esos segmentos. En primer lugar:

DROP ROLLBACK SEGMENT '_SYSSMU13;

Que dará un resultado como este:

ERROR en línea 1:
ORA-02175: nombre de segmento de rollback no válido

Obviamos el error y repetimos el DROP sobre el otro segmento. Después de eso, ya se puede borrar el segmento de undo:


DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Una vez borrado este tablespace, vuelvo a parar la instancia y arranco con el SPFILE (hago un startup sin ningún parámetro). En este momento,

ya puedo crear el nuevo tablespace de undo con los parámetros deseados:

CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE SIZE 500M AUTOEXTEND ON NEXT 128M MAXSIZE 1G;

Igual que he hecho antes, cambio el tablespace de UNDO por defecto, substituyendo UNDO_TEMPORAL por UNDOTBS1.

ALTER SYSTEM SET UNDO_TABLESPACE='UNDOTBS1' SID=’DB1’;

Finalmente, solo queda repetir el proceso con la otra instancia. Cuando se concluya, se puede borrar el tablespace de UNDO que se ha creado para solucionar el problema.

Restore completo base de datos con RMAN.

1- Suponiendo que disponemos de un backup a disco en el directorio /pruebarman
2. Iniciar una sesión desde un terminal del servidor como usuario oracle
3. Conectarse al RMAN:

$ rman target / nocatalog

4. Iniciar la base de datos sin montarla y restaurar el pfile utilizando el autobackup:

RMAN> set DBID=248804095;
startup nomount;
run
{
restore spfile to pfile '/oracle/product/10.2.0/dbs/initBAIHONT.ora' from '/pruebarman/c-248804095-20090130-04';
shutdown abort;
}

5. Iniciar la base de datos utilizando el pfile restaurado.
RMAN> set DBID=248804095;
RMAN> startup force nomount pfile = '/oracle/product/10.2.0/dbs/initBAIHONT.ora';

6. Restaurar los archivos de control e la base de datos (control files)
run
{
restore controlfile from '/pruebarman/c-248804095-20090130-04';
alter database mount;
}

7. Restaurar los archivos de base de datos (data files) y restaurar la data al día del backup utilizado.
run
{
restore database;
switch datafile all;
recover database;
}

8. Inicializar la base de datos para un normal funcionamiento.
RMAN> alter database open resetlogs;

9. Comprobar el funcionamiento de la base de datos restaurada, para ello se realizaran prueba de conexión a la base de datos:
$ sqlplus / as sysdba
SQL> connect system;
SQL> connect [usuario diferente al sys o system];