sql - tablas - ver usuarios oracle

Eliminando todas las tablas/secuencias de usuario en Oracle

Question

Como parte de nuestro proceso de compilación y la base de datos en evolución, estoy tratando de crear un script que elimine todas las tablas y secuencias para un usuario. No quiero volver a crear el usuario ya que esto requerirá más permisos que los permitidos.

Mi script crea un procedimiento para descartar las tablas / secuencias, ejecuta el procedimiento y, a continuación, descarta el procedimiento. Estoy ejecutando el archivo desde sqlplus:

drop.sql:

create or replace procedure drop_all_cdi_tables
is
cur integer;
begin
cur:= dbms_sql.OPEN_CURSOR();
for t in (select table_name from user_tables) loop
execute immediate ''drop table '' ||t.table_name|| '' cascade constraints'';
end loop;
dbms_sql.close_cursor(cur);

cur:= dbms_sql.OPEN_CURSOR();
for t in (select sequence_name from user_sequences) loop
execute immediate ''drop sequence '' ||t.sequence_name;
end loop;
dbms_sql.close_cursor(cur);
end;
/
execute drop_all_cdi_tables;
/
drop procedure drop_all_cdi_tables;
/

Desafortunadamente, la caída del procedimiento causa un problema. Parece que hay una condición de carrera y el procedimiento se descarta antes de que se ejecute.
P.ej:

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Mar 30 18:45:42 2010 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Procedure created. PL/SQL procedure successfully completed. Procedure created. Procedure dropped. drop procedure drop_all_user_tables * ERROR at line 1: ORA-04043: object DROP_ALL_USER_TABLES does not exist SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64 With the Partitioning, OLAP, Data Mining and Real Application Testing options

¿Alguna idea sobre cómo hacer que esto funcione?

Answer 1

Además de la solución presentada por OMG Ponies, si tiene secuencias con espacios en blanco, necesita mejorar un poco el PLSQL:

BEGIN
  FOR i IN (SELECT sequence_name FROM user_sequences)
    Loop
      EXECUTE IMMEDIATE(''"DROP SEQUENCE '' || user || ''"."'' || i.sequence_name || ''"'');
    End Loop;
End;
/

Answer 2

Para una sentencia de SQL, el punto y coma al final ejecutará la sentencia. El / ejecutará la sentencia anterior. Como tal, terminas líneas de

drop procedure drop_all_cdi_tables;
/

dejará caer el procedimiento, luego intente soltarlo nuevamente.

Si observa su salida, verá ''PROCEDIMIENTO CREADO'', luego se ejecutará, luego ''PROCEDIMIENTO CREADO'' nuevamente cuando se vuelva a ejecutar la última instrucción (EXECUTE es un comando de SQL * Plus, no una declaración, por lo que no se almacena en búfer) ) luego "PROCEDIMIENTO CAÍDO" y luego intenta (y falla) soltarlo por segunda vez.

PD. Estoy de acuerdo con Dougman en las llamadas DBMS_SQL impares.

Answer 3

Parece que su mensaje de error de ejemplo está recibiendo un error en drop_all_user_tables pero el ejemplo que dio es para drop_all_cdi_tables . ¿ drop_all_user_tables código drop_all_user_tables ve diferente?

También tiene llamadas a dbms_sql pero parece que no lo está utilizando para realizar ningún análisis.

Answer 4

Por alguna razón, la solución OMG Ponies dio un error "comando SQL no finalizado correctamente" en PLSQL. En caso de que alguien más encuentre el mismo problema, aquí es cómo pude eliminar todas las tablas en el esquema actual.

DECLARE
  table_name VARCHAR2(30);
  CURSOR usertables IS SELECT * FROM user_tables WHERE table_name NOT LIKE ''BIN$%'';
BEGIN
  FOR i IN usertables
  LOOP
  EXECUTE IMMEDIATE ''drop table '' || i.table_name || '' cascade constraints'';
  END LOOP;
END;
/

Créditos: Snippler

Answer 5

Si no tiene la intención de mantener el procedimiento almacenado, usaría un bloque PLSQL anónimo :

BEGIN

  --Bye Sequences!
  FOR i IN (SELECT us.sequence_name
              FROM USER_SEQUENCES us) LOOP
    EXECUTE IMMEDIATE ''drop sequence ''|| i.sequence_name ||'''';
  END LOOP;

  --Bye Tables!
  FOR i IN (SELECT ut.table_name
              FROM USER_TABLES ut) LOOP
    EXECUTE IMMEDIATE ''drop table ''|| i.table_name ||'' CASCADE CONSTRAINTS '';
  END LOOP;

END;

Answer 6

Simplemente ejecute estas dos declaraciones y luego ejecute todos los resultados:

select ''drop table '' || table_name || '';'' from user_tables;
select ''drop sequence '' || sequence_name || '';'' from user_sequences;