Tuesday 4 June 2013

How to cleanup orphaned datapump jobs from DBA_DATAPUMP_JOBS

In many cases you sometimes stop data pump job or in case of an abnormal end of the Data Pump job (the orphaned job) or using undocumented parameter KEEP_MASTER=Y, the master table remain in the database.

Though this topic is related to cleanup orphaned datapump jobs. But it is good to know several things before doing cleanup jobs.

1) You can check the orphaned data pump from the state column of the view dba_datapump_jobs and DBA_DATAPUMP_JOBS is based on gv$datapump_job, obj$, com$, and user$. Orphaned Data Pump jobs do not have an impact on new Data Pump jobs. If a new Data Pump job is started, a new entry will be created, which has no relation to the old Data Pump jobs.

2) For a new data pump job without any job name it is used a system generated name. From the dba_datapump_jobs it is checked for existing data pump jobs and then obtain a unique new system generated jobname.

3) Data pump jobs are different from DBMS_JOBS and they are maintained differently. Jobs created with DBMS_JOBS use there own processes. Data Pump jobs use a master process and worker process(es).

4) If you drop the master table while doing the data pump export or data pump import operation then the scenario is discussed below.

In case of export if you drop data pump export operation then export process will abort.

In case of import if you drop data pump import operation then import process will abort while it leads an incomplete import.

If the data pump job is completed and master table exist (a common if you do export operation with KEEP_MASTER=y) then it is safe to drop the master table.

Step by step cleanup orphaned datapump jobs is discussed below.

Step 01: Check the orphaned datapump jobs.
sqlplus / as sysdba
SET lines 140
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12 
COL operation LIKE owner_name
COL job_mode LIKE owner_name
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE        ATTACHED_SESSIONS
---------- -------------------- ---------- ---------- ------------ -----------------
ARJU       SYS_EXPORT_SCHEMA_01 EXPORT     SCHEMA     NOT RUNNING                  0
ARJU       SYS_EXPORT_SCHEMA_02 EXPORT     SCHEMA     NOT RUNNING                  0

Step 02: Check the state field. For orphaned jobs the state will be NOT RUNNING. So from the output we can say both are orphaned jobs.

Step 03: Drop the master table.
DROP TABLE ARJU.SYS_EXPORT_SCHEMA_01;
DROP TABLE ARJU.SYS_EXPORT_SCHEMA_02;

Step 04: Check for existing data pump jobs by query issued in step 01. If objects are in recyclebin bin then purge the objects from the recyclebin.
SQL> SELECT owner_name, job_name, operation, job_mode,
   state, attached_sessions
   FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE        ATTACHED_SESSIONS
---------- -------------------- ---------- ---------- ------------ -----------------
ARJU       BIN$xMNQdACzQ6yl22kj EXPORT     SCHEMA     NOT RUNNING                  0
          9U0B8A==$0
ARJU       BIN$BmUy4r5MSX+ojxFk EXPORT     SCHEMA     NOT RUNNING                  0
          sw8ocg==$0

SQL> PURGE TABLE ARJU.SYS_EXPORT_SCHEMA_01;

Table purged.

SQL> PURGE TABLE ARJU.SYS_EXPORT_SCHEMA_02;

Table purged.

Check if there is any orphaned jobs again.
SQL> SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs;

no rows selected

Step 05: In this stage you did not get any orphaned jobs if the jobs have a master table. If there are still jobs listed in dba_datapump_jobs do cleanup process like below.
SET serveroutput on
SET lines 100
DECLARE
  job1 NUMBER;
BEGIN
  job1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_01','ARJU');
  DBMS_DATAPUMP.STOP_JOB (job1);
END;
/

DECLARE
  job2 NUMBER;
BEGIN
  job2 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_02','ARJU');
  DBMS_DATAPUMP.STOP_JOB (job2);
END;
/

Friday 8 March 2013

Alter Materialized View ( for start time)

Note: Start Time was 9:20AM, but to 11AM.

declare
  no_primary_key exception;
  PRAGMA EXCEPTION_INIT (no_primary_key, -12014);
begin
  -- Oracle does not make it easy to reliably determine if a MV is
  -- created with the PRIMARY KEY clause or not.  So we're going to
  -- assume it does, but if that fails, we'll try again without it.
  execute immediate 'ALTER MATERIALIZED VIEW Schema_name.mv_name
 REFRESH FORCE
 ON DEMAND
 START WITH TO_DATE(''08-Mar-2013 11:00:00'',''dd-mon-yyyy hh24:mi:ss'')
 NEXT SYSDATE + 1/24
 WITH PRIMARY KEY';
exception
  when no_primary_key then
    execute immediate 'ALTER MATERIALIZED VIEW schema_name.mv_name
REFRESH FORCE
 ON DEMAND
 START WITH TO_DATE(''08-Mar-2013 11:00:00'',''dd-mon-yyyy hh24:mi:ss'')
 NEXT SYSDATE + 1/24';
  when others then
    raise;
end;

Friday 1 March 2013

Query running more than 60 minutes

select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s
join v$sqltext_with_newlines q
on s.sql_address = q.address
 where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by sid,serial#,q.piece;

QUERY for finding long operations (e.g. full table scans).

COLUMN percent FORMAT 999.99

SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1
/

QUERY will shows locks.

select
  object_name,
  object_type,
  session_id,
  type,   -- Type or system/user lock
  lmode,     -- lock mode in which session holds lock
  request,
  block,
  ctime   -- Time since current mode was granted
from
  v$locked_object, all_objects, v$lock
where
  v$locked_object.object_id = all_objects.object_id AND
  v$lock.id1 = all_objects.object_id AND
  v$lock.sid = v$locked_object.session_id
order by
  session_id, ctime desc, object_name
/

This QUERY shows SQL that is currently "ACTIVE":-

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
/

move table from one tablespace to another tablespace

alter table <table> move tablespace <Tablespace_name>;