Export and Import management

Create a directory:

COLUMN owner FORMAT A20
COLUMN directory_name FORMAT A25
COLUMN directory_path FORMAT A45
SELECT * FROM   dba_directories where directory_name='ARCHIVE_DIR' ORDER BY owner, directory_name;
create or replace directory MARS_EXP  as '/mnt/datapump/marsdb';
grant READ on directory MARS_EXP to CORE;
grant WRITE on directory MARS_EXP to CORE;
grant read,write on directory MARS_EXP to CORE;

Metadata_only:
nohup expdp \"/ as sysdba\" schemas=MARS,PLUTO,JUPITER,EARTH directory=DATA_PUMP_DIR dumpfile=Mars02012018.dmp content=METADATA_ONLY logfile=Mars02012018.log full=N flashback_time=systimestamp

Table level export:
nohup expdp \"/ as sysdba\" parfile=Mars_Export.par &
vi Mars_Export.par
directory=DATA_PUMP_DIR
dumpfile=Mars02012018.dmp
logfile=Mars02012018.log
flashback_time=systimestamp 
cluster=N 
tables=MARS.MARSTB,PLUTO.PLUTOTB,JUPITER.JUPITERTB,EARTH.EARTHTB

schemas export:
nohup expdp \'/ as sysdba\' parfile=Export_CFD.par &
oracle@hpx86-db02:/Export/datapump/Mars>cat Export_CFD.par
schemas=MARS,PLUTO,JUPITER,EARTH
directory=MARS_EXP
dumpfile=MARS_full_%U_02012018.dmp
logfile=MARS_full_02012018.log
parallel=16
exclude=statistics
cluster=n

Schema import:
select owner, object_type, count(*) from dba_objects where owner in ('MARS') GROUP BY ROLLUP (OWNER,object_type);
select 'drop '||object_type||' '||owner||'.'||object_name||' cascade constraints;' from dba_objects where owner='SCOTT' AND OBJECT_TYPE='TABLE';
select 'drop '||object_type||' '||owner||'.'||object_name||';' from dba_objects where owner='SCOTT';

purge dba_recyclebin;

nohup impdp \'/ as sysdba\' parfile=impdp_CFD.par &
[oracle@hpx86-db02]$ cat impdp_CFD.par
[oracle@oracle@hpx86-db02:]$ cat impdp_CFD.par
schemas=MARS,PLUTO,JUPITER,EARTH
transform=OID:n
directory=MARS_EXP
dumpfile=MARS_full_%U_02012018.dmp
logfile=MARS_full_02012018.log
parallel=48
cluster=n

Export through network link:
expdp testing_expdp/testing_expdp directory=MY_DIR schemas=DESGNE dumpfile=DESGNE_%U.dmp logfile=exp.log compression=all PARALLEL=8 network_link=testing_export

Kill or Stop Oracle Datapump Import ( KILL IMPDP ) Job
select JOB_NAME,state from dba_datapump_jobs;
Output:
SYS_IMPORT_SCHEMA_02
SYS_IMPORT_SCHEMA_01
oracle@hpx86-db02:impdp \"/ as sysdba\" attach=SYS_IMPORT_SCHEMA_02
>  expdp attach=SYS_EXPORT_TABLE_01
>status
$IMPORT> kill_job

Objects count:
col OWNER for a20
select owner,OBJECT_TYPE,count(*) from dba_objects where owner='SCOTT' group by owner,object_type order by owner,object_type ;
select owner,OBJECT_TYPE,count(*) from dba_objects where owner in ('MARS','PLUTO','JUPITER','EARTH17') group by owner,object_type order by owner,object_type ;
View the JOB_NAME using dba_datapump_jobs using below sql
SET lines 1000
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state
select * from dba_datapump_jobs where state='EXECUTING';

To check export or import or datapump status:
select x.job_name,b.state,b.job_mode,b.degree
, x.owner_name,z.sql_text, p.message
, p.totalwork, p.sofar
, round((p.sofar/p.totalwork)*100,2) done
, p.time_remaining
from dba_datapump_jobs b 
left join dba_datapump_sessions x on (x.job_name = b.job_name)
left join v$session y on (y.saddr = x.saddr)
left join v$sql z on (y.sql_id = z.sql_id)
left join v$session_longops p ON (p.sql_id = y.sql_id)
WHERE y.module='Data Pump Worker'
AND p.time_remaining > 0;

The following Query displays the amount of work done so far:
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;
                    or
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    
AND B.OPNAME LIKE '%pump%'
ORDER BY 6;

Enjoy the work and do like me if you like this article!!!