Удаление всех пользовательских таблиц/последовательностей в Oracle

Как часть нашего процесса сборки и меняющейся базы данных, я пытаюсь создать script, который удалит все таблицы и последовательности для пользователя. Я не хочу воссоздавать пользователя, так как для этого потребуется больше разрешений, чем разрешено.

My script создает процедуру для удаления таблиц/последовательностей, выполняет процедуру и затем отменяет процедуру. Я выполняю файл из 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;
/

К сожалению, отказ от процедуры вызывает проблему. Кажется, что причина гонки, и процедура отбрасывается до ее выполнения.
Например:.

 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

Любые идеи о том, как это сделать?

Ответ 1

Если вы не собираетесь хранить хранимую процедуру, я бы использовал анонимный блок PLSQL:

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;

Ответ 2

Для оператора SQL в конце запятой в конце будет выполняться оператор. Команда/выполнит предыдущий оператор. Таким образом, вы заканчиваете строки

drop procedure drop_all_cdi_tables;
/

отбросит эту процедуру, затем попробуйте снова ее добавить.

Если вы посмотрите на свой результат, вы увидите "ПРОЦЕДУРА СОЗДАННОЙ", затем выполните, а затем "ПРОЦЕДУРА СОЗДАНА" еще раз, когда он повторно выполнит последний оператор (EXECUTE - это команда SQL * Plus, а не оператор, поэтому isn 't буферизуется), затем "ПРОЦЕДУРА УСТРАНЕНА", а затем пытается (и не удается) сбросить его второй раз.

PS. Я согласен с Dougman на нечетные вызовы DBMS_SQL.

Ответ 3

Похоже, что в вашем примере сообщения об ошибке появляется сообщение об ошибке drop_all_user_tables, но приведенный вами пример относится к drop_all_cdi_tables. Код drop_all_user_tables выглядит иначе?

Также вы звоните в dbms_sql, но, похоже, не используете его для разбора.

Ответ 4

В дополнение к решению, представленному OMG Ponies, если у вас есть последовательности с пробелами, вам нужно немного улучшить PLSQL:

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

Ответ 5

По какой-то причине решение OMG Ponies выдало ошибку "SQL-команда, которая не была должным образом закончена" на PLSQL. В случае, если кто-то другой сталкивается с той же проблемой, вот как я смог удалить все таблицы в текущей схеме.

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

Кредиты: Snippler