1. Check session

1.1 Data pump session
 

-- List all active Data Pump jobs

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions

FROM dba_datapump_jobs

WHERE state = 'EXECUTING';

​

-- List all active sessions for Data Pump jobs

SELECT s.owner_name, s.job_name, s.saddr, p.sid, p.serial#

FROM dba_datapump_sessions s

JOIN v$session p ON s.saddr = p.saddr;

​

1.2 Sessions

​

​

col USERNAME format a50 

col MACHINE format a100 

col TYPE format a10 

col STATUS format a10 

set markup csv on quote off

select S.USERNAME,S.MACHINE,S.STATUS,DECODE(NETWORK_SERVICE_BANNER,null,'Secure','Un-Secure') type, COUNT(*),global_name

from

(select sid,network_service_banner

from v$session_connect_info

where network_service_banner is null

union

select sid,NETWORK_SERVICE_BANNER

from V$SESSION_CONNECT_INFO

where NETWORK_SERVICE_BANNER like '%TCP/IP%') SCI, global_name,

V$SESSION S

where S.sid=SCI.sid

and S.USERNAME is not null

group by S.USERNAME, S.MACHINE,S.STATUS, DECODE(NETWORK_SERVICE_BANNER,null,'Secure','Un-Secure'),global_name order by 1,2,3,4;

 

 

set markup csv on quote off

select global_name,username,machine,status,type,count(*) from v$session, global_name 

where username in (

select username from all_users

where oracle_maintained='N' and  NOT REGEXP_LIKE(username, '[0-9]') 

and username not in (PERFDBA'))

group by global_name,username,machine,status,type;

 

 

select username||' ~ '|| machine||' ~ '|| status ||' ~ '|| count(1)||' ~ '|| global_name from v$session,global_name where  type !='BACKGROUND'  group by username, machine, status,global_name order by     username,machine, status,count(1);

 

select username||' | '||machine||'| '||count(1)  from v$session

group by username||' | '||machine order by username||' | '||machine||'| '||count(1);

​

1.3 Kill sessions​

​

​

col USERNAME format a50 

col MACHINE format a100 

col TYPE format a10 

col STATUS format a10 

set markup csv on quote off

select 'alter system kill session '''||s.sid||','||s.serial#||''';'

from

(select sid,network_service_banner

from v$session_connect_info

where network_service_banner is null

union

select sid,NETWORK_SERVICE_BANNER

from V$SESSION_CONNECT_INFO

where NETWORK_SERVICE_BANNER like '%TCP/IP%') SCI, global_name,

V$SESSION S

where S.sid=SCI.sid

and S.USERNAME is not null

and DECODE(NETWORK_SERVICE_BANNER,null,'Secure','Un-Secure')='Secure';

​