wake-up-neo.com

Alle Benutzertabellen/Sequenzen in Oracle löschen

Als Teil unseres Build-Prozesses und der sich entwickelnden Datenbank versuche ich, ein Skript zu erstellen, das alle Tabellen und Sequenzen für einen Benutzer entfernt. Ich möchte den Benutzer nicht neu erstellen, da hierfür mehr Berechtigungen erforderlich sind als zulässig. 

Mein Skript erstellt eine Prozedur zum Löschen der Tabellen/Sequenzen, führt die Prozedur aus und löscht dann die Prozedur. Ich führe die Datei von sqlplus aus:

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;
/

Leider verursacht das Löschen des Verfahrens ein Problem. Es scheint eine Wettlaufsituation zu geben, und die Prozedur wird fallen gelassen, bevor sie ausgeführt wird.
Z.B.:

 SQL * Plus: Release 11.1.0.7.0 - Produktion am Di 30.03. 18:45:42 2010 

 Copyright (c) 1982, 2008, Oracle. Alle Rechte vorbehalten.


 Verbunden:
 Oracle Database 11g Enterprise Edition Version 11.1.0.7.0 - 64-Bit-Produktion 
 Mit den Optionen Partitionierung, OLAP, Data Mining und Real Application Testing 


 Prozedur erstellt .


 PL/SQL-Prozedur erfolgreich abgeschlossen .


 Prozedur erstellt .


 Prozedur wurde fallen gelassen .

 Drop-Prozedur Drop_all_user_tables 
 * 
 FEHLER in Zeile 1: 
 ORA-04043: Objekt DROP_ALL_USER_TABLES ist nicht vorhanden 


 SQL> Verbindung zu Oracle Database 11g Enterprise Edition Version 11.1.0.7.0 - 64 getrennt 
 Mit den Optionen Partitionierung, OLAP, Data Mining und Real Application Testing 

Irgendwelche Ideen, wie man das zum Laufen bringt?

30
Ambience

Wenn Sie die gespeicherte Prozedur nicht beibehalten möchten, würde ich einen anonymen PLSQL-Block verwenden :

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;
76
OMG Ponies

Für eine SQL-Anweisung führt das Semikolon am Ende die Anweisung ..__ aus, und/führt die vorherige Anweisung ..__ aus. Sie beenden also Zeilen von

drop procedure drop_all_cdi_tables;
/

löscht die Prozedur und versucht dann, sie erneut zu löschen.

Wenn Sie sich Ihre Ausgabe ansehen, sehen Sie 'PROCEDURE CREATED', dann werden sie ausgeführt. Anschließend wird 'PROCEDURE CREATED' erneut ausgeführt, wenn die letzte Anweisung erneut ausgeführt wird (EXECUTE ist ein SQL * Plus-Befehl, keine Anweisung, daher nicht gepuffert ) dann "PROCEDURE DROPPED" und dann versucht (und schlägt fehl), es zum zweiten Mal zu löschen.

PS. Ich stimme mit Dougman hinsichtlich der ungeraden DBMS_SQL-Aufrufe überein. 

6
Gary Myers

Es sieht so aus, als würde Ihre Beispiel-Fehlermeldung einen Fehler in drop_all_user_tables erhalten, aber das von Ihnen angegebene Beispiel bezieht sich auf drop_all_cdi_tables. Sieht der drop_all_user_tables-Code anders aus?

Sie haben auch Aufrufe von dbms_sql, scheinen dies aber nicht zu tun, sondern analysieren.

2
Doug Porter

Wenn Sie über Sequenzen mit Leerzeichen verfügen, müssen Sie zusätzlich zu der von OMG Ponies vorgestellten Lösung die PLSQL-Version etwas verbessern:

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

Aus irgendeinem Grund hat die Lösung von OMG Ponies auf PLSQL einen Fehler "SQL-Befehl nicht ordnungsgemäß beendet" ausgegeben. Für den Fall, dass jemand anderes auf dasselbe Problem stößt, konnte ich im folgenden Schema alle Tabellen im aktuellen Schema löschen.

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;
/

Credits: Snippler

0
ZerosAndOnes

Führen Sie einfach diese beiden Anweisungen aus und führen Sie dann alle Ergebnisse aus:

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