Oracle expdp - impdp tricks

Getting started

1) Create expimpuser user (In place of expimpuser you can give any name or use existing database user name, don't use sys)

   and give connect, resource to expimpuser

CREATE USER expimpuser IDENTIFIED BY passwowrd123 DEFAULT TABLESPACE APPS_TS;

GRANT CONNECT, RESOURCE TO expimpuser;

2) Create physical directory on the system like /u01/expimpdir -change path accroding to your system.

mkdir /u01/expimpdir

chmod -R 777 /u01/expimpdir

3) Create DBA dump directory where yyo would be placining all exmport dmp filies.

CREATE OR REPLACE DIRECTORY DATA_PUMP AS '/u01/expimpdir';

SELECT * FROM DBA_DIRECTORIES;

GRANT READ,WRITE ON DIRECTORY DATA_PUMP TO expimpuser;

GRANT READ,WRITE ON DIRECTORY DATA_PUMP TO system;

Dirrerent export scenarios

1) Export full schema from source to target server

  Lets say you want to export all the hr user data 

  GRANT  DATAPUMP_EXP_FULL_DATABASE  TO  hr;

  GRANT READ,WRITE ON DIRECTORY DATA_PUMP TO hr;

    ->If using container architectre use @PDB1(PDB1 is name of pluggable db) to connect to specific pluggable database.

%U would append numbers to dump directory once the filesize reaches 100GB.

Use PARALLEL to make it fast and compress to reduce the dump file size.

expdp hr/h#rc#01@PDB1 directory=DATA_PUMP dumpfile=HR_PDB1_%U.dmp 

COMPRESS=Y filesize=100G PARALLEL=5 logfile=EXPDP_HR_PDB1_20Jan23.log

-> Non PDB , don't use @PDB1 (PDB1 is name of pluggable db)

expdp hr/h#rc#01@PDB1 directory=DATA_PUMP dumpfile=HR_PDB1_%U.dmp 

COMPRESS=Y filesize=100G PARALLEL=5 logfile=EXPDP_HR_PDB1_20Jan24.log

->To import , make user hr user exists in new database , copy the dump files to target server /u01/expimpdir

impdp hr/h#rc#01@PDB1 directory=DATA_PUMP dumpfile=HR_PDB1_%U.dmp 

PARALLEL=5 logfile=IMPDP_HR_PDB1_20Jan24.log

2) Export View as table

expdp system/passwowrd##@PDB1  directory=DATA_PUMP dumpfile=HR_HR_VIEW1_%U.dmp 

COMPRESS=Y filesize=100G views_as_tables=HR.HR_VIEW PARALLEL=5 logfile=OHR_HR_VIEW1_17Jan24.log

Exclude indexes

Monitor jobs using

 select * from DBA_DATAPUMP_JOBS;

---Tips 

Always use METRICS=YES

and LOGTIME=ALL

--Monitoring

Monitor a Data Pump process in V$SESSION_LONGOPS

$ expdp ... job_name=DUMPJOB

SQL> select sid, serial#, sofar, totalwork

from v$session_longops

where opname = 'DUMPJOB' and sofar != totalwork;

sofar Shows how much work in MB has been done so far in relation to totalwork

totalwork Shows the total amount of work in MB

---Current status

SELECT sl.sid, sl.serial#, sl.sofar, sl.totalwork, dp.owner_name, dp.state, dp.job_mode

     FROM v$session_longops sl, v$datapump_job dp

     WHERE sl.opname = dp.job_name

     AND sl.sofar != sl.totalwork;

select * from dba_datapump_jobs;

--Run time details

SELECT b.username, a.sid, b.opname, b.target,

            round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE", b.TIME_REMAINING,

            to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time

     FROM v$session_longops b, v$session a

     WHERE a.sid = b.sid      ORDER BY 6;

--sofar work done

SELECT sl.sid, sl.serial#, sl.sofar, sl.MESSAGE,sl.totalwork, dp.owner_name, dp.state, dp.job_mode

FROM v$session_longops sl, v$datapump_job dp

WHERE sl.opname = dp.job_name;