— Avant de réimporter un dump de schéma, il faut parfois faire le ménage
— Créer le Directory et donner les droits de l’utiliser
— Supprimer les jobs, les connections, le user
whenever OSERROR exit 1
whenever SQLERROR exit 2
— 1 er param le fichier spool
— 2eme param le repertoire du directory datapump
— 3eme param le schema cible
set serveroutput on
set linesize 132
set pagesize 132
— set autoprint off
— set autotrace off
— afin d’éliminer la ligne :Procédure PL/SQL terminée avec succès.
set FEEDBACK off
— afin d’éliminer la verification des variables
set VERIFY off
—
SPOOL &&1
prompt — Creation du directory avec &&2
create or replace directory DATA_PUMP_DIR as ‘&&2’;
grant read,write on DIRECTORY DATA_PUMP_DIR to public;
—
Declare
— Declaration des variables
MaRequete varchar2(2000) ;
MonInstance varchar2(2000) ;
n PLS_INTEGER;
Begin
DBMS_OUTPUT.ENABLE( 10000000 ) ;
DBMS_OUTPUT.PUT_LINE (‘– Vous trouverez la log d execution dans &&1’);
select instance_name into MonInstance from v$instance;
DBMS_OUTPUT.PUT_LINE (‘– __________________________________________________________’);
DBMS_OUTPUT.PUT_LINE (‘– Suppression des jobs cours sur ‘ || MonInstance || ‘ &&3 ‘);
DBMS_OUTPUT.PUT_LINE (‘– __________________________________________________________’);
n := 0;
FOR c_job_name IN (select job_name from dba_scheduler_jobs
where upper(owner) = upper(‘&&3’)
) LOOP
n := n + 1;
DBMS_OUTPUT.PUT_LINE(‘Execution : DROP SCHEDULED JOB ‘ || c_job_name.job_name || ‘ ;’);
DBMS_SCHEDULER.DROP_JOB(job_name => ‘&&3..’ || c_job_name.job_name, force => TRUE);
End loop ;
DBMS_OUTPUT.PUT_LINE (‘– ______________________________________’);
DBMS_OUTPUT.PUT_LINE (‘– Nombre de Jobs suppprrriiimmmeee :’ || n);
DBMS_OUTPUT.PUT_LINE (‘– ______________________________________’);

DBMS_OUTPUT.PUT_LINE (‘–‘);
DBMS_OUTPUT.PUT_LINE (‘–‘);

DBMS_OUTPUT.PUT_LINE (‘– _______________________________________________________’);
DBMS_OUTPUT.PUT_LINE (‘– Suppression connexions en cours ‘ || MonInstance || ‘ &&3’);
DBMS_OUTPUT.PUT_LINE (‘– _______________________________________________________’);
n := 0;
FOR c_kill IN (select SID,SERIAL# from v$session
where upper(username) = upper(‘&&3’)
and STATUS != ‘KILLED’
) LOOP
n := n + 1;
MaRequete := ‘ALTER SYSTEM KILL SESSION ‘ ||  » » || c_kill.sid || ‘,’ || c_kill.serial# ||  » » ;
DBMS_OUTPUT.PUT_LINE (‘Execution de ‘ || MaRequete );
EXECUTE IMMEDIATE MaRequete ;
End loop ;
DBMS_OUTPUT.PUT_LINE (‘– ______________________________________’);
DBMS_OUTPUT.PUT_LINE (‘– Nombre de Connexion suppprrriiimmmeee :’ || n);
DBMS_OUTPUT.PUT_LINE (‘– ______________________________________’);

DBMS_OUTPUT.PUT_LINE (‘–‘);
DBMS_OUTPUT.PUT_LINE (‘– fin du block pl’);
DBMS_OUTPUT.PUT_LINE (‘–‘);
End ;
/
prompt — Suppression du user &&3
DROP USER &&3 CASCADE;
prompt — Fin du traitement

SPOOL OFF
exit

— fin du script sql