• Réaliser un export datapump
expdp \"/ as sysdba\" SCHEMAS=metier DIRECTORY=DATA_PUMP_DIR DUMPFILE=metier.dmp LOGFILE=metier.log


  • Réaliser un import datapump
impdp \"/ as sysdba\" SCHEMAS=metier DIRECTORY=DATA_PUMP_DIR DUMPFILE=metier.dmp LOGFILE=metier.log

Avec un Remap schéma

impdp \"/ as sysdba\" SCHEMAS=MGNADMF DIRECTORY=DATAPUMP DUMPFILE=MGNNATfull20160624.dmp LOGFILE=impdp_IQ_MGNNAT_MGNRW_280616.log REMAP_SCHEMA=MGNADMF:MGNRW


OPTION POSSIBLE

TABLE_EXISTS_ACTION = SKIP | APPEND | TRUNCATE | REPLACE


  • Purger les données d'un schéma
SET head off
SELECT 'DROP '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||';' FROM dba_objects WHERE owner='GAGCVEILLE' AND OBJECT_TYPE<>'INDEX';


  • Purger la Corbeille
purge dba_recyclebin;


  • Directory oracle
CREATE OR REPLACE directory AS 'G:\..\..';
 
GRANT READ, WRITE ON DIRECTORY TO ;
 
DROP directory '.....' ;


  • Monitorer les jobs Datapump
COL OWNER_NAME FORMAT A11
COL JOB_NAME FORMAT A20
COL OPERATION FORMAT A11
COL JOB_MODE FORMAT A9
COL STATE FORMAT A12
SET LINESIZE 120
 
SELECT * FROM DBA_DATAPUMP_JOBS;
 
OU
 
SET LINES 200
col owner_name format a10;
col state format a11
col operation LIKE state
col job_mode LIKE state
 
SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;


  • Killer les jobs Datapump

PENDANT

CTRL-C
stop_job=immediate
yes


APRES

SET serveroutput ON
SET LINES 100
DECLARE
h1 NUMBER;
BEGIN
— Format: dbms_datapump.attach('[job_name]','[owner_name]');
h1 := dbms_datapump.attach('SYS_EXPORT_SCHEMA_01','SYS');
dbms_datapump.stop_job (h1,1,0);
END;
/


  • Dropper les job Datapump inutile
DROP TABLE SYS.SYS_IMPORT_FULL_01;
DROP TABLE SYS.SYS_EXPORT_FULL_01;
DROP TABLE SYS.SYS_IMPORT_SCHEMA_01;
DROP TABLE SYS.SYS_EXPORT_SCHEMA_01;


  • Monter un partage CIFS pour export DP
mount.cifs //stock-ora/oradump$/RepDump /mnt/share --verbose -ouser=<user>,pass=<pass>,auto,uid=oracle,gid=dba 0 0

          DISTANT                      LOCAL


  • Voir ce qu'il se passe avec le user SYS pendant l'export ou l'import
SELECT sid, serial#, username, program, module, event, seconds_in_wait FROM v$session WHERE username = 'SYS';