oracle - usuario - procedimientos pl sql ejemplos

PLSQL-Eliminar todos los objetos de base de datos de un usuario

Question

Estoy tratando de usar un procedimiento (sin parámetros) para eliminar todos los objetos de base de datos creados por el usuario ubicados dentro del esquema desde donde se inició el procedimiento, pero realmente no estoy seguro de cómo hacerlo. Esto es lo que tengo hasta ahora, pero creo que estoy haciendo esto de manera incorrecta.

create or replace procedure CLEAN_SCHEMA is
cursor schema_cur is
select ''drop ''||object_type||'' ''|| object_name||  DECODE(OBJECT_TYPE,''TABLE'','' CASCADE CONSTRAINTS;'','';'')
from user_objects;
schema_rec schema_cur%rowtype;
begin
select ''drop ''||object_type||'' ''|| object_name||  DECODE(OBJECT_TYPE,''TABLE'','' CASCADE CONSTRAINTS;'','';'')
into schema_rec
from user_objects;
end;
/

Answer 1

A menos que el usuario tenga permisos para volver a solicitar permisos, es probablemente más fácil simplemente dejarlo y recrearlo.

Answer 2

Gracias Martin Brambley y Vijayan Srinivasan !

Pero la versión de Vijayan Srinivasan no es correcta, porque los objetos dependientes de tipo ''TIPO'' en algún momento generan errores al soltarlos:

ORA-02303: no se puede quitar ni reemplazar un tipo con dependientes de tipo o tabla

Mi versión elimina TODOS los objetos del esquema con adicional:

• abandonar procedimientos y funciones (esperar ''DROP_ALL_SCHEMA_OBJECTS'')
• soltar todos los trabajos y dbms_jobs
• soltar todos los db_links
• no descarte las tablas anidadas, ya que el DROP de tablas anidadas no es compatible

CREATE OR REPLACE procedure DROP_ALL_SCHEMA_OBJECTS AS PRAGMA AUTONOMOUS_TRANSACTION; cursor c_get_objects is select uo.object_type object_type_2,''"''||uo.object_name||''"''||decode(uo.object_type,''TABLE'' ,'' cascade constraints'',null) obj_name2 FROM USER_OBJECTS uo where uo.object_type in (''TABLE'',''VIEW'',''PACKAGE'',''SEQUENCE'',''SYNONYM'', ''MATERIALIZED VIEW'', ''FUNCTION'', ''PROCEDURE'') and not (uo.object_type = ''TABLE'' and exists (select 1 from user_nested_tables unt where uo.object_name = unt.table_name)) and not (uo.object_type = ''PROCEDURE'' and uo.object_name = ''DROP_ALL_SCHEMA_OBJECTS'') order by uo.object_type; cursor c_get_objects_type is select object_type, ''"''||object_name||''"'' obj_name from user_objects where object_type in (''TYPE''); cursor c_get_dblinks is select ''"''||db_link||''"'' obj_name from user_db_links; cursor c_get_jobs is select ''"''||object_name||''"'' obj_name from user_objects where object_type = ''JOB''; cursor c_get_dbms_jobs is select job obj_number_id from user_jobs where schema_user != ''SYSMAN''; BEGIN begin for object_rec in c_get_objects loop execute immediate (''drop ''||object_rec.object_type_2||'' '' ||object_rec.obj_name2); end loop; for object_rec in c_get_objects_type loop begin execute immediate (''drop ''||object_rec.object_type||'' '' ||object_rec.obj_name); end; end loop; for object_rec in c_get_dblinks loop execute immediate (''drop database link ''||object_rec.obj_name); end loop; for object_rec in c_get_jobs loop DBMS_SCHEDULER.DROP_JOB(job_name => object_rec.obj_name); end loop; commit; for object_rec in c_get_dbms_jobs loop dbms_job.remove(object_rec.obj_number_id); end loop; commit; end; END DROP_ALL_SCHEMA_OBJECTS; / execute DROP_ALL_SCHEMA_OBJECTS; drop procedure DROP_ALL_SCHEMA_OBJECTS; exit;

Answer 3

Gracias Martin Brambley,

Siento que podemos simplificar su respuesta de la siguiente manera.

CREATE OR REPLACE
procedure  DROP_ALL_SCHEMA_OBJECTS AS
PRAGMA AUTONOMOUS_TRANSACTION;
cursor c_get_objects is
  select object_type,''"''||object_name||''"''||decode(object_type,''TABLE'' ,'' cascade constraints'',null) obj_name
  FROM USER_OBJECTS
  where object_type in (''TABLE'',''VIEW'',''PACKAGE'',''SEQUENCE'',''SYNONYM'', ''MATERIALIZED VIEW'', ''TYPE'')
  order by object_type;
BEGIN
  begin
    for object_rec in c_get_objects loop
      execute immediate (''drop ''||object_rec.object_type||'' '' ||object_rec.obj_name);
    end loop;
  end;
END DROP_ALL_SCHEMA_OBJECTS;

/

execute DROP_ALL_SCHEMA_OBJECTS;

Answer 4

Lo que tienes es un buen comienzo.

Aquí está el resto:

• Tienes un cursor y una declaración de selección. Solo necesitas el cursor.
• El siguiente paso es llamar a la instrucción drop usando el PLSQL dinámico. Yo usaría la sentencia EXECUTE IMMEDIATE. Es más elegante y fácil de usar solo para seleccionar el nombre de la cosa que está eliminando y enviarlo como una variable de enlace para EJECUTAR INMEDIATO.
• Para eliminar los objetos del esquema que llama al método y no el esquema que posee el método, debe usar "AUTHID CURRENT_USER". Consulte la documentación de Oracle para obtener más información.
• Otras cosas para eliminar: paquetes, funciones, procedimientos (es probable que el sistema se bloquee y se agote el tiempo de espera si intenta eliminar este método mientras se ejecuta), clases de Java, desencadenadores, vistas, tipos

Por último, es obvio que este es un método muy peligroso, por lo que puede considerar colocarlo en una secuencia de comandos en lugar de un procedimiento almacenado para que no se quede en la base de datos para que nadie lo ejecute.

Answer 5

Usted está cerca: como alguien más notó que necesita un "EJECUTAR INMEDIATO" para la declaración. Deberías considerar:

• En lugar de crear un procedimiento para hacer esto, ejecute esto como un bloque PL / SQL anónimo para que no tenga el problema de intentar descartar un procedimiento que se esté ejecutando.

• Agregue una prueba para el tipo de objeto de TABLE y, en ese caso, modifique la instrucción drop para incluir la opción en cascada para manejar las tablas que son "padres" de otras tablas mediante restricciones de clave externa. Recuerde que probablemente generará la lista de cursores en un orden que no considera las dependencias que bloquearán la caída.

• También en el tema de las dependencias, probablemente sea mejor eliminar primero las tablas (agregue un DECODE en su cursor que asigna un valor numérico inferior a este tipo de objeto y ordene la selección del cursor por este valor). Si tiene objetos de Oracle de tipo TIPO que se utilizan como tipos de columna en una definición de tabla, la tabla debe eliminarse primero.

• Si utiliza Oracle Advanced Queue Server, los objetos relacionados con esto DEBEN ser eliminados con las llamadas a la API del paquete AQ. Aunque puede eliminar las tablas generadas por Oracle para el soporte de colas con una TABLA DE GOTAS regular, se encontrará en la posición catch-22 de la cual no podrá eliminar las colas relacionadas ni agregarlas nuevamente. Hasta la versión 10g, al menos ni siquiera se podía descartar el esquema que contiene sin poner la base de datos en un modo especial cuando existía esta situación

Answer 6

create or replace
FUNCTION                DROP_ALL_SCHEMA_OBJECTS RETURN NUMBER AS
PRAGMA AUTONOMOUS_TRANSACTION;
cursor c_get_objects is
  select object_type,''"''||object_name||''"''||decode(object_type,''TABLE'' ,'' cascade constraints'',null) obj_name
  from user_objects
  where object_type in (''TABLE'',''VIEW'',''PACKAGE'',''SEQUENCE'',''SYNONYM'', ''MATERIALIZED VIEW'')
  order by object_type;
cursor c_get_objects_type is
  select object_type, ''"''||object_name||''"'' obj_name
  from user_objects
  where object_type in (''TYPE'');
BEGIN
  begin
    for object_rec in c_get_objects loop
      execute immediate (''drop ''||object_rec.object_type||'' '' ||object_rec.obj_name);
    end loop;
    for object_rec in c_get_objects_type loop
      begin
        execute immediate (''drop ''||object_rec.object_type||'' '' ||object_rec.obj_name);
      end;
    end loop;
  end;
  RETURN 0;
END DROP_ALL_SCHEMA_OBJECTS;

Cree la función anterior (autónoma para que se pueda llamar a DDL a través de una función) luego puede simplemente:

select DROP_ALL_SCHEMA_OBJECTS from dual;

cuando desee eliminar todos sus objetos, asegúrese de no intentar cancelar el proceso que está ejecutando (no me preocupan los procesos, por eso no tengo procs ni funciones en la lista object_type)

Si quieres soltar todo necesitas un bloque anónimo.

pero necesitaba poder hacer esto desde una herramienta que solo permitiera ansi sql (no plsql) por lo tanto, un proceso almacenado.

Disfrutar.

Answer 7

declare
  cursor ix is
    select *
      from user_objects
     where object_type in (''TABLE'', ''VIEW'', ''FUNCTION'', ''SEQUENCE'');
begin
 for x in ix loop
   execute immediate(''drop ''||x.object_type||'' ''||x.object_name);
 end loop;
end;