Longops EXPORT

EXPORT STATUS
-------------
jobs
or
ps -ef| grep expdp or ps -ef| grep exp or ps -ef|grep <processid>
or
ls -lrth
tail -f nohup.out
tail -f SOURCE_TABLENAME_EXP.log

cat SOURCE_TABLENAME_EXP.log
(OR)

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

$expdp attach=<JOB_NAME>
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 &

Longops IMPORT

IMPORT STATUS
------------
jobs
or
ps -ef| grep impdp or ps -ef| grep imp or ps -ef|grep <processid>
or
ls -lrth
tail -f nohup.out
tail -f IMPORT_SOURCE_TABLENAME_EXP.log

cat  IMPORT_SOURCE_TABLENAME_EXP.log

(OR)

IMPORT STATUS
------------
 $impdp attach=<JOB_NAME>
username: / as sysdba
IMPDP>STATUS

If required, to kill IMPORT job

CTRL+C
IMPDP>ATTACH=SOURCE_TABLE_IMPORT
IMPDP>STATUS
IMPDP>STOP_JOB=IMMEDIATE (OR) KILL_JOB


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

oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon

Set the environment for the database
------------------------------------
uname

cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab

export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin

Database Details
----------------
sqlplus "/as sysdba"

set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME, INSTANCE_NAME, HOST_NAME, DATABASE_ROLE,
OPEN_MODE, version DB_VERSION, LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"
from v$database,gv$instance;

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 Query using v$session_longops

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

set pages 50000 lines 32767
SELECT INST_ID, SID, SERIAL#, SQL_ID,USERNAME, PROGRAM, MACHINE, SERVICE_NAME
FROM GV$SESSION
WHERE SID IN ('<SID_NO.1>','<SID_NO.2>')
/
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
/