To Find session details using SID.
set verify off
col sid format 99999
col machine format a10
col program format a25 trunc
col username format a10
col logontime format a15
col osuser format a10 trunc
col proginfo format a30 trunc
accept trgtsid number default 0 prompt 'What is the SID : '
select to_char(s.logon_time,'mm/dd hh24:mi:ss') logontime,
s.sid,s.serial#,s.status,s.type,s.username,s.osuser,s.machine,
s.module || ' - ' || s.program proginfo,
s.process,p.spid, s.sql_hash_value
from v$session s, v$process p
where sid = &trgtsid
and p.addr = s.paddr;
To Find the process details
set echo off
set serveroutput on size 999999
set verify off
set feedback off
accept uxproc prompt 'Enter Unix process id: '
DECLARE
v_sid number;
vs_cnt number;
s sys.v_$session%ROWTYPE;
p sys.v_$process%ROWTYPE;
cursor cur_c1 is select sid from sys.v_$process p, sys.v_$session s where p.addr = s.paddr and (p.spid = &uxproc or s.process = '&uxproc');
BEGIN
dbms_output.put_line('=====================================================================');
select nvl(count(sid),0) into vs_cnt from sys.v_$process p, sys.v_$session s where p.addr = s.paddr and (p.spid = &uxproc or s.process = '&uxproc');
dbms_output.put_line(to_char(vs_cnt)||' sessions were found with '||'&uxproc'||' as their unix process id.');
dbms_output.put_line('=====================================================================');
open cur_c1;
LOOP
FETCH cur_c1 INTO v_sid;
EXIT WHEN (cur_c1%NOTFOUND);
select * into s from sys.v_$session where sid = v_sid;
select * into p from sys.v_$process where addr = s.paddr;
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Details : '|| s.action||' - '||s.module);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '99990.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.v_$sqltext where HASH_VALUE = s.sql_hash_value order by piece)
loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.v_$sqltext where HASH_VALUE = s.prev_hash_value order by piece)
loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.v_$session_wait where sid = s.sid)
loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop
-- dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
-- end loop;
dbms_output.put_line('Locks:');
for c1 in ( select /*+ RULE */ decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.v_$lock l, sys.obj$ o
where sid = s.sid
and l.id1 = o.obj#(+) )
loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;
dbms_output.put_line('=====================================================================');
END LOOP;
dbms_output.put_line(to_char(vs_cnt)||' sessions were found with '||'&uxproc'||' as their unix process id.');
dbms_output.put_line('Please scroll up to see details of all the sessions.');
dbms_output.put_line('=====================================================================');
close cur_c1;
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&uxproc!!!');
dbms_output.put_line('=====================================================================');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
END;
/
undef uxproc
set heading on
set verify on
set feedback on
set echo on
To find currently running requests details.
set pages 300
set lines 300
col os form A7 head AppProc
col spid form a6 head DBProc
col program form A43 trunc
set pages 38
col time form 9999.99 head Elapsed
col "Req Id" form 9999999999
col "Parent" form a8
col "Prg Id" form 999999
col qname head "Concurrent Manager Queue" format a25 trunc
col sid format 99999 head SID
col Parent for a9
set recsep off
select q.concurrent_queue_name || ' - ' || target_node qname
,a.request_id "Req Id"
,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
,a.concurrent_program_id "Prg Id"
,a.phase_code,a.status_code
,b.os_process_id "OS"
,vs.sid
,vp.spid
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||
c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,v$session vs
,v$process vp
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.program_application_id = c.application_id
and c2.concurrent_program_id = c.concurrent_program_id
and a.phase_code in ('I','P','R','T')
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and c2.language = 'US'
and vs.process (+) = b.os_process_id
and vs.paddr = vp.addr (+)
order by 1,2
/
CON_AVG_MIN_MAX
set linesize 200
col username for a10
col status for a10
col phase for a10
col PNAME for a70
col request_id for 99999999999
col PNAME for a40
select
b.user_name username,
a.USER_CONCURRENT_PROGRAM_NAME as PNAME,
avg((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) avg_Hrs_running,
max((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Max_Hrs_running,
min((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Min_Hrs_running
from
apps.fnd_conc_req_summary_v a,
apps.fnd_user b
where
phase_code = 'C' and status_code in ('C','E','X','G') and
a.REQUESTED_START_DATE >= sysdate-10 and
upper(a.USER_CONCURRENT_PROGRAM_NAME) like upper('%Interface%Trip%Stop%') and
a.REQUESTED_BY=b.user_id
group by b.user_name,a.USER_CONCURRENT_PROGRAM_NAME;
GetOrclID.sh
DT=$(date +"%d%m%Y")
if [ -d $DT ];then
mv $DT $DT'_BKP'
mkdir $DT
else
mkdir $DT
fi
cat /dev/null > ldapcmd
chmod +x ldapcmd
cat /dev/null > UserGuid.txt
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/RemoveGuid.sh
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/RemoveSub.sh
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/DrpUser.sh
BSPTH='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/'
RmGuidFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveGuid.sh'
RmSubsFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveSubs.sh'
RmUserFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveUser.sh'
cat /dev/null > $RmGuidFL
cat /dev/null > $RmSubsFL
cat /dev/null > $RmUserFL
while read line
do
echo 'ldapsearch -h hostname.domain.com -p 1389 -D "cn=orcladmin" -w xxxxxx -b "cn='$line',ou=na,cn=users,dc=corp,dc=net" -s sub "objectclass=*" orclguid' > ldapcmd
oguid=`ldapcmd |grep orclguid|cut -d '=' -f2`
echo $oguid >> UserGuid.txt
echo xxxxxx|ldifwrite connect="oiddb" basedn="cn=$line,ou=na,cn=users,dc=corp,dc=net" ldiffile="$BSPTH$line.ldif"
echo 'echo xxxxxx|bulkdelete connect=oiddb basedn="orclOwnerGUID='$oguid',cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,orclApplicationCommonName=EBIZ_PRD,cn=EBusiness,cn=Products,cn=OracleContext,dc=corp,dc=net"' >> $RmGuidFL
echo 'echo xxxxxx|bulkdelete connect=oiddb basedn="cn='${line}',ou=na,cn=users,dc=corp,dc=net"' >> $RmUserFL
echo "dn:cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,orclApplicationCommonName=EBIZ_PRD,cn=EBusiness,cn=Products,cn=OracleContext,dc=corp,dc=net" > $BSPTH$line'_SubRemove.ldif'
echo "changetype: modify" >> $BSPTH$line'_SubRemove.ldif'
echo "delete: uniquemember" >> $BSPTH$line'_SubRemove.ldif'
echo "uniquemember: cn=${line},ou=na,cn=users,dc=corp,dc=net" >> $BSPTH$line'_SubRemove.ldif'
echo '$ORACLE_HOME/bin/ldapmodify -h hostname1.domain.com -p 1389 -D cn=orcladmin -w xxxxxx -v -f '$line'_SubRemove.ldif;' >> $RmSubsFL
# done < /app/orclas/admin/scripts/users/UserOrclID.txt
done < $1
# done < /app/orclas/admin/scripts/users/UserOrclID1.txt
OID Scripts
applmgr@hostname:/app/orclas/admin/scripts/fix_users> cat searchOIDuser.sh
ldapsearch -h hostname.domin.com -p 1389 -D "cn=orcladmin" -w xxxxxx "cn=$1"
applmgr@hostname:/app/orclas/admin/scripts/fix_users> cat searchusrAD.sh
ldapsearch -h hostname.domin.com -p 3268 -D "svc-oracle-oid@domin.com" -w oracleOID1 -b "dc=corp,dc=net" -s sub "cn=$1" dn
applmgr@hostname:/app/orclas/admin/scripts/fix_users> cat findGUID.sh
ldapsearch -h hostname.domin.com -p 1389 -D "cn=orcladmin" -w xxxxxx -b "cn=$1,ou=na,cn=users,dc=corp,dc=net" -s sub "uid=*" orclguid
applmgr@hostname:/app/orclas/admin/scripts/fix_users> cat findSubscription.sh
ldapsearch -h hostname.domin.com -p 1389 -D cn=orcladmin -w xxxxxx -b "orclOwnerGUID=$1,cn=accounts,cn=subscription_data,cn=subscriptions,orclapplicationcommonname=EBIZ_PRD,cn=ebusiness,cn=products,cn=oraclecontext,dc=corp,dc=net" -s sub objectclass=* dn
applmgr@hostname:/app/orclas/admin/scripts/fix_users> cat findGUID_External.sh
ldapsearch -h hostname.domin.com -p 1389 -D "cn=orcladmin" -w xxxxxx "cn=$1" orclguid
applmgr@hostname:/app/orclas/admin/scripts/users/inconsUser> cat GetOrclID.sh
DT=$(date +"%d%m%Y")
if [ -d $DT ];then
mv $DT $DT'_BKP'
mkdir $DT
else
mkdir $DT
fi
cat /dev/null > ldapcmd
chmod +x ldapcmd
cat /dev/null > UserGuid.txt
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/RemoveGuid.sh
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/RemoveSub.sh
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/DrpUser.sh
BSPTH='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/'
RmGuidFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveGuid.sh'
RmSubsFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveSubs.sh'
RmUserFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveUser.sh'
cat /dev/null > $RmGuidFL
cat /dev/null > $RmSubsFL
cat /dev/null > $RmUserFL
while read line
do
echo 'ldapsearch -h hostname.domin.com -p 1389 -D "cn=orcladmin" -w xxxxxx -b "cn='$line',ou=na,cn=users,dc=corp,dc=net" -s sub "objectclass=*" orclguid' > ldapcmd
oguid=`ldapcmd |grep orclguid|cut -d '=' -f2`
echo $oguid >> UserGuid.txt
echo xxxxxx|ldifwrite connect="oiddb" basedn="cn=$line,ou=na,cn=users,dc=corp,dc=net" ldiffile="$BSPTH$line.ldif"
echo 'echo xxxxxx|bulkdelete connect=oiddb basedn="orclOwnerGUID='$oguid',cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,orclApplicationCommonName=EBIZ_PRD,cn=EBusiness,cn=Products,cn=OracleContext,dc=corp,dc=net"' >> $RmGuidFL
echo 'echo xxxxxx|bulkdelete connect=oiddb basedn="cn='${line}',ou=na,cn=users,dc=corp,dc=net"' >> $RmUserFL
echo "dn:cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,orclApplicationCommonName=EBIZ_PRD,cn=EBusiness,cn=Products,cn=OracleContext,dc=corp,dc=net" > $BSPTH$line'_SubRemove.ldif'
echo "changetype: modify" >> $BSPTH$line'_SubRemove.ldif'
echo "delete: uniquemember" >> $BSPTH$line'_SubRemove.ldif'
echo "uniquemember: cn=${line},ou=na,cn=users,dc=corp,dc=net" >> $BSPTH$line'_SubRemove.ldif'
echo '$ORACLE_HOME/bin/ldapmodify -h hostname.domin.com -p 1389 -D cn=orcladmin -w xxxxxx -v -f '$line'_SubRemove.ldif;' >> $RmSubsFL
# done < /app/orclas/admin/scripts/users/UserOrclID.txt
done < $1
# done < /app/orclas/admin/scripts/users/UserOrclID1.txt
applmgr@hostname:/app/orclas/admin/scripts/users/inconsUser>
applmgr@hostname:/app/orclas/admin/scripts/users/inconsUser> cat GetOrclID_ExtUsr.sh
DT=$(date +"%d%m%Y")
if [ -d $DT ];then
mv $DT $DT'_BKP'
mkdir $DT
else
mkdir $DT
fi
cat /dev/null > ldapcmd
chmod +x ldapcmd
cat /dev/null > UserGuid.txt
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/RemoveGuid.sh
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/RemoveSub.sh
cat /dev/null > /app/orclas/admin/scripts/users/inconsUser/04MAR16/DrpUser.sh
BSPTH='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/'
RmGuidFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveGuid.sh'
RmSubsFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveSubs.sh'
RmUserFL='/app/orclas/admin/scripts/users/inconsUser/'${DT}'/RemoveUser.sh'
cat /dev/null > $RmGuidFL
cat /dev/null > $RmSubsFL
cat /dev/null > $RmUserFL
while read line
do
echo 'ldapsearch -h hostname.domin.com -p 1389 -D "cn=orcladmin" -w xxxxxx -b "cn='$line',ou=na,cn=odcustomer,cn=odexternal,cn=users,dc=corp,dc=net" -s sub "objectclass=*" orclguid' > ldapcmd
oguid=`ldapcmd |grep orclguid|cut -d '=' -f2`
echo $oguid >> UserGuid.txt
echo xxxxxx|ldifwrite connect="oiddb" basedn="cn=$line,ou=na,cn=odcustomer,cn=odexternal,cn=users,dc=corp,dc=net" ldiffile="$BSPTH$line.ldif"
echo 'echo xxxxxx|bulkdelete connect=oiddb basedn="orclOwnerGUID='$oguid',cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,orclApplicationCommonName=EBIZ_PRD,cn=EBusiness,cn=Products,cn=OracleContext,dc=corp,dc=net"' >> $RmGuidFL
echo 'echo xxxxxx|bulkdelete connect=oiddb basedn="cn='${line}',ou=na,cn=odcustomer,cn=odexternal,cn=users,dc=corp,dc=net"' >> $RmUserFL
echo "dn:cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,orclApplicationCommonName=EBIZ_PRD,cn=EBusiness,cn=Products,cn=OracleContext,dc=corp,dc=net" > $BSPTH$line'_SubRemove.ldif'
echo "changetype: modify" >> $BSPTH$line'_SubRemove.ldif'
echo "delete: uniquemember" >> $BSPTH$line'_SubRemove.ldif'
echo "uniquemember: cn=${line},ou=na,cn=odcustomer,cn=odexternal,cn=users,dc=corp,dc=net" >> $BSPTH$line'_SubRemove.ldif'
echo '$ORACLE_HOME/bin/ldapmodify -h hostname.domin.com -p 1389 -D cn=orcladmin -w xxxxxx -v -f '$line'_SubRemove.ldif;' >> $RmSubsFL
# done < /app/orclas/admin/scripts/users/UserOrclID.txt
done < $1
# done < /app/orclas/admin/scripts/users/UserOrclID1.txt
applmgr@hostname:/app/orclas/admin/scripts/users/inconsUser>
applmgr@hostname:/app/orclas/admin/scripts/users/inconsUser> cat /app/ebs/appl_share/scripts/fix_user.sql
select user_guid from apps.fnd_user where user_name='&1';
declare
c fnd_sso_util.userCursor;
iter number:=0;
begin
open c for select * from fnd_user
where user_name ='&1' and user_guid is null;
fnd_sso_util.link_batch(c);
iter:=iter+1;
dbms_output.put_line('Users updated are: ' || iter);
close c;
if( iter=0) then
dbms_output.put_line(iter);
end if;
end;
/
applmgr@hostname:/app/orclas/admin/scripts/users/inconsUser>