Отбрасывание всех пользовательских таблиц/последовательностей в 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
Какие-либо идеи о том, как получить эту работу?
3 ответа
Если вы не собираетесь сохранять хранимую процедуру, я бы использовал анонимный блок 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;
Похоже, что ваш пример сообщения об ошибке получает ошибку на drop_all_user_tables, но пример, который вы привели, предназначен для drop_all_cdi_tables. Выглядит ли код drop_all_user_tables иначе?
Также у вас есть вызовы dbms_sql, но, похоже, вы не используете его для парсинга.
Для оператора SQL точка с запятой в конце приведет к выполнению оператора. Символ / выполняет предыдущий оператор. Таким образом, в конце строк
drop procedure drop_all_cdi_tables;
/
вы бросите процедуру, а затем попытаетесь бросить ее снова.
Если вы посмотрите на вывод, вы увидите "PROCEDURE CREATED", затем выполняется, затем "PROCEDURE CREATED" снова, поскольку он повторно выполняет последний оператор (EXECUTE - это команда SQL*Plus, а не оператор, поэтому не буферизируется), затем "PROCEDURE DROPPED", а затем он пытается (и терпит неудачу) бросить его во второй раз.
PS. Я согласен с Дугманом по поводу странных вызовов DBMS_SQL.