Troubleshooting datapump issues

 How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1)


Data Pump Recommended Proactive Patches For 19.10 and Above (Doc ID 2819284.1)



2) SRDC - Diagnostic Collection for DataPump Export Performance Issues (Doc ID 1935743.1)


i) provide full expdp command and expdp.log

ii) At the point of slow or hang

Enable the event 10046 level 12 tracing for the DataPump Master (DM) and Worker (DW) processes (for versions >= 11g):
SQL> connect / as sysdba

-- Versions >= 11g and < 12c
SQL> alter system set events 'sql_trace {process : pname = dw | pname = dm} level=12';

-- Version = 12c or higher
SQL> alter system set events 'sql_trace {process: pname = dw | process: pname = dm} level=12';

Then start expdp with parameter EXCLUDE=STATISTICS METRICS=Y TRACE=480301 added to command line.

To disable the tracing perform:

SQL> connect / as sysdba

-- Versions >= 11g and < 12c
SQL> alter system set events 'sql_trace {process : pname = dw | pname = dm} off';

-- Version = 12c or higher
SQL> alter system set events 'sql_trace {process: pname = dw | process: pname = dm} off';

 

iii) upload the trace r DM<xx> and DW<xx> processes traces

Better upload the tfprof of the above trace files

$ tkprof <DW_TRACE_FILE>.trc <DW_OUTPUT_FILE>.out waits=y sort=exeela

obtain the DataPump process information:
SQL> connect / as sysdba
SQL> set lines 150 pages 100 numwidth 7
SQL> col program for a38
SQL> col username for a10
SQL> col spid for a7
SQL> 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%');

Identify the Data Pump Worker SID and SERIAL# (i.e. for DM<xx> and DW<xx> processes).


iv) Run the script srdc_expdp_performance.sql during the problem period.

   Download the attached srdc_expdp_performance.sql script.
   Connect as SYSDBA and run the script during the problem time:

e.g:
SQL> connect / as sysdba
SQL> @srdc_expdp_performance.sql
--wait for 10 minutes
SQL> @srdc_expdp_performance.sql
--wait for 10 minutes
SQL> @srdc_expdp_performance.sql

 

v) During hang , provide


sqlplus '/ as sysdba'
oradebug setorapname diaghang    
oradebug  unlimit
oradebug -g all hanganalyze 3
exec dbms_lock.sleep(60);
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 266
exec dbms_lock.sleep(60);
oradebug -g all dump systemstate 266
oradebug tracefile_name
exit

upload alert.log and tracefile_name



select  s.inst_id,s.sid,s.serial#,d.JOB_NAME,d.OWNER_NAME  from gv$session s,dba_datapump_sessions d where s.saddr = d.saddr ;


ed_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1, 2;


Please run the below statements and share the output

CONNECT / as sysdba
SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE state
COL job_mode LIKE state
COL owner.object for a50

-- locate Data Pump jobs:

SELECT owner_name, job_name, rtrim(operation) "OPERATION",
      rtrim(job_mode) "JOB_MODE", state, attached_sessions
 FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;

COL owner.object FORMAT a50

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;




########################


script as per 1935743.1


$ TFA_HOME/bin/tfactl diagcollect -srdc dbexpdpperf


srdc_expdp_performance.sql


REM srdc_expdp_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 a50

Column job_name format a35

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 a16

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 a50

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 '==================================Determine sid, serial# details for the active DataPump export process(es):=====================================' 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 HEADING OFF 

set feedback off

SELECT '=================================================================================================================================================' FROM dual;



--====================Retrieve sid, serial#, PGA details for the active export process(es)===========================

SET HEADING OFF 

SELECT '==================================Determine sid, serial#, PGA details for the active export process(es):=========================================' 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 HEADING OFF 

set feedback off

SELECT '=================================================================================================================================================' FROM dual;



--====================Retrieve the number of objects per object_type===========================

SET HEADING OFF 

SELECT '==================================Determine the number of objects per object_type:===============================================================' 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;

SET HEADING OFF 

SELECT '=================================================================================================================================================' FROM dual;



--====================Retrive all wait events and time in wait for the running export process(es)====================

SET HEADING OFF

SELECT '==================================Retrive all wait events and time in wait for the running export process(es):===================================' FROM dual;

SET HEADING ON

set feedback 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;

SET HEADING OFF 

set feedback off

SELECT '=================================================================================================================================================' FROM dual;



--====================Export progress - retrieve current sql id and statement====================

SET HEADING OFF

SELECT '==================================Export progress - retrieve current sql id and statement:=======================================================' FROM dual;

SET HEADING ON

set feedback on

select distinct 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 

set feedback off

SELECT '=================================================================================================================================================' FROM dual;


SET HEADING OFF MARKUP HTML OFF

SET SERVEROUTPUT ON FORMAT WRAP


declare

  table_view_not_exists EXCEPTION; 

  PRAGMA EXCEPTION_INIT(table_view_not_exists, -00942);

  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;

  CURSOR c_count is select count(*) from DBA_OPTSTAT_OPERATIONS where operation in ('gather_dictionary_stats','gather_fixed_objects_stats')

or (operation in ('gather_schema_stats') and target in ('SYS','SYSTEM'));

  CURSOR c_stats is select operation,target, START_TIME, END_TIME from DBA_OPTSTAT_OPERATIONS  

         where operation in ('gather_dictionary_stats','gather_fixed_objects_stats') or (operation in ('gather_schema_stats') and target in ('SYS','SYSTEM')) order by 3 desc;

  CURSOR c_sql_patches_count is select count(*) FROM DBA_REGISTRY_SQLPATCH; 

  CURSOR c_sql_patches IS SELECT PATCH_ID,PATCH_UID,ACTION,STATUS,DESCRIPTION,ACTION_TIME FROM DBA_REGISTRY_SQLPATCH order by ACTION_TIME desc;

  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;

  v_count number;

  v_sql_patches c_sql_patches%ROWTYPE;

  v_sql_patches_count NUMBER;

  v_is_AI number :=0;

  TYPE sessionwait_info_record IS RECORD

  (

  WAITING_SESSION NUMBER,

  EVENT VARCHAR2(64),

  DP_WAITTIME NUMBER,

  DP_SECONDS_IN_WAIT NUMBER,

  DP_STATE_IN_WAIT VARCHAR2(19),

  DP_P1TEXT VARCHAR2(64),

  DP_P1 NUMBER,

  DP_P2TEXT VARCHAR2(64),

  DP_P2 NUMBER,

  DP_P3TEXT VARCHAR2(64),

  DP_P3 NUMBER,

  CON_ID NUMBER

);

  TYPE process_info_record IS RECORD

  (

  CUR_DATE VARCHAR2(19),

  PROGRAM VARCHAR2(84),

  SESSIONID NUMBER,

  STATUS  VARCHAR2(8),

  USERNAME VARCHAR2(128),

  JOBNAME VARCHAR2(128),

  SPID  VARCHAR2(24),

  PROCESSID NUMBER,

  CON_ID NUMBER

);

type sessionwait_info_table is table of sessionwait_info_record;

SESSION_COLLECTOR sessionwait_info_table;

type process_table is table of process_info_record;

PROCESS_COLLECTOR process_table;

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('==================================Check v$session_longops - Export pending work:=================================================================');

  dbms_output.put_line(chr(10));

  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 ('=================================================================================================================================================');

  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 ('================================================================================================================================================='); 

    dbms_output.put_line (chr (10)); 

  end If;


    DBMS_OUTPUT.PUT_LINE ('===========================Have Dictionary,Fixed Objects or Schema statistics for SYS and SYSTEM users been gathered?============================'); 

  dbms_output.put_line (chr (10));

  open c_count;

    fetch c_count into v_count;

    if v_count>0 then

      BEGIN

        DBMS_OUTPUT.PUT_LINE (rpad ('OPERATION', 30)||' '||rpad ('TARGET', 8)||' '||rpad ('START_TIME', 32)||'   '||rpad ('END_TIME', 32));

        DBMS_OUTPUT.PUT_LINE (rpad ('--------------------------', 30)||' '||rpad ('---------', 8)||' '||rpad ('-----------------------------', 32)||'   '||rpad ('-----------------------------', 32));

        FOR v_stats IN c_stats LOOP 

          DBMS_OUTPUT.PUT_LINE (rpad (v_stats.operation, 30)||' '||rpad (nvl(v_stats.target,'         '), 8)||' '||rpad (v_stats.start_time, 32)||'   '||rpad (v_stats.end_time, 32)); 

        END LOOP;

      end;   

    else

      DBMS_OUTPUT.PUT_LINE ('Dictionary,fixed objects or Schema statistics for SYS and SYSTEM users have not been gathered in this database.'); 

    END IF; 

    dbms_output.put_line ('=================================================================================================================================================');

    dbms_output.put_line (chr (10));


  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('==================================Is the '||upper (v_ksppinm)||' parameter set?');

     dbms_output.put_line(chr(10));

    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 ('=================================================================SQL database patches============================================================'); 

  open c_sql_patches_count;

  fetch c_sql_patches_count into v_sql_patches_count;

  if v_sql_patches_count>0 then

    BEGIN

      DBMS_OUTPUT.PUT_LINE (rpad ('PATCH_ID', 10)||' '||rpad ('PATCH_UID', 10)||' '||rpad ('ACTION', 14)||' '||rpad ('STATUS', 15)||' '||rpad ('DESCRIPTION', 62)||' '||rpad ('ACTION_TIME', 29));

      DBMS_OUTPUT.PUT_LINE (rpad ('----------', 10)||' '||rpad ('----------', 10)||' '||rpad ('---------------', 14)||' '||rpad ('----------------', 15)||' '||rpad ('-----------------------------------------------------------------', 62)||' '||rpad ('-------------------------------', 29));

      FOR v_sql_patches IN c_sql_patches LOOP 

        DBMS_OUTPUT.PUT_LINE (rpad (v_sql_patches.PATCH_ID, 10)||' '||rpad (v_sql_patches.PATCH_UID, 10)||' '||rpad (v_sql_patches.ACTION, 14)||' '||rpad (v_sql_patches.STATUS, 15)||' '||rpad (v_sql_patches.DESCRIPTION, 62)||' '||rpad (v_sql_patches.ACTION_TIME, 29)); 

      END LOOP;

    end;   

  else

    dbms_output.put_line (chr (10));

    DBMS_OUTPUT.PUT_LINE ('There are no SQL patches installed in this database');     

  END IF; 

  DBMS_OUTPUT.PUT_LINE ('================================================================================================================================================='); 

  dbms_output.put_line(chr (10));


  stmt:='select count(*) from v$version where banner like ''%23ai%''';

  execute immediate stmt into v_is_AI;

  if (v_is_AI > 0) then      

   dbms_output.put_line('====================================Check v$datapump_sessionwait_info - Export waits for each session attached===================================');

   dbms_output.put_line (chr (10));

   v_count:=0;

   DBMS_OUTPUT.PUT_LINE (rpad ('SESS|ID', 7)||' '||rpad ('EVENT', 23)||' '||rpad ('DP_WAITTIME', 12)||' '||rpad ('DP_SECONDS_IN_WAIT', 19)||' '||rpad ('DP_STATE_IN_WAIT', 17)||' '||rpad ('DP_P1TEXT', 12)||' '||rpad ('DP_P1', 12)||' '||rpad ('DP_P2TEXT', 12)||' '||rpad ('DP_P2', 12)||' '||rpad ('DP_P3TEXT', 18)||' '||rpad ('DP_P3', 12)||' '||rpad ('CON_ID', 6));

      DBMS_OUTPUT.PUT_LINE (rpad ('--------', 7)||' '||rpad ('-----------------------', 23)||' '||rpad ('---------------', 12)||' '||rpad ('-------------------', 19)||' '||rpad ('-----------------', 17)||' '||rpad ('-------------', 12)||' '||rpad ('-------------', 12)||' '||rpad ('-------------', 12)||' '||rpad ('-------------', 12)||' '||rpad ('-----------------------', 18)||' '||rpad ('-------------', 12)||' '||rpad ('-------', 6));  

   END if;

   EXECUTE IMMEDIATE  'select WAITING_SESSION,EVENT,DP_WAITTIME,DP_SECONDS_IN_WAIT,DP_STATE_IN_WAIT,DP_P1TEXT,DP_P1,DP_P2TEXT,DP_P2,DP_P3TEXT,DP_P3,CON_ID from V$DATAPUMP_SESSIONWAIT_INFO' BULK COLLECT INTO SESSION_COLLECTOR;    

   for indx in 1..SESSION_COLLECTOR.count loop

     dbms_output.put_line (chr (10));

     dbms_output.put_line(rpad(SESSION_COLLECTOR(indx).WAITING_SESSION,7) || ' ' || rpad(SESSION_COLLECTOR(indx).EVENT,23) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_WAITTIME,12) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_SECONDS_IN_WAIT,19) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_STATE_IN_WAIT,17) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_P1TEXT,12) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_P1,12) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_P2TEXT,12) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_P2,12) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_P3TEXT,18) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_P3,12) || ' ' || rpad(SESSION_COLLECTOR(indx).CON_ID,6));

     v_count:=1;

   end loop;


   if (v_count=0) then

    dbms_output.put_line('No sessions attached found');

    dbms_output.put_line (chr (10)); 

   END if;

  dbms_output.put_line ('=================================================================================================================================================');

  dbms_output.put_line (chr (10));


  dbms_output.put_line('=======================================Check v$datapump_process_info - Display information for each process======================================');

  dbms_output.put_line (chr (10));

  v_count:=0;

  DBMS_OUTPUT.PUT_LINE (rpad ('CUR_DATE', 20)||' '||rpad ('PROGRAM', 40)||' '||rpad ('SESSIONID', 9)||' '||rpad ('STATUS', 8)||' '||rpad ('USERNAME', 9)||' '||rpad ('JOBNAME', 25)||' '||rpad ('SPID', 9)||' '||rpad ('PROCESSID', 9)||' '||rpad ('CON_ID', 6));

  DBMS_OUTPUT.PUT_LINE (rpad ('-------------------', 20)||' '||rpad ('----------------------------------------', 40)||' '||rpad ('----------', 9)||' '||rpad ('---------', 8)||' '||rpad ('----------', 9)||' '||rpad ('-------------------------', 25)||' '||rpad ('----------', 9)||' '||rpad ('----------', 9)||' '||rpad ('-------', 6));

   EXECUTE IMMEDIATE  'select CUR_DATE,PROGRAM,SESSIONID,STATUS,USERNAME,JOBNAME,SPID,PROCESSID,CON_ID from V$DATAPUMP_PROCESS_INFO' BULK COLLECT INTO PROCESS_COLLECTOR;

   for indx in 1..PROCESS_COLLECTOR.count loop

     dbms_output.put_line (chr (10));

     dbms_output.put_line(rpad (PROCESS_COLLECTOR(indx).CUR_DATE,20) || ' ' || rpad (PROCESS_COLLECTOR(indx).PROGRAM,40) || ' ' || rpad (PROCESS_COLLECTOR(indx).SESSIONID,9) || ' ' || rpad (PROCESS_COLLECTOR(indx).STATUS,8) || ' ' || rpad (PROCESS_COLLECTOR(indx).USERNAME,9) || ' ' || rpad (PROCESS_COLLECTOR(indx).JOBNAME,25) || ' ' || rpad (PROCESS_COLLECTOR(indx).SPID,9) || ' ' || rpad (PROCESS_COLLECTOR(indx).PROCESSID,9) || ' ' || rpad (PROCESS_COLLECTOR(indx).CON_ID,6));

     v_count:=1;

   end loop;

   if (v_count=0) then

    dbms_output.put_line('No proceses related to Datapump found');

    dbms_output.put_line (chr (10));

   END if;

  dbms_output.put_line ('=================================================================================================================================================');

  dbms_output.put_line (chr (10));

  EXCEPTION

     WHEN table_view_not_exists THEN

     null;      

end;

/



set feedback off

begin

  DBMS_OUTPUT.PUT_LINE ('====================================Current instance parameter values relevant for DataPump Export(EXPDP) issues================================='); 

end;

/


set feedback on

SET HEADING ON

set linesize 150

set pagesize 120

COL name FORMAT A40

COL value FORMAT A30

SELECT n.ksppinm name, v.KSPPSTVL value FROM x$ksppi n, x$ksppsv v

WHERE n.indx = v.indx AND n.ksppinm IN ('aq_tm_processes','__streams_pool_size','streams_pool_size','sga_target','memory_target','memory_size') 

ORDER BY 1;


set feedback off

SET HEADING OFF

SET LINESIZE 256

begin

  dbms_output.put_line ('=================================================================================================================================================');

  DBMS_OUTPUT.PUT_LINE('</pre>');

end;

/


spool off

PROMPT

PROMPT

PROMPT REPORT GENERATED : &SRDCSPOOLNAME..htm


exit





Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more