Welcome to My Oracle World: September 2023

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

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

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

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

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="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

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

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="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>

Welcome to My Oracle World

01 September, 2023

Useful Scripts

Useful Scripts