PLSQL - удалить все объекты базы данных пользователя

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


create or replace procedure CLEAN_SCHEMA is
cursor schema_cur is
select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects;
schema_rec schema_cur%rowtype;
begin
select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
into schema_rec
from user_objects;
end;
/

Ответ 1

create or replace
FUNCTION                DROP_ALL_SCHEMA_OBJECTS RETURN NUMBER AS
PRAGMA AUTONOMOUS_TRANSACTION;
cursor c_get_objects is
  select object_type,'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_name
  from user_objects
  where object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','SYNONYM', 'MATERIALIZED VIEW')
  order by object_type;
cursor c_get_objects_type is
  select object_type, '"'||object_name||'"' obj_name
  from user_objects
  where object_type in ('TYPE');
BEGIN
  begin
    for object_rec in c_get_objects loop
      execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
    end loop;
    for object_rec in c_get_objects_type loop
      begin
        execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
      end;
    end loop;
  end;
  RETURN 0;
END DROP_ALL_SCHEMA_OBJECTS;

Создайте вышеуказанную функцию (автономно, так что DDL можно вызвать через функцию) то вы можете просто:

select DROP_ALL_SCHEMA_OBJECTS from dual;

когда вы хотите сбросить все свои объекты, убедитесь, что вы не пытаетесь отбросить процесс вашего запуска (я не забочусь о procs, поэтому у меня нет обработок или функций в списке object_type)

если вы хотите отбросить все, что вам нужно, анонимный блок

но мне нужно было сделать это с помощью инструмента, который только разрешил ansi sql (а не plsql), следовательно, сохраненный proc.

Enjoy.

Ответ 2

declare
  cursor ix is
    select *
      from user_objects
     where object_type in ('TABLE', 'VIEW', 'FUNCTION', 'SEQUENCE');
begin
 for x in ix loop
   execute immediate('drop '||x.object_type||' '||x.object_name);
 end loop;
end;

Ответ 3

Если пользователю не удастся повторно применить разрешения, возможно, проще просто отбросить пользователя и воссоздать их.

Ответ 4

Спасибо Мартин Брэмбли,

Я чувствую, что мы можем упростить ваш ответ следующим образом.

CREATE OR REPLACE
procedure  DROP_ALL_SCHEMA_OBJECTS AS
PRAGMA AUTONOMOUS_TRANSACTION;
cursor c_get_objects is
  select object_type,'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_name
  FROM USER_OBJECTS
  where object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','SYNONYM', 'MATERIALIZED VIEW', 'TYPE')
  order by object_type;
BEGIN
  begin
    for object_rec in c_get_objects loop
      execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
    end loop;
  end;
END DROP_ALL_SCHEMA_OBJECTS;

/

execute DROP_ALL_SCHEMA_OBJECTS;

Ответ 5

У вас есть хорошее начало.

Вот остальные:

У вас есть указатель AND и select. Вам нужен только курсор.
Следующий шаг - вызвать оператор drop с помощью динамического PLSQL. Я бы использовал инструкцию EXECUTE IMMEDIATE. Его более элегантная и предварительная совместимость просто позволяет выбрать имя вещи, которую вы бросаете, и отправить ее как переменную связывания для EXECUTE IMMEDIATE.
Чтобы удалить объекты схемы, вызывающие метод, а не схему, владеющую этим методом, вы должны использовать "AUTHID CURRENT_USER". Подробнее см. документацию Oracle.
Другие вещи, которые нужно удалить: пакеты, функции, процедуры (система, скорее всего, будет зависать, а затем таймаут, если вы попытаетесь удалить этот метод во время его запуска), классы Java, триггеры, представления, типы

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

Ответ 6

Вы близки - как кто-то заметил, что для утверждения вам нужен "EXECUTE IMMEDIATE". Вы должны учитывать:

Вместо того, чтобы создавать процедуру для этого, запустите это как анонимный блок PL/SQL, чтобы у вас не было проблемы с удалением выполняемой процедуры.
Добавьте тест типа объекта TABLE и в этом случае измените оператор drop, чтобы включить параметр каскада для обработки таблиц, которые являются "родителями" других таблиц с помощью ограничений внешнего ключа. Помните, что вы, вероятно, будете генерировать список курсоров в порядке, который не учитывает зависимости, которые будут блокировать падение.
Также в отношении зависимостей, вероятно, лучше сначала отбросить таблицы (добавьте DECODE в ваш курсор, который назначает меньшее числовое значение этому типу объекта и заказывает курсор, выбирая это значение). Если у вас есть объекты Oracle типа TYPE, которые используются в качестве типов столбцов в определении таблицы, сначала нужно удалить таблицу.
Если вы используете Oracle Advanced Queuing, объекты, связанные с этим, ДОЛЖНЫ быть сброшены с помощью вызовов API пакета AQ. Хотя вы можете отказаться от таблиц сгенерированных Oracle для поддержки очередей с помощью обычного DROP TABLE, вы окажетесь в позиции catch-22, а затем не сможете отбросить связанные очереди и не добавить их обратно. До версии 10g, по крайней мере, вы даже не могли удалить содержащую схему, не помещая базу данных в специальный режим, когда эта ситуация существовала.

Ответ 7

Спасибо Мартин Брэмбли и Виджаян Шринивасан!

Но версия Vijayan Srinivasan неверна, потому что зависимые объекты типа "TYPE" когда-то генерируют ошибки при их удалении:

ORA-02303: невозможно удалить или заменить тип с помощью зависимостей типа или таблицы

Моя версия удаляет ВСЕ объекты из схемы с помощью дополнительных:

процедуры и функции удаления (ожидают "DROP_ALL_SCHEMA_OBJECTS" )
удалить все задания и dbms_jobs
удалить все db_links
не отбрасывать вложенные таблицы, потому что DROPing вложенных таблиц не поддерживается

CREATE OR REPLACE
procedure  DROP_ALL_SCHEMA_OBJECTS AS
PRAGMA AUTONOMOUS_TRANSACTION;
cursor c_get_objects is
  select uo.object_type object_type_2,'"'||uo.object_name||'"'||decode(uo.object_type,'TABLE' ,' cascade constraints',null) obj_name2
  FROM USER_OBJECTS uo
  where uo.object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','SYNONYM', 'MATERIALIZED VIEW', 'FUNCTION', 'PROCEDURE')
        and not (uo.object_type = 'TABLE' and exists (select 1 from user_nested_tables unt where uo.object_name = unt.table_name))
        and not (uo.object_type = 'PROCEDURE' and uo.object_name = 'DROP_ALL_SCHEMA_OBJECTS')
  order by uo.object_type;
cursor c_get_objects_type is
  select object_type, '"'||object_name||'"' obj_name
  from user_objects
  where object_type in ('TYPE');
cursor c_get_dblinks is
  select '"'||db_link||'"' obj_name
  from user_db_links;
cursor c_get_jobs is
  select '"'||object_name||'"' obj_name
  from user_objects
  where object_type = 'JOB';
cursor c_get_dbms_jobs is
  select job obj_number_id
  from user_jobs
  where schema_user != 'SYSMAN';
BEGIN
  begin
    for object_rec in c_get_objects loop
      execute immediate ('drop '||object_rec.object_type_2||' ' ||object_rec.obj_name2);
    end loop;
    for object_rec in c_get_objects_type loop
      begin
        execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
      end;
    end loop;
    for object_rec in c_get_dblinks loop
        execute immediate ('drop database link '||object_rec.obj_name);
    end loop;
    for object_rec in c_get_jobs loop
        DBMS_SCHEDULER.DROP_JOB(job_name => object_rec.obj_name);
    end loop;
    commit;
    for object_rec in c_get_dbms_jobs loop
        dbms_job.remove(object_rec.obj_number_id);
    end loop;
    commit;
  end;
END DROP_ALL_SCHEMA_OBJECTS;

/

execute DROP_ALL_SCHEMA_OBJECTS;
drop procedure DROP_ALL_SCHEMA_OBJECTS;

exit;