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

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

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

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


sql oracle plsql oracle11g build-process
person Ambience    schedule 31.03.2010    source источник

Ответы (6)


arrow_upward
83
arrow_downward

Если вы не собираетесь сохранять хранимую процедуру, я бы использовал анонимный 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;
person OMG Ponies    schedule 31.03.2010
comment
Это помогает. Как ни странно, мне пришлось добавить завершающий / в конце скрипта, чтобы фактически выполнить анонимный блок PLSQL. Если позже мы создадим настраиваемую задачу MSBUILD для выполнения операторов в сценарии, вызовет ли / вызовет проблемы? - person Ambience; 31.03.2010
comment
/ Сообщает sqlplus, что ваш блок PLSQL готов, и отправляет его в базу данных для обработки. Поэтому, если ваш MSBUILD использует sqlplus, ему понадобится файл /. - person Todd Pierce; 31.03.2010
comment
@OMGPonies Я пытаюсь выполнить ту же команду из Oracle SQL Developer, но получаю сообщение об ошибке: ORA-00701: object necessary for warmstarting database cannot be altered среди других ошибок. Любые намеки на то, почему? - person lbrahim; 25.05.2014

arrow_upward
6
arrow_downward

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

drop procedure drop_all_cdi_tables;
/

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

Если вы посмотрите на свой вывод, вы увидите «PROCEDURE CREATED», затем выполнено, затем снова «PROCEDURE CREATED», поскольку он повторно выполняет последний оператор (EXECUTE - это команда SQL * Plus, а не инструкция, поэтому не буферизуется ), затем "ПРОЦЕДУРА ОТКЛЮЧЕНА", а затем пытается (и терпит неудачу) отбросить ее во второй раз.

PS. Я согласен с Дугманом относительно нечетных вызовов DBMS_SQL.

person Gary Myers    schedule 31.03.2010

arrow_upward
3
arrow_downward

Просто запустите эти два оператора, а затем запустите все результаты:

select 'drop table ' || table_name || ';' from user_tables;
select 'drop sequence ' || sequence_name || ';' from user_sequences;
person Ahmed Elgamal    schedule 10.02.2018

arrow_upward
2
arrow_downward

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

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

person Doug Porter    schedule 31.03.2010
comment
Drop_all_cdi_tables - это тот же код. Dbms_sql был позаимствован из другого примера - я новичок в P / L SQL :) - person Ambience; 31.03.2010

arrow_upward
1
arrow_downward

В дополнение к решению, представленному 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;
/
person bravenoob    schedule 01.06.2015

arrow_upward
0
arrow_downward

По какой-то причине решение 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

person ZerosAndOnes    schedule 24.10.2016