export/ import job monitor from data-pump (STATUS/ stop immediate)

when export/import running if you want to check status of use below procedure to check.

step1:
check JOB_NAME using dba_datapump_job.(like below ).

set lines 300;
set pages 300;
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;

ouput like below
OWNER_NAME JOB_NAME             OPERATION   JOB_MODE    STATE     
---------- -------------------- ----------- ----------- -----------
OPER       SYS_IMPORT_SCHEMA_01 IMPORT      SCHEMA      EXECUTING 

step2:  now you know current running job

impdp userid=usenmae/password attach=SYS_IMPORT_SCHEMA_01

(or)

impdp attach=SYS_IMPORT_SCHEMA_01  => eneter next it will ask user to connect you can connect thoutgh "/ as sysdba"


ouput like below some lines remove due to naming or safety perpous
Job: SYS_IMPORT_SCHEMA_01
  Owner: OPER
  Operation: IMPORT
  Creator Privs: TRUE
  Start Time:
  Mode: SCHEMA
  Instance:
  Max Parallelism:
  EXPORT Job Parameters:
 CLIENT_COMMAND 
  IMPORT Job Parameters:
  Parameter Name      Parameter Value:
     C
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File:
 Worker 1 Status:
  Process Name: DW00
  State: EXECUTING
  Object Schema:
  Object Name:
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Completed Rows: 3,437,594
  Completed Bytes: 40,101,139,632
  Percent Done: 2
  Worker Parallelism: 1

Import>
---------------if you want check status  eneter command
Import>STATUS


---------------if you want check STOP_JOB  eneter command
Import>STOP_JOB=IMMEDIATE
 ---------------and then it will ask you to conform if you really want to stop yes and proceed.
Are you sure you wish to stop this job ([yes]/no): yes

HELP
STATUS
CONTINUE_CLIENT
KILL_JOB 
START_JOB
PARALLEL 
STOP_JOB


-----------------all suggestion are welcome to receive----------------------------- 

Monitor expdp/imp job script when it slow

srdc_expdp_performance.sql   ==> create sql from below script and run

---------------


REM srdc_exp_performance.sql - Gather Information for EXPDP Performance Issues
define SRDCNAME='EXPDP_PERFORMANCE'
SET MARKUP HTML ON PREFORMAT ON
set TERMOUT off FEEDBACK off verify off TRIMSPOOL on HEADING off
set lines 132 pages 10000
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
set TERMOUT on MARKUP html preformat on
REM
spool &&SRDCSPOOLNAME..htm
select '+----------------------------------------------------+' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp:       '||to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine:         '||host_name from v$instance
union all
select '| Version:         '||version from v$instance
union all
select '| DBName:          '||name from v$database
union all
select '| Instance:        '||instance_name from v$instance
union all
select '+----------------------------------------------------+' from dual
/

set HEADING on MARKUP html preformat off
REM === -- end of standard header -- ===

set concat "#"
SET PAGESIZE 9999
SET LINESIZE 256
SET TRIMOUT ON
SET TRIMSPOOL ON
Column sid format 99999 heading "SESS|ID"
Column serial# format 9999999 heading "SESS|SER|#"
Column session_id format 99999 heading "SESS|ID"
Column session_serial# format 9999999 heading "SESS|SER|#"
Column event format a40
Column total_waits format 9,999,999,999 heading "TOTAL|TIME|WAITED|MICRO"
Column pga_used_mem format 9,999,999,999
Column pga_alloc_mem format 9,999,999,999
Column status heading 'Status' format a20
Column timeout heading 'Timeout' format 999999
Column error_number heading 'Error Number' format 999999
Column error_msg heading 'Message' format a44
Column sql_text heading 'Current SQL statement' format a44
Column Number_of_objects format 99999999
Column object_type format a35
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SET MARKUP HTML ON PREFORMAT ON

--====================Retrieve sid, serial# information for the active export process(es)===========================
SET HEADING OFF
SELECT '=================================================================================================================================' FROM dual
UNION ALL
SELECT 'Determine sid, serial# details for the active DataPump export process(es):' FROM dual
UNION ALL
SELECT '=================================================================================================================================' FROM dual;
SET HEADING ON
set feedback on
col program for a38
col username for a10
col spid for a7
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
       s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from   v$session s, v$process p, dba_datapump_sessions d
where  p.addr=s.paddr and s.saddr=d.saddr and
      (UPPER (s.program) LIKE '%DM0%' or UPPER (s.program) LIKE '%DW0%');
set feedback off

--====================Retrieve sid, serial#, PGA details for the active export process(es)===========================
SET HEADING OFF
SELECT '=================================================================================================================================' FROM dual
UNION ALL
SELECT 'Determine PGA details for the active DataPump export process(es):' FROM dual
UNION ALL
SELECT '=================================================================================================================================' FROM dual;
SET HEADING ON
set feedback on
SELECT sid, s.serial#, p.PGA_USED_MEM,p.PGA_ALLOC_MEM
FROM   v$process p, v$session s
WHERE  p.addr = s.paddr and
       (UPPER (s.program) LIKE '%DM0%' or UPPER (s.program) LIKE '%DW0%');
set feedback off

--====================Retrieve the number of objects per object_type===========================
SET HEADING OFF
SELECT '=================================================================================================================================' FROM dual
UNION ALL
SELECT 'Determine the number of objects per object_type:' FROM dual
UNION ALL
SELECT '=================================================================================================================================' FROM dual;
SET HEADING ON
SELECT count(*) Number_of_objects, object_type
FROM   dba_objects
GROUP  BY object_type
ORDER  BY Number_of_objects desc;

--====================Retrive all wait events and time in wait for the running export process(es)====================
SET HEADING OFF
SELECT '=================================================================================================================================' FROM dual
UNION ALL
SELECT 'All wait events and time in wait for the active DataPump export process(es):' FROM dual
UNION ALL
SELECT '=================================================================================================================================' FROM dual;
SET HEADING ON
select session_id, session_serial#, Event, sum(time_waited) total_waits
from   v$active_session_history
where  sample_time > sysdate - 1 and
       (UPPER (program) LIKE '%DM0%' or UPPER (program) LIKE '%DW0%') and
       session_id in (select sid from v$session where UPPER (program) LIKE '%DM0%' or UPPER (program) LIKE '%DW0%') and
       session_state = 'WAITING' And time_waited > 0
group  by session_id, session_serial#, Event
order  by session_id, session_serial#, total_waits desc;

--====================Export progress - retrieve current sql id and statement====================
SET HEADING OFF
SELECT '=================================================================================================================================' FROM dual
UNION ALL
SELECT 'Export progress - retrieve current SQL id and statement:' FROM dual
UNION ALL
SELECT '=================================================================================================================================' FROM dual;
SET HEADING ON
select sysdate, a.sid, a.sql_id, a.event, b.sql_text
from   v$session a, v$sql b
where  a.sql_id=b.sql_id and
       (UPPER (a.program) LIKE '%DM0%' or UPPER (a.program) LIKE '%DW0%')
order  by a.sid desc;

SET HEADING OFF MARKUP HTML OFF
SET SERVEROUTPUT ON FORMAT WRAP

declare
  v_ksppinm varchar2(30);
  CURSOR c_fix IS select v.KSPPSTVL value FROM x$ksppi n, x$ksppsv v WHERE n.indx = v.indx and n.ksppinm = v_ksppinm;
  v_long_op_flag number := 0 ;
  v_target varchar2(100);
  v_sid number;
  v_totalwork number;
  v_opname varchar2(200);
  v_sofar number;
  v_time_remain number;
  stmt varchar2(2000);
  v_fix c_fix%ROWTYPE;

begin
  stmt:='select count(*) from v$session_longops where sid in (select sid from v$session where UPPER (program) LIKE '||
        '''%DM0%'''||' or UPPER (program) LIKE '||'''%DW0%'')'||' and totalwork <> sofar';
  DBMS_OUTPUT.PUT_LINE ('<pre>');
  dbms_output.put_line ('=================================================================================================================================');
  dbms_output.put_line ('Check v$session_longops - Export pending work');
  dbms_output.put_line ('=================================================================================================================================');
  execute immediate stmt into v_long_op_flag;
  if (v_long_op_flag > 0) then
    dbms_output.put_line ('The number of long running export processes is:   '|| v_long_op_flag);
    dbms_output.put_line (chr (10));
    for longop in (select sid,target,opname, sum(totalwork) totwork, sum(sofar) sofar, sum(totalwork-sofar) blk_remain, Round(sum(time_remaining/60),2) time_remain
                   from v$session_longops where sid in (select sid from v$session where UPPER (program) LIKE '%DM0%' or UPPER (program) LIKE '%DW0%') and
                   opname NOT LIKE '%aggregate%' and totalwork <> sofar group by sid,target,opname) loop
      dbms_output.put_line (Rpad ('Export SID', 40, ' ')||chr (9)||':'||chr (9)||longop.sid);
      dbms_output.put_line (Rpad ('Object being read', 40, ' ')||chr (9)||':'||chr (9)||longop.target);
      dbms_output.put_line (Rpad ('Operation being executed', 40, ' ')||chr (9)||':'||chr (9)||longop.opname);
      dbms_output.put_line (Rpad ('Total blocks to be read', 40, ' ')||chr (9)||':'||chr (9)||longop.totwork);
      dbms_output.put_line (Rpad ('Total blocks already read', 40, ' ')||chr (9)||':'||chr (9)||longop.sofar);
      dbms_output.put_line (Rpad ('Remaining blocks to be read', 40, ' ')||chr (9)||':'||chr (9)||longop.blk_remain);
      dbms_output.put_line (Rpad ('Estimated time remaining for the process', 40, ' ')||chr (9)||':'||chr (9)||longop.time_remain|| ' Minutes');
      dbms_output.put_line (chr (10));
    end Loop;
  else
    DBMS_OUTPUT.PUT_LINE ('No export session is found in v$session_longops');
    dbms_output.put_line (chr (10));
  end If;

  for i in 1..6 loop
    if i = 1 then
      v_ksppinm := 'fixed_date';
    elsif i = 2 then
      v_ksppinm := 'aq_tm_processes';
    elsif i = 3 then
      v_ksppinm := 'compatible';
    elsif i = 4 then
      v_ksppinm := 'optimizer_features_enable';
    elsif i = 5 then
      v_ksppinm := 'optimizer_index_caching';
    elsif i = 6 then
      v_ksppinm := 'optimizer_index_cost_adj';
    end if;

    dbms_output.put_line ('=================================================================================================================================');
    DBMS_OUTPUT.PUT_LINE ('Is the '||upper (v_ksppinm)||' parameter set?');
    dbms_output.put_line ('=================================================================================================================================');

    open c_fix;
    fetch c_fix into v_fix;
    close c_fix;
    if nvl (to_char (v_fix.value), '1') = to_char ('1') then
      DBMS_OUTPUT.PUT_LINE ('No value is found for '||upper (v_ksppinm)||' parameter.');
    else
      DBMS_OUTPUT.PUT_LINE ('The '||upper (v_ksppinm)||' parameter is set for this database and the value is: '||v_fix.value);
    end if;
    dbms_output.put_line('=================================================================================================================================');
    dbms_output.put_line (chr (10));
  end loop;
  DBMS_OUTPUT.PUT_LINE('</pre>');
end;
/

spool off
PROMPT
PROMPT
PROMPT REPORT GENERATED : &SRDCSPOOLNAME..htm

exit

=======================================


-- note :- this i get from vendor when have some fallow up 

data pump export size estimate script

Data pump export size estimate script

following script will provide you  data in each schema on your database(MB). 
you can change if want GB as per need. 
Hope with the help of this script you will get aprox size of export backup.
-----------------------------------------------------------------------------------------------------------------------
SELECT owner, ROUND(SUM(size_mb)) MBytes FROM
(
SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
--AND TABLESPACE_NAME LIKE 'COSTE%'
--AND SEGMENT_NAME LIKE 'OE_ORDER_LINES_ALL%'
--AND partition_name LIKE 'USAGE_FCT_NEW%'
--AND OWNER = 'TARGET_DW'
--AND ROUND(bytes/(1024*1024),2) > 1000)
)
GROUP BY owner
ORDER BY MBytes DESC;



---------------------------------------
any suggestion are well come, you can comment if you have better script in botom plz.

Data pump orphan jobs cleanup.

when export/import job fail time it exit from procees level but still not in dabase.
But it does not effect your next job job or current job it give another job name like _02 runs.
only impact is still it occupies space in db level so better check and  clean up orphan jobs from database to free up system table space.




1.For now we don’t have export job is running but as checked found oracle orphaned datapump job ,  in other database not found like this
SQL>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;

-- output for refrence
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
SYS SYS_EXPORT_FULL_01 EXPORT                        FULL                          NOT RUNNING 0

select count(*) from sys.SYS_EXPORT_FULL_01
8535

2. So, we have to cleanup orphaned datapump jobs from DBA_DATAPUMP_JOBS by drop:

SQL> DROP TABLE SYS.SYS_EXPORT_FULL_01;   ==> make sure current table-name.



select sum(bytes/1024/1024) from dba_segments where segment_name = 'SYS_EXPORT_FULL_01' and owner = 'SYS'
3MB

select owner,created,object_name,last_ddl_time from dba_objects where object_name = 'SYS_EXPORT_FULL_01'
OWNER CREATED OBJECT_NAME LAST_DDL_TIME
SYS 12/23/2015 17:13 SYS_EXPORT_FULL_01 12/23/2015 17:13

SELECT o.status, o.object_id, o.object_type,        o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j  WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
VALID 26326 TABLE SYS.SYS_EXPORT_FULL_01