Удалить все содержимое в схеме в Oracle

Ответ 1

Как правило, проще всего сбросить и добавить пользователя. Это предпочтительный метод, если у вас есть системный или sysdba доступ к базе данных.

Если у вас нет доступа к системному уровню и вы хотите очистить свою схему, следующий sql создаст серию записей капель, которые затем могут быть выполнены.

select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS','') || ';'  from user_objects

Затем я обычно очищаю корзину, чтобы действительно очистить вещи. Честно говоря, я не вижу большого смысла в корзине для оракула и хочу, чтобы я мог отключить его, но в любом случае:

purge recyclebin;

Это приведет к созданию списка операторов выписки. Не все из них будут выполняться - если вы катитесь с каскадом, падение индексов PK_ * не удастся. Но в итоге у вас будет довольно чистая схема. Подтвердите с помощью:

select * from user_objects

Также, чтобы добавить, блок Pl/sql в вашем вопросе удалит только таблицы, он не удалит все остальные объекты.

ps: Скопировано с какого-то веб-сайта, было полезно для меня. Протестировано и работает как шарм.

Ответ 2

Да, вы можете. Вы можете удалить пользователя и, таким образом, удалить объекты схемы. Оператор DROP USER используется для удаления пользователя из базы данных Oracle и удаления всех объектов, принадлежащих этому пользователю.

DROP USER TestDB;

Этот оператор будет работать только корректно и отбросить пользователя, называемого TestDB, только если TestDB не владеет никакими объектами в своей схеме. Объект в таблицах и представлениях смысла и т.д. Если он содержит какие-либо объекты, то после выполнения инструкции DROP USER вы получите следующее сообщение об ошибке

Error starting at line : 1 in command -
DROP USER TestDB
Error report -
SQL Error: ORA-01922: CASCADE must be specified to drop 'TESTDB'
01922. 00000 -  "CASCADE must be specified to drop '%s'"
*Cause:    Cascade is required to remove this user from the system.  The
           user own object which will need to be dropped.
*Action:   Specify cascade.

Если TestDB создал собственные объекты в своей схеме, вам нужно будет выполнить следующую инструкцию DROP USER:

DROP USER TestDB CASCADE;

Этот оператор сбросит все объекты, принадлежащие TestDB, и все ограничения ссылочной целостности объектов TestDB также будут удалены.

Ответ 3

Следующий SQLplus script генерирует операторы SQL, необходимые для удаления всех объектов схемы от желаемого пользователя:

set heading off
set pagesize 0
set feedback off

-- wipe out all scheduler jobs
select 'exec dbms_scheduler.drop_job(job_name => '''||j.job_creator||'.'||j.job_name||''');'
from user_scheduler_jobs j
/

-- wipe out all XML schemas
select 'exec dbms_xmlschema.deleteSchema(schemaURL => '''||s.qual_schema_url||''',delete_option => dbms_xmlschema.DELETE_CASCADE_FORCE);'
from user_xml_schemas s
/

-- wipe out all remaining objects
select 'drop '
       ||o.object_type
       ||' '||object_name
       ||case o.object_type when 'TABLE' then ' cascade constraints' when 'TYPE' then ' force' else '' end
       ||';'
from user_objects o
where o.object_type not in ('JOB','LOB','PACKAGE BODY','INDEX','TRIGGER')
and not exists (select 1
                from user_objects r
                where r.object_name = o.object_name 
                and   r.object_type = 'MATERIALIZED VIEW'
                and   o.object_type != 'MATERIALIZED VIEW'
               )
/

-- empty the recycle bin
select 'purge recyclebin;' from dual
/

script работает на 100% для меня, как есть, но если по какой-то причине он не является полным для вас, он легко расширяется с использованием виртуальной машины (VM) следующим образом:

• войти в систему как [пользователь вашей схемы для пустых]
• Сделайте снимок вашей виртуальной машины
• запустите указанный выше script для создания операторов удаления
• запустите операторы удаления (вы можете игнорировать любые ошибки "объект не существует", поскольку некоторые объекты будут удалены автоматически перед операцией удаления script. Это происходит в результате владения удаленными объектами).
• выйти
• войдите в систему как SYS и выполните "drop user [пользователь вашей схемы, который будет пустым]"; - БЕЗ каскадной опции

Если шаг 6 завершается неудачно, вам нужно идентифицировать оставшиеся объекты, которые не позволяют вашему пользователю быть удаленным, и добавить их к указанному выше script. Повторяйте пока ваш пользователь не упадет (т.е. Ваш script исчерпывающий), затем сохраните script

Отбросьте свою виртуальную машину на ваш снимок и повторите шаги 3 и 4 (используя обновленный script) - и теперь у вас будет 100% -ная пустая схема.

Ответ 4

Это то, что я использовал:

set echo off feedback off serverout on

spool drop_all_objects.sql

declare  l_object varchar2(32000);

begin

  for i in (select object_name, object_type from dba_objects where owner='<owner>') loop

    if i.object_type='JOB' then

          l_object := 'begin dbms_scheduler.drop_job (job_name => ''<owner>'||i.object_name||'''); end;';

elsif i.object_type='PROGRAM' then

      l_object := 'begin dbms_scheduler.drop_program (program_name => ''<owner>'||i.object_name||'''); end;';

elsif i.object_type='RULE' then

      l_object := 'begin dbms_rule_adm.drop_rule (rule_name => ''<owner>'||i.object_name||''', force => TRUE); end;';

elsif i.object_type='RULE SET' then

      l_object := 'begin dbms_rule_adm.drop_rule_set (rule_set_name => ''<owner>'||i.object_name||''', delete_rules => TRUE); end;';

elsif i.object_type='CHAIN' then

      l_object := 'begin dbms_scheduler.drop_chain (chain_name => ''<owner>'||i.object_name||''', force => TRUE); end;';

elsif i.object_type='RULE' then

      l_object := 'begin dbms_rule_adm.drop_evaluation_context (evaluation_context_name => ''<owner>'||i.object_name||''', force => TRUE); end;';

else

          l_object := 'drop '||i.object_type||'<owner>'||i.object_name||';';

end if;

dbms_output.put_line(i_object);

dbms_output.put_line('/');

end loop;

end;

/

@drop_all_objects

Ответ 5

Просто идите с:

select 'drop '||object_type||' '|| object_name || ';' from user_objects where 
object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX')

Ответ 6

На github нашел следующий скрипт, который работал "из коробки" (SQL * Plus: выпуск 12.2.0.1.0 Production):

https://gist.github.com/rafaeleyng/33eaef673fc4ee98a6de4f70c8ce3657

Спасибо автору Рафаэлю Эйнгу.

Просто войдите в схему, объекты которой вы хотите удалить.

BEGIN
   FOR cur_rec IN (SELECT object_name, object_type
                     FROM user_objects
                    WHERE object_type IN
                             ('TABLE',
                              'VIEW',
                              'PACKAGE',
                              'PROCEDURE',
                              'FUNCTION',
                              'SEQUENCE',
                              'SYNONYM'
                             ))
   LOOP
      BEGIN
         IF cur_rec.object_type = 'TABLE'
         THEN
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '" CASCADE CONSTRAINTS';
         ELSE
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '"';
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'FAILED: DROP '
                                  || cur_rec.object_type
                                  || ' "'
                                  || cur_rec.object_name
                                  || '"'
                                 );
      END;
   END LOOP;
END;
/