Longops queries for datapump export/ Import /rman

DATAPUMP STATUS
---------------

 set pages 50000 lines 32767
col OPNAME for a10
col UNITS for a10
col TARGET_DESC for a10 
col OWNER_NAME for a10
col STATE for a10
col JOB_MODE for a10
col JOBNAME for a20
col MESSAGE for a30
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
select sid,serial#,sofar,totalwork,units,target_desc,start_time,last_update_time,
round(time_remaining/60,2) "REMAINING MINS",round(elapsed_seconds/60,2) "ELAPSED MINS",
round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS",
dp.owner_name,dp.state,dp.job_name,dp.job_mode,dp.workers,
message 
from gv$session_longops sl,gv$datapump_job dp 
where sl.opname=dp.job_name and sofar != totalwork
/

Currently Active DataPump Operations
---------------------------------------
set pages 9999 lines 300

TTITLE 'Currently Active DataPump Operations'
COL owner_name          FORMAT A06      HEADING 'Owner'
COL job_name            FORMAT A20      HEADING 'JobName'
COL operation           FORMAT A12      HEADING 'Operation'
COL job_mode            FORMAT A12      HEADING 'JobMode'
COL state               FORMAT A12      HEADING 'State'
COL degree              FORMAT 9999     HEADING 'Degr'
COL attached_sessions   FORMAT 9999     HEADING 'Sess'

SELECT
 owner_name
,job_name
,operation
,job_mode
,state
,degree
,attached_sessions
FROM dba_datapump_jobs
/

Currently Active DataPump Sessions
------------------------------------- 
set pages 9999 lines 300

TTITLE 'Currently Active DataPump Sessions'
COL owner_name          FORMAT A06      HEADING 'Owner'
COL job_name            FORMAT A20      HEADING 'Job'
COL osuser              FORMAT A12      HEADING 'UserID'

SELECT
 DPS.owner_name
,DPS.job_name
,S.osuser
,S.sid
,S.serial#
,S.status
FROM
 dba_datapump_sessions DPS
,v$session S
WHERE S.saddr = DPS.saddr
/

DBA_DATAPUMP_JOBS - This view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached.
-----------------
set pages 9999 lines 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 where job_name not like 'BIN$%' order by 1,2;
OWNER_NAME JOB_NAME               OPERATION  JOB_MODE   STATE         DEGREE    ATTACHED_SESSIONS
---------- ---------------------- ---------- ---------- ------------- --------- ----------------- 

DBA_DATAPUMP_SESSIONS - This view give gives the SADDR that assist in determining why a Data Pump session may be having problems.
---------------------
set pages 9999 lines 300

COL owner_name FORMAT a10
COL job_name FORMAT a20
  
select owner_name,job_name,saddr,sid,serial# from v$session s, dba_datapump_sessions d
where s.saddr = d.saddr;

EXPORT STATUS
-------------

$expdp attach=
username: / as sysdba
EXPDP>STATUS

If required, to kill EXPORT job
CTRL+C
EXPDP>ATTACH=SOURCE_TABLE_EXPORT
EXPDP>STATUS
EXPDP>STOP_JOB=IMMEDIATE (OR) KILL_JOB


(OR)
EXPORT STATUS
-------------
set pages 50000 lines 32767
col UNITS for a5
col TARGET_DESC for a11
col OWNER_NAME for a10
col STATE for a10
col JOB_MODE for a10
col JOB_NAME for a20
col MESSAGE for a30
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
select sid,serial#,username,opname,sofar,totalwork,units,target_desc,start_time,last_update_time,
round(time_remaining/60,2) "REMAINING MINS",round(elapsed_seconds/60,2) "ELAPSED MINS",
round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS",
dp.owner_name,dp.state,dp.job_name,dp.job_mode,dp.workers,
message
from gv$session_longops sl,gv$datapump_job dp
where sl.opname=dp.job_name and sofar != totalwork and target_desc='EXPORT'
/
(OR)

DBA_DATAPUMP_JOBS - This view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached.

set pages 50000 lines 32767
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 job_name not like 'BIN$%' order by 1,2;
OWNER_NAME JOB_NAME               OPERATION  JOB_MODE   STATE         DEGREE    ATTACHED_SESSIONS
---------- ---------------------- ---------- ---------- ------------- --------- ----------------- 

DBA_DATAPUMP_SESSIONS - This view give gives the SADDR that assist in determining why a Data Pump session may be having problems.

set pages 50000 lines 32767
COL owner_name FORMAT a10
COL job_name FORMAT a20
  
select owner_name,job_name,s.saddr,sid,serial# from v$session s, dba_datapump_sessions d
where s.saddr = d.saddr;

select owner_name, job_name, operation, job_mode, state from dba_datapump_jobs;

EXP
---
nohup exp userid=\'/ as sysdba \' file=SOURCE_TABLENAME_EXP.dmp log=SOURCE_TABLENAME_EXP.log tables=OWNER.TABLENAME &



IMPORT STATUS
------------
 set pages 50000 lines 32767
col OPNAME for a10
col UNITS for a5
col TARGET_DESC for a10 
col OWNER_NAME for a10
col STATE for a10
col JOB_MODE for a10
col JOB_NAME for a20
col MESSAGE for a30
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
select sid,serial#,sofar,totalwork,units,target_desc,start_time,last_update_time,
round(time_remaining/60,2) "REMAINING MINS",round(elapsed_seconds/60,2) "ELAPSED MINS",
round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS",
dp.owner_name,dp.state,dp.job_name,dp.job_mode,dp.workers,
message 
from gv$session_longops sl,gv$datapump_job dp 
where sl.opname=dp.job_name and sofar != totalwork
/

IMPORT SPEED
------------ 

set pages 50000 lines 32767
col TABLE_NAME for a80

SELECT SUBSTR(sql_text, INSTR(sql_text,'INTO "'),120) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM sys.v_$sqlarea
WHERE sql_text like 'insert %into "%' AND command_type = 2 AND open_versions > 0
/ 

(OR)

DBA_DATAPUMP_JOBS - This view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached.

set pages 50000 lines 32767

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 job_name not like 'BIN$%' order by 1,2;
OWNER_NAME JOB_NAME               OPERATION  JOB_MODE   STATE         DEGREE    ATTACHED_SESSIONS
---------- ---------------------- ---------- ---------- ------------- --------- ----------------- 

DBA_DATAPUMP_SESSIONS - This view give gives the SADDR that assist in determining why a Data Pump session may be having problems.

set pages 50000 lines 32767
COL owner_name FORMAT a10
COL job_name FORMAT a20
  
select owner_name,job_name,saddr,sid,serial# from v$session s, dba_datapump_sessions d
where s.saddr = d.saddr;

select owner_name, job_name, operation, job_mode, state from dba_datapump_jobs;

IMP
---
nohup imp userid=\'/ as sysdba \' file=SOURCE_TABLENAME_EXP.dmp log=IMPORT_SOURCE_TABLENAME_EXP.log tables=OWNER.TABLENAME IGNORE=y FROMUSER=OWNER TOUSER=OWNER &


Longops - RMAN
------------------------
set pages 50000 lines 32767

col USERNAME for a10
col OPNAME for a10
col TARGET_DESC for a10
col CONTEXT for a10
col MESSAGE for a30

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE",
TIME_REMAINING/60 REMAINING_MINS 
FROM GV$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0 AND SOFAR <> TOTALWORK
/
Longops - RMAN
------------------------
set pages 9999 lines 300
col status for a10
col object_type for a10

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

select SID,STATUS,START_TIME,END_TIME,OBJECT_TYPE,OUTPUT_DEVICE_TYPE
from v$rman_status order by START_TIME desc;

select max(START_TIME) "START_TIME", END_TIME,
ELAPSED_SECONDS/60 "ELAPSED_MINS",
OUTPUT_BYTES/1024/1024/1024 "OUTPUT_BYTES_GB",STATUS
from v$rman_backup_job_details
group by START_TIME,END_TIME,ELAPSED_SECONDS,OUTPUT_BYTES, STATUS
having max(START_TIME) > sysdate-1
/

Longops.sql

----------- 

set pages 50000 lines 32767

col OPNAME for a10

col SID form 9999

col SERIAL form 9999999

col PROGRAM for a10

col USERNAME for a10

col SQL_TEXT for a40

col START_TIME for a10

col LAST_UPDATE_TIME for a10

col TARGET for a25

col MESSAGE for a25alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

SELECT inst_id,sid, serial#, sql_id, opname, username, target, sofar, totalwork, start_time,last_update_time,round(time_remaining/60,2) "REMAIN MINS", round(elapsed_seconds/60,2) "ELAPSED MINS", round((time_remaining+elapsed_seconds)/60,2) "TOTAL MINS", ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE", message

FROM gv$session_longops 

WHERE OPNAME NOT LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND sofar<>totalwork AND time_remaining > 0 

/

Note:

Get the SID from v$session_longops and plug it into v$session to check the SQL command details.

Current Running SQLs

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

set pages 50000 lines 32767

col program format a40

col sql_text format a130

select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text from v$session b,v$sqlarea c

where b.sql_id=c.sql_id and b.sid='&sid'

/ 

set pages 50000 lines 32767

col USERNAME for a10

col OSUSER for a10

col MACHINE for a10

select s.sid,s.serial#,p.spid,s.username,s.osuser,s.status,s.process fg_pid,s.longon_time,s.machine,p.spid bg_pid from gv$session s,gv$process p where s.addr=p.addr and s.sid='&sid'

/

$ps -ef | grep

set pages 50000 lines 32767

SELECT INST_ID, SID, SERIAL#, SQL_ID,USERNAME, PROGRAM, MACHINE, SERVICE_NAME

FROM GV$SESSION

WHERE SID IN ('','')

/

Active Running SQLs

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

 set pages 50000 lines 32767

col SPID for a10

col PROGRAM for a15

col OSUSER for a10

col ACTION for a10

col EVENT for a25

col SQL_TEXT for a25

col MACHINE for a10

col P1TEXT for a10 

col P2TEXT for a10

col P3TEXT for a10  

SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine, b.type,

b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time

FROM v$process a, v$session b, v$sqltext c WHERE a.addr=b.paddr

AND b.sql_hash_value = c.hash_value

AND b.STATUS = 'ACTIVE'

AND b.sid = '&sid' ORDER BY a.spid, c.piece

/

OR - Use the below Query 

Longops.sql

----------- 

set pages 50000 lines 32767

col OPNAME for a10

col SID form 9999

col SERIAL form 9999999

col PROGRAM for a10

col USERNAME for a10

col SQL_TEXT for a40

col START_TIME for a10

col LAST_UPDATE_TIME for a10

col TARGET for a25

col MESSAGE for a25

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

SELECT l.inst_id,l.sid, l.serial#, l.sql_id, l.opname, l.username, l.target, l.sofar, l.totalwork, l.start_time,l.last_update_time,round(l.time_remaining/60,2) "REMAIN MINS", round(l.elapsed_seconds/60,2) "ELAPSED MINS", round((l.time_remaining+l.elapsed_seconds)/60,2) "TOTAL MINS", ROUND(l.SOFAR/l.TOTALWORK*100,2) "%_COMPLETE", l.message,s.sql_text 

FROM gv$session_longops l 

LEFT OUTER JOIN v$sql s on s.hash_value=l.sql_hash_value and s.address=l.sql_address and s.child_number=0

WHERE l.OPNAME NOT LIKE 'RMAN%' AND l.OPNAME NOT LIKE '%aggregate%' AND l.TOTALWORK != 0 AND l.sofar<>l.totalwork AND l.time_remaining > 0

/