Supprimer tous les contenus d'un schéma dans Oracle

Oui, vous pouvez. Vous pouvez laisser tomber l'utilisateur et ainsi laisser tomber les objets schema. La déclaration DROP USER est utilisée pour supprimer un utilisateur de la base de données Oracle et supprimer tous les objets appartenant à cet utilisateur.

DROP USER TestDB;

cette instruction ne s'exécute correctement et ne laisse tomber L'utilisateur appelé TestDB que si TestDB ne possède aucun objet dans son schéma. Objet dans le sens tables et vues etc. Si elle contient des objets, après avoir exécuté la déclaration DROP USER, vous obtiendrez l'erreur suivante: message

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's object which will need to be dropped.
*Action:   Specify cascade.

si TestDB possède des objets dans son schéma, vous devrez exécuter la déclaration suivante à la place:

DROP USER TestDB CASCADE;

cette instruction supprimera tous les objets appartenant à TestDB, et toutes les contraintes d'intégrité référentielle sur les objets TestDB seront également supprimées.

le script SQLplus suivant génère les instructions SQL nécessaires pour supprimer tous les objets de schéma de l'utilisateur désiré:

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
/

Le script fonctionne à 100%, pour moi, qu'est - mais si, pour une raison quelconque, il n'est pas complet pour vous, alors il est facilement améliorée en utilisant une machine virtuelle (VM) comme suit:

1. connecter en tant que [votre schéma de l'utilisateur à vide]
2. prenez un instantané de votre VM
3. exécuter le script ci-dessus pour créer la suppression déclarations
4. exécutez les déclarations de suppression (vous pouvez ignorer toutes les erreurs "object does not exist", car certains objets seront automatiquement supprimés avant la suppression du script. Cela se produit à la suite de posséder des objets supprimé)
5. journal
6. ouvrez une session SYS et exécutez "drop user [your schema user to empty];" -- sans l'option cascade

si l'étape 6 échoue alors vous devez identifier les objets restants empêcher votre utilisateur d'être supprimé et de les ajouter au script ci-dessus. Répétez jusqu'à ce que votre utilisateur gouttes (ie. votre script est complet) puis enregistrez votre script

Retournez votre VM à votre snapshot et répétez les étapes 3 et 4 (en utilisant votre script mis à jour) - et vous devriez maintenant avoir un schéma 100% vide.

voici ce que j'ai utilisé:

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