Eliminación masiva de registros en oracle
No se que pasa, pero he tomado este ejemplo de muchas páginas de internet de eliminación masiva y no me funciona, lo pruebo con una tabla de 800 registros y no me elimina nada... Ya le coloque privilegios de ejecion sobre el paquete dbms al usuario. Pero aun así no.. Por favor.. Cualquier ayudita gracias
CREATE OR REPLACE PROCEDURE DeleteMasivo( NombreTabla IN VARCHAR2,
CondicionWhere IN VARCHAR2 DEFAULT NULL,
RegistrosCommit IN NUMBER DEFAULT 1000 ) IS
idSelect INTEGER;
idDelete INTEGER;
execSelect INTEGER;
execDelete INTEGER;
SQLCursor VARCHAR2(2000);
ROWIDDelete ROWID;
Begin
--
-- Se prepara un cursor dinánico para seleccionar los ROWIDs
-- De los registros a borrar.
--
SQLCursor := 'SELECT rowid FROM ' || NombreTabla || ' ' || CondicionWhere;
idSelect := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(idSelect, SQLCursor, DBMS_SQL.v7);
DBMS_SQL.DEFINE_COLUMN_ROWID(idSelect, 1, ROWIDDelete);
execSelect := DBMS_SQL.EXECUTE(idSelect);
--
-- Se prepara el cursor para borrar los registros
--
idDelete := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(idDelete, 'DELETE FROM ' || NombreTabla ||
' WHERE ROWID = :rowid_delete', DBMS_SQL.v7);
LOOP
IF DBMS_SQL.FETCH_ROWS(idSelect) = 0 THEN
--
-- Obtiene los ROWIDs de las filas a borrar
--
DBMS_SQL.COLUMN_VALUE(idSelect, 1, ROWIDDelete);
DBMS_SQL.BIND_VARIABLE(idSelect, 'rowid_delete', ROWIDDelete);
--
-- Ejecuta el delete para las filas obtenidas
---
execDelete := DBMS_SQL.EXECUTE(idDelete);
--
-- Hace un commit cada n filas.
-- n se especifica en el parámetro "RegistrosCommit"
--
IF MOD(DBMS_SQL.LAST_ROW_COUNT, RegistrosCommit) = 0 THEN
COMMIT;
END IF;
ELSE
EXIT;
END IF;
END LOOP;
--
-- Hace commit otra vez, para validar el ultimo grupo de registros
--
COMMIT;
DBMS_SQL.CLOSE_CURSOR(idSelect);
DBMS_SQL.CLOSE_CURSOR(idDelete);
END DeleteMasivo;
P.D. Amigos, utilizo oracle 8i, los parametros que le paso es nombre tabla: log_table
where code>1
Y cantidad 100 de registros .. La tabla que tengo que eliminar los registros tiene casi 8.000.000
CREATE OR REPLACE PROCEDURE DeleteMasivo( NombreTabla IN VARCHAR2,
CondicionWhere IN VARCHAR2 DEFAULT NULL,
RegistrosCommit IN NUMBER DEFAULT 1000 ) IS
idSelect INTEGER;
idDelete INTEGER;
execSelect INTEGER;
execDelete INTEGER;
SQLCursor VARCHAR2(2000);
ROWIDDelete ROWID;
Begin
--
-- Se prepara un cursor dinánico para seleccionar los ROWIDs
-- De los registros a borrar.
--
SQLCursor := 'SELECT rowid FROM ' || NombreTabla || ' ' || CondicionWhere;
idSelect := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(idSelect, SQLCursor, DBMS_SQL.v7);
DBMS_SQL.DEFINE_COLUMN_ROWID(idSelect, 1, ROWIDDelete);
execSelect := DBMS_SQL.EXECUTE(idSelect);
--
-- Se prepara el cursor para borrar los registros
--
idDelete := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(idDelete, 'DELETE FROM ' || NombreTabla ||
' WHERE ROWID = :rowid_delete', DBMS_SQL.v7);
LOOP
IF DBMS_SQL.FETCH_ROWS(idSelect) = 0 THEN
--
-- Obtiene los ROWIDs de las filas a borrar
--
DBMS_SQL.COLUMN_VALUE(idSelect, 1, ROWIDDelete);
DBMS_SQL.BIND_VARIABLE(idSelect, 'rowid_delete', ROWIDDelete);
--
-- Ejecuta el delete para las filas obtenidas
---
execDelete := DBMS_SQL.EXECUTE(idDelete);
--
-- Hace un commit cada n filas.
-- n se especifica en el parámetro "RegistrosCommit"
--
IF MOD(DBMS_SQL.LAST_ROW_COUNT, RegistrosCommit) = 0 THEN
COMMIT;
END IF;
ELSE
EXIT;
END IF;
END LOOP;
--
-- Hace commit otra vez, para validar el ultimo grupo de registros
--
COMMIT;
DBMS_SQL.CLOSE_CURSOR(idSelect);
DBMS_SQL.CLOSE_CURSOR(idDelete);
END DeleteMasivo;
P.D. Amigos, utilizo oracle 8i, los parametros que le paso es nombre tabla: log_table
where code>1
Y cantidad 100 de registros .. La tabla que tengo que eliminar los registros tiene casi 8.000.000
2 Respuestas
Respuesta de antipauli
1
Ahí va:
Create or replace procedure deletemasivo( nombretabla in varchar2,
condicionwhere in varchar2 default null,
registroscommit in number default 1000 ) is
idselect integer;
iddelete integer;
execselect integer;
execdelete integer;
sqlcursor varchar2(2000);
rowiddelete rowid;
begin
--
-- Se prepara un cursor dinánico para seleccionar los rowids
-- De los registros a borrar.
--
sqlcursor := 'select rowid from ' || nombretabla || ' ' || condicionwhere;
idselect := dbms_sql.open_cursor;
dbms_sql.parse(idselect, sqlcursor, dbms_sql.v7);
dbms_sql.define_column_rowid(idselect, 1, rowiddelete);
execselect := dbms_sql.execute(idselect);
--
-- Se prepara el cursor para borrar los registros
--
iddelete := dbms_sql.open_cursor;
dbms_sql.parse(iddelete, 'delete from ' || nombretabla ||
' where rowid = :rowid_delete', dbms_sql.v7);
loop
if dbms_sql.fetch_rows(idselect) > 0 then
--
-- obtiene los rowids de las filas a borrar
--
dbms_sql.column_value(idselect, 1, rowiddelete);
dbms_sql.bind_variable(iddelete, 'rowid_delete', rowiddelete);
--
-- ejecuta el delete para las filas obtenidas
---
execdelete := dbms_sql.execute(iddelete);
--
-- Hace un commit cada n filas.
-- N se especifica en el parámetro "registroscommit"
--
if mod(dbms_sql.last_row_count, registroscommit) = 0 then
commit;
end if;
else
exit;
end if;
end loop;
--
-- Hace commit otra vez, para validar el ultimo grupo de registros
--
commit;
dbms_sql.close_cursor(idselect);
dbms_sql.close_cursor(iddelete);
end deletemasivo;
Create or replace procedure deletemasivo( nombretabla in varchar2,
condicionwhere in varchar2 default null,
registroscommit in number default 1000 ) is
idselect integer;
iddelete integer;
execselect integer;
execdelete integer;
sqlcursor varchar2(2000);
rowiddelete rowid;
begin
--
-- Se prepara un cursor dinánico para seleccionar los rowids
-- De los registros a borrar.
--
sqlcursor := 'select rowid from ' || nombretabla || ' ' || condicionwhere;
idselect := dbms_sql.open_cursor;
dbms_sql.parse(idselect, sqlcursor, dbms_sql.v7);
dbms_sql.define_column_rowid(idselect, 1, rowiddelete);
execselect := dbms_sql.execute(idselect);
--
-- Se prepara el cursor para borrar los registros
--
iddelete := dbms_sql.open_cursor;
dbms_sql.parse(iddelete, 'delete from ' || nombretabla ||
' where rowid = :rowid_delete', dbms_sql.v7);
loop
if dbms_sql.fetch_rows(idselect) > 0 then
--
-- obtiene los rowids de las filas a borrar
--
dbms_sql.column_value(idselect, 1, rowiddelete);
dbms_sql.bind_variable(iddelete, 'rowid_delete', rowiddelete);
--
-- ejecuta el delete para las filas obtenidas
---
execdelete := dbms_sql.execute(iddelete);
--
-- Hace un commit cada n filas.
-- N se especifica en el parámetro "registroscommit"
--
if mod(dbms_sql.last_row_count, registroscommit) = 0 then
commit;
end if;
else
exit;
end if;
end loop;
--
-- Hace commit otra vez, para validar el ultimo grupo de registros
--
commit;
dbms_sql.close_cursor(idselect);
dbms_sql.close_cursor(iddelete);
end deletemasivo;
Respuesta de neosys
1
Antes de analizar todo el código, al ver que no se está borrando nada, lo primero que haría es :
Ejecutar a mano (segun indicas) : select count(*) from log_table where code>1
Para ver la existencia de registros que cumplen esa propiedad.
Y como condición obligatoria, especificar en la 'procedure' el control de execciones y si está bien compilada.
...
Exceptions
When other then
dbms_output.put_line( sqlerrm );
end;
Activando la salida con la condición de : SET SERVEROUTPUT ON SIZE 1000000
Si así no descubres nada -yo particularmente- me olvidaría de esta opción de 'DBMS_SQL' y optaría por la ejecución de SQL Dinámico con EXECUTE IMMEDIATE (que con la 8i ya puedes hacer) ... También has de tener en cuenta que el uso de identificador de registro por 'rowid' haciendo operaciones de 'DELETE, UPDATE e INSERT' pueden fallar -por reubicaciones internas que se realizan en TABLESPACES'. Te muestro tu ejemplo :
AH! Y pasa la CondicionWhere sin la clausula WHERE o modifica el código.
Yo le pasaría ...
NombreTabla --> 'log_table'
CondicionWhere --> 'code>1'
RegistrosCommit --> 5000 (y podrías incrementarlo más sino te morirás esperando ... y el segmento de rollback aguantará con esos 5000 de sobras).
CREATE OR REPLACE PROCEDURE DeleteMasivo( NombreTabla IN VARCHAR2,
CondicionWhere IN VARCHAR2 DEFAULT NULL,
RegistrosCommit IN NUMBER DEFAULT 1000 ) IS
Sentencia VARCHAR2(400);
BEGIN
Sentencia := 'DELETE FROM '||NombreTabla||' WHERE '||CondicionWhere||
' AND ROWNUM <= :filas';
--
LOOP
EXECUTE IMMEDIATE Sentencia USING RegistrosCommit;
EXIT WHEN SQL%ROWCOUNT = 0;
COMMIT;
END;
/
Y POR ULTIMO, LO MÁS SENCILLO Y MENOS COSTOSO (Y, POR SUPUESTO, RÁPIDO ya que no tira de segmento de ROLLBACK), eso si, te eliminaría todos los registros, que no sé si es lo que quieres hacer con una condición con la que usas ' :
TRUNCATE TABLE Nombre_de_tabla;
(Si te fallase, es por el tema de que tuvieses constraints de foreign key, es apuntando a esta tabla, pero también te pasaría si borrases registro por registro).
Ejecutar a mano (segun indicas) : select count(*) from log_table where code>1
Para ver la existencia de registros que cumplen esa propiedad.
Y como condición obligatoria, especificar en la 'procedure' el control de execciones y si está bien compilada.
...
Exceptions
When other then
dbms_output.put_line( sqlerrm );
end;
Activando la salida con la condición de : SET SERVEROUTPUT ON SIZE 1000000
Si así no descubres nada -yo particularmente- me olvidaría de esta opción de 'DBMS_SQL' y optaría por la ejecución de SQL Dinámico con EXECUTE IMMEDIATE (que con la 8i ya puedes hacer) ... También has de tener en cuenta que el uso de identificador de registro por 'rowid' haciendo operaciones de 'DELETE, UPDATE e INSERT' pueden fallar -por reubicaciones internas que se realizan en TABLESPACES'. Te muestro tu ejemplo :
AH! Y pasa la CondicionWhere sin la clausula WHERE o modifica el código.
Yo le pasaría ...
NombreTabla --> 'log_table'
CondicionWhere --> 'code>1'
RegistrosCommit --> 5000 (y podrías incrementarlo más sino te morirás esperando ... y el segmento de rollback aguantará con esos 5000 de sobras).
CREATE OR REPLACE PROCEDURE DeleteMasivo( NombreTabla IN VARCHAR2,
CondicionWhere IN VARCHAR2 DEFAULT NULL,
RegistrosCommit IN NUMBER DEFAULT 1000 ) IS
Sentencia VARCHAR2(400);
BEGIN
Sentencia := 'DELETE FROM '||NombreTabla||' WHERE '||CondicionWhere||
' AND ROWNUM <= :filas';
--
LOOP
EXECUTE IMMEDIATE Sentencia USING RegistrosCommit;
EXIT WHEN SQL%ROWCOUNT = 0;
COMMIT;
END;
/
Y POR ULTIMO, LO MÁS SENCILLO Y MENOS COSTOSO (Y, POR SUPUESTO, RÁPIDO ya que no tira de segmento de ROLLBACK), eso si, te eliminaría todos los registros, que no sé si es lo que quieres hacer con una condición con la que usas ' :
TRUNCATE TABLE Nombre_de_tabla;
(Si te fallase, es por el tema de que tuvieses constraints de foreign key, es apuntando a esta tabla, pero también te pasaría si borrases registro por registro).
Perfecto, la segunda opción de sql dinámico me sirvió más que la anterior con dbms, elimina más rapido y se puede ver que confirma cada 5000 con la otra parece que ejecuta todo..
Muchas gracias experto
Muchas gracias experto