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

/

How to find high CPU using sessions in Oracle Database

If the problem relates to CPU bound applications then CPU information for each session can be examined to determine the culprits. The v$sesstat  view can be queried to find high cpu using sessions and then SQL can be listed. 

1.Find the 'CPU used by this session' statistic.

SQL>SELECT name ,statistic# FROM v$statname WHERE  name LIKE '%CPU%session';

    NAME                                STATISTIC#

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

    CPU used by this session                    14

2. Then determine which session is using most of the cpu.

SQL>SELECT * FROM v$sesstat WHERE statistic# = 14;

           SID STATISTIC#      VALUE

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

             1         14          0

             2         14          0

             3         14          0

             4         14          0

             5         14          0

             6         14          0

             7         14          0

             8         14          0

             9         14          0

            10         14          0

            11         14          0

            12         14          0

            16         14       1930

3. Lookup details for the session which is using most of the cpu.

SQL>SELECT address ,SUBSTR(sql_text,1,20) Text, buffer_gets, executions,buffer_gets/executions AVG

    FROM   v$sqlarea a, v$session s

    WHERE  sid = 16 

    AND    s.sql_address = a.address

    AND    executions > 0

    ORDER BY 5;

4. Use v$sqltext to extract the whole SQL text.

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.sid = '&sid' ORDER BY a.spid, c.piece

/

5. Once the whole SQL statement has been identified it can be tuned. 

Explain the queries and examine their access paths.

Autotrace is a useful tool for examining access paths. 

Undo issue

In Undo Segments there are three types of extents, they are

Unexpired Extents – Undo data whose age is less than the undo retention period.
Expired Extents – Undo data whose age is greater than the undo retention period.
Active Extents – Undo data that is part of the active transaction.

-----------------------------------
Check the overall status for undos.
-----------------------------------

SQL> select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB
from dba_undo_extents group by tablespace_name, status;

TBS STATUS  GB

UNDO_TDS EXPIRED  5.95526123046875
UNDO_TDS UNEXPIRED 0.0001220703125

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

SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"  FROM v$undostat;

UNDO_BLK_PER_SEC
1241.68

----------------------
Undo Blocks per Second
----------------------


SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",       
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",       
       ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" 
FROM (       
      SELECT SUM(a.bytes) undo_size         
        FROM v$datafile a,v$tablespace b,dba_tablespaces c         
       WHERE c.contents = 'UNDO'         
         AND c.status = 'ONLINE'         
         AND b.name = c.tablespace_name           
         AND a.ts# = b.ts#       
      ) d,v$parameter e,v$parameter f,( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec         
                                          FROM v$undostat 
                                      ) g WHERE e.name = 'undo_retention'  AND f.name = 'db_block_size'

          
ACTUAL_UNDO_SIZE_MB  UNDO_RETENTION_SEC  OPTIMAL_UNDO_RETENTION_SEC
-------------------  ------------------  --------------------------
6108      900       630

----------------------
Optimal Undo Retention
----------------------


SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",       

ACTUAL_UNDO_SIZE_MB  UNDO_RETENTION_SEC<]" 
    FROM (        SELECT SUM(a.bytes) undo_size         
        FROM v$datafile a, v$tablespace b, dba_tablespaces c 
       WHERE c.contents = 'UNDO'         
 tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from 
SIZE_MB FREE_MB  NEEDED_UNDO_SIZE
-------------------  ------------------  -----------------
6108      900      8730.5625


------------------------------------------------------
Calculate Needed UNDO Size for given Database Activity
------------------------------------------------------


select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from  dba_data_files group by tablespace_name union all
 select  tablespace_name || '  **TEMP**'
 , sum(bytes)/1024/1024 used_mb
 from  dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
 from  dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/


TBS  SIZE_MB FREE_MB %USED
---  ------- ------- -----

UNDO_TDS 6108   10  99

1. What are the current UNDO settings (including UNDERSCORE settings)? 

SET PAGESIZE 900 
COL NAME FORMAT A32 
COL VALUE FORMAT A40 
SPOOL UNDOPARAMS.TXT 
/* Database identification */ 
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database; 
select * from V$version where banner like 'Oracle Database%'; 
/* Internal AUM settings */ 
select nam.ksppinm NAME, val.KSPPSTVL VALUE 
from x$ksppi nam, x$ksppsv val 
where nam.indx = val.indx and (nam.ksppinm like '%undo%' or nam.ksppinm like '_smu%' or nam.ksppinm in ('event', '_first_spare_parameter' ) ) 
order by 1; 
SPOOL OFF 

2. What are the various statuses for Undo Extents? 
SELECT DISTINCT STATUS, SUM(BYTES)/1024/0124 "BYTES (MB)", COUNT(*) 
FROM DBA_UNDO_EXTENTS 
GROUP BY STATUS; 

3. Please provide the TUNED_UNDORETENTION details from V$UNDOSTAT. 
Note that the nls_date_format is needed so that the time is displayed. 

ALTER SESSION SET nls_date_format = 'DD/MM/YYYY HH24:MI:SS'; 
SET PAGESIZE 900 
SET LINESIZE 255 
SPOOL UNDOSTAT.TXT 
/* Database identification */ 
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database; 
select * from V$version where banner like 'Oracle Database%'; 
/* Tuned Undo Retention */ 
SELECT MAX(TUNED_UNDORETENTION), MAX(MAXQUERYLEN), MAX(NOSPACEERRCNT), MAX(EXPSTEALCNT) FROM V$UNDOSTAT; 
SELECT BEGIN_TIME, END_TIME, TUNED_UNDORETENTION, MAXQUERYLEN, MAXQUERYID, NOSPACEERRCNT, EXPSTEALCNT, UNDOBLKS, TXNCOUNT FROM V$UNDOSTAT; 
SPOOL OFF 

4. For the UNDO TableSpace, what is the RETENTION GUARANTEE? 
SELECT tablespace_name, retention, min_extlen FROM dba_tablespaces WHERE contents = 'UNDO'; 

5. How much free space is in the UNDO TableSpace? 
SELECT SUM(BYTES)/1024/1024 "BYTES (MB)" FROM dba_free_space WHERE tablespace_name='&UNDOTBS'; 

6. For the Data Files used for Undo, what is the autoextend setting? 
COL AUTOEXTENSIBLE FORMAT A14 
SELECT FILE_ID, BYTES/1024/1024 AS "BYTES (MB)", MAXBYTES/1024/1024 AS "MAXBYTES (MB)", AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='&UNDOTBS'; 

7. set pagesize 50 
set linesize 100 
column UNXPSTEALCNT heading "# Unexpired|Stolen" 
column EXPSTEALCNT heading "# Expired|Reused" 
column SSOLDERRCNT heading "ORA-1555|Error" 
column NOSPACEERRCNT heading "Out-Of-space|Error" 
column MAXQUERYLEN heading "Max Query|Length" 
select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, 
UNXPSTEALCNT, EXPSTEALCNT , SSOLDERRCNT, NOSPACEERRCNT, MAXQUERYLEN, TUNED_UNDORETENTION 
from gv$undostat 
order by inst_id, begin_time;