Anuroop Challa

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 ...

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:     ...

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...

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...