5may24
; TyperTask will automate your keyboard when a "signal" is typed.
; For example, when you type "hth", it will auto type "Hope this helps!"
; The format of this file is:
;
; Trigger=Type This Text (one per line)
#old =cd /u01/app/oracle/R122hso_17May23{enter}
#x=cd /u01/app/oracle/R122hso_4Jul23/fs1/inst/apps/SAHFTST_alt-hso-saeba01/appl/admin{enter}
~nls1=alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';{enter}
#tune1= SET SERVEROUTPUT ON {enter}declare{enter}stmt_task VARCHAR2(40);{enter}begin{enter}stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '&a');{enter}DBMS_OUTPUT.put_line('task_id: ' || stmt_task );{enter}end; {enter}/{enter}
#tune2=begin{enter}DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => '&A');{enter}end; {enter}/{enter}
#tune3=set lines 1000 pages 1000 long 1000000{enter}column recommendations format a500{enter}SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&T1') AS recommendations FROM dual; {enter}
#adshowlog=sqlplus apps/apps4hsotest @$AD_TOP/sql/ADZDSHOWLOG.sql > output.txt {enter}
#cos=146.213.0.134
uato=cd /u01/apex/oracle/Middleware/APEX_HOME/user_projects/domains/uat_apex_domain/config/fmwconfig/components/OHS/instances/ohs1 {enter}
prodo=cd /u01/apex/oracle/Middleware/APEX_HOME/user_projects/domains/prod_apex_domain/config/fmwconfig/components/OHS/instances/ohs1 {enter}
#dev=appl4sadev{enter}
#tst=appl4satst{enter}
#test=apps4hsotest{enter}
#suorcl=sudo su - oracle{enter}
#sso1=SSO_LOGIN
#sso2=OAM_REMOTE_USER
#sahfqlogout=https://sauat.erp.sykehuspartner.no:8005/oamsso-bin/logout.pl?end_url=https://sauat.erp.sykehuspartner.no/
#sahfplogout=https://saprod.erp.sykehuspartner.no:8005/oamsso-bin/logout.pl?end_url=https://saprod.erp.sykehuspartner.no/
#regqlogout=https://uat.erp.sykehuspartner.no:8005/oamsso-bin/logout.pl?end_url=https://uat.erp.sykehuspartner.no/
#regplogout=https://prod.erp.sykehuspartner.no:8005/oamsso-bin/logout.pl?end_url=https://prod.erp.sykehuspartner.no/
#watch1=watch 'ls -l --time-style=full-iso'{enter}
~bash2=PS1='\u@\h:\w\$ ' {enter} clear {enter}
~tp=appl4hso{enter}
~bash1=cd {enter} clear{enter} . ./.bash_profile{enter}
;cl1=mkdir Archive && find . -maxdepth 1 -type f -mtime +100 -exec ls {} \; {enter}
;cl2=find . -maxdepth 1 -type f -mtime +100 -exec mv {} Archive \;{enter}
;cl3=cd Archive {enter}
;cl4=nohup zip logs_archive_30June2021.zip * & {enter}
;cl5=rm *.log* *.out* {enter}
dt1=_$(date +%F){enter}
dt2=_$(date +%F-%T){enter}
~pa=apps4hsoprd461{enter}
~exa1=exa06-clu2-scan.hso.cosng.net{tab}
~exa2=1521{tab}
~exa3=REPPROD.HSO.COSNG.NET{tab}
~exa4=rcuinstall{tab}
~exa5=rcuinstall123{tab}
~base=/u01/app/oracle/R122hso{enter}
~oasp=Prdlogic4hso{tab}
#tsid=SAHFTST
;#1=apps4saqa9211{enter}
;#2=pwd4sahfqa{enter}
;#3=SAHFQA{enter}
;#4=exa06-dbadm01vm04.hso.cosng.net{enter}
;#5=/u01/app/oracle/R122hso{enter}
;#6=2{enter}
;#7=3{enter}
~hpass=harm0104{enter}
#bb=IamTheWarriorKing66%{enter}
~uatpa=apps4hsouatp{enter}
~qq=pwd4ppost{enter}
#sapapps=apps4saprd461{enter}
; temp
~long1=set feed off long 999999999 {enter} col module for a30 {enter} col DURATION_HOURS for 99999.99 {enter} col STARTED_AT for a13 {enter} col "USERNAME| SID,SERIAL#" for a30 {enter} col "SQL_ID | SQL_TEXT" for a160 {enter} select username||'| '||sid ||','|| serial# "USERNAME| SID,SERIAL#",substr(MODULE,1,30) "MODULE", to_char(sysdate-last_call_et/24/60/60,'DD-MON HH24:MI') STARTED_AT, last_call_et/60/60 "DURATION_HOURS" ,SQL_ID ||' | '|| (select SQL_FULLTEXT from gv$sql where address=sql_address and CHILD_NUMBER=SQL_CHILD_NUMBER) "SQL_ID | SQL_TEXT" from gv$session where last_call_et > 50*60 and username is not null and module is not null and module not like 'backup%' and status = 'ACTIVE' order by "DURATION_HOURS" desc;{enter}
~long2=select a.time_remaining , a.* from gv$session_longops a where sid='&sid';
~instancestartime=COLUMN INSTANCE_NAME FOR A20 {enter} SELECT INSTANCE_NAME,TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') "STARTUP TIME" FROM GV$INSTANCE; {enter}
#wfhist=col Parameters for a15 {enter}col USER_CONCURRENT_PROGRAM_NAME for a30{enter} SELECT distinct t.user_concurrent_program_name, r.REQUEST_ID, to_char(r.ACTUAL_START_DATE,'dd-mm-yy hh24:mi:ss') "Started at", to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss') "Completed at", round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60)) "Time took MIN", decode(r.PHASE_CODE,'C','Completed','I','Inactive','P ','Pending','R','Running','NA') phasecode, decode(r.STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M', 'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status", R.ARGUMENT_TEXT "Parameters" FROM APPS.FND_CONCURRENT_REQUESTS R , apps.fnd_concurrent_programs_tl t, apps.fnd_user u, apps.fnd_conc_req_summary_v v WHERE R.ACTUAL_START_DATE >= (SYSDATE-2/24) and t.concurrent_program_id=r.concurrent_program_id AND r.REQUESTED_BY=u.user_id and V.REQUEST_ID=R.REQUEST_ID and t.user_concurrent_program_name like '%Workflow Background Process%' order by TO_CHAR(R.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss') DESC;{enter}
~useremail=col CLIENT_IDENTIFIER for a10{enter} col ACTION for a35{enter} col DESCRIPTION for a20{enter} col email_address for a40{enter} select a.description, a.email_address from fnd_user a where user_name ='&B';{enter}
~hsoa1=set linesize 250 {enter} col request_date format a15 {enter} col requested_start_date format a10 {enter} col actual_start_date format a20 {enter} col actual_completion_date format a20 {enter} col argument_text format a50 {enter} col Elapsed format 9999.99 {enter} col parent_request_id format 99999999 {enter} col ARGUMENT_TEXT for a20 {enter}
~hsoaf=select * from (select a.request_id, a.PARENT_REQUEST_ID as parent, DECODE(a.phase_code, 'C','Completed', 'I','Inactive', 'P','Pending', 'R','Running') || ' ' || DECODE(a.status_code, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'G','Warning', 'H','On Hold', 'I',' Normal', 'M','No Manager', 'P','Scheduled', 'Q','Standby', 'R',' Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z',' Waiting') "PHASE_STATUS " , a.actual_start_date, a.actual_completion_date , (nvl(actual_completion_date,sysdate) - actual_start_date)*1440 "Elapsed", a.argument_text ,a.USER_CONCURRENT_PROGRAM_NAME from apps.FND_CONC_REQ_SUMMARY_V a where a.concurrent_program_id=(71339) order by 1 desc ) where rownum < 40; {enter}
~ri2=select fcr.actual_start_date, fu.user_name, fcr.actual_completion_date , prh.request_id , round ((fcr.actual_completion_date - fcr.actual_start_date)*24*60,2) run_time_min , round ((fcr.actual_completion_date - fcr.actual_start_date)*24*60*60,2) run_time_sec , count (*) req_lines from po_requisition_headers_all prh, po_requisition_lines_all prl , fnd_concurrent_requests fcr, fnd_user fu where 1=1 and prh.creation_date > sysdate -10 and prh.requisition_header_id = prl.requisition_header_id and prh.request_id is not null and prh.request_id = fcr.request_id and fcr.requested_by = fu.user_id group by fcr.actual_start_date, fu.user_name, fcr.actual_completion_date , prh.request_id, round ((fcr.actual_completion_date - fcr.actual_start_date)*24*60,2), round ((fcr.actual_completion_date - fcr.actual_start_date)*24*60*60,2) order by 1 desc FETCH FIRST 40 ROWS ONLY; {enter}
~ri1=select fcr.actual_start_date, fu.user_name, fcr.actual_completion_date , prh.request_id , round ((fcr.actual_completion_date - fcr.actual_start_date)*24*60,2) run_time_min , round ((fcr.actual_completion_date - fcr.actual_start_date)*24*60*60,2) run_time_sec , count (*) req_lines from po_requisition_headers_all prh, po_requisition_lines_all prl , fnd_concurrent_requests fcr, fnd_user fu where 1=1 and prh.creation_date > sysdate -10 and prh.requisition_header_id = prl.requisition_header_id and prh.request_id is not null and prh.request_id = fcr.request_id and fcr.requested_by = fu.user_id group by fcr.actual_start_date, fu.user_name, fcr.actual_completion_date , prh.request_id, round ((fcr.actual_completion_date - fcr.actual_start_date)*24*60,2), round ((fcr.actual_completion_date - fcr.actual_start_date)*24*60*60,2) order by 5 desc FETCH FIRST 40 ROWS ONLY; {enter}
~hsor1=col SID FORMAT 99999 {enter} col SERIAL# FORMAT 999999 {enter} col PROCESS FORMAT 999999 {enter} col INST_ID for 99999 {enter} col module for a20 {enter} col PROGNAME for a44 {enter} col USER_NAME for a30 {enter} column ETIME heading "Elapsed time" Format 999999 {enter} set pages 10000 lines 10000 colsep " | " enter} SET UNDERLINE = {enter} SET TIMING ON {enter} alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; {enter} col USER_NAME for a15 {enter} col ACTUAL_START_DATE format a20 {enter} col ACTUAL_COMPLETION_DATE format a20 {enter} col EFFECTIVE_RUN_TIME_MIN format 999.999 {enter} {enter}
~HSO=
~oamlog=cd /u01/app/oracle/oracle_uatp/OAM/Middleware/user_projects/domains/oam_domain/bin
~oidlog=cd /u01/app/oracle/oracle_uatp/OID/Middleware/user_projects/domains/OID_Domain/bin
webgl=/u01/app/oracle/oracle_uatp/WG/Middleware/Oracle_WT1/instances/instance1/config/OHS/ohs1/webgate/config
{control}{shift}{alt}A={shift}{insert}
; For check status of WF running from long
~q1=select SQL_TEXT fulltext from gv$sqltext where sql_id='&sql_id' order by PIECE;{enter}
~q2=select CLIENT_IDENTIFIER , ACTION , SID , STATUS from gv$session where sid =&&sid and SERIAL# = &&SERIAL ;{enter}
~ftpuatsoa=ftp 10.184.44.207{enter}{Delay=2000}svcERPFTPUser{enter}{Delay=2000}svcERPFTPUser01!{enter} {Delay=2000}cd /DISPATCHER_UAT{enter}
~ftpprodsoa=ftp 10.184.44.247{enter}{Delay=2000}svcERPFTPUser{enter}{Delay=2000}svcERPFTPUser01!{enter} {Delay=2000}cd /DISPATCHER{enter}
~tablesize=select SEGMENT_NAME, BYTES/1024/1024/1024 GB, TABLESPACE_NAME from dba_segments where SEGMENT_NAME = '&TAB'{enter}
; To check locks
~ltable=col OSUSER for a10{enter} col TABELL for a30{enter} col Login_time for a30{enter} col module for a20{enter} SELECT s.inst_id, l.session_id, s.serial#, s.module, TO_CHAR( s.logon_time,'yyyy-mon-dd hh24:mi:ss') , (SELECT object_name FROM dba_objects DO WHERE do.object_id = l.object_id AND object_type ='TABLE' ) tabell FROM gv$locked_object l, gv$session s WHERE s.sid =l.session_id AND s.inst_id = l.inst_id AND object_id IN (SELECT object_id FROM dba_objects WHERE 1=1 AND object_name LIKE '%' AND object_type='TABLE' ) order by 6 ;{enter}
~l1=select l1.sid, ' IS BLOCKING ', l2.sid from gv$lock l1, gv$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;
~ll1= col BLOCKING_STATUS for a140 {enter}select s1.inst_id,s2.inst_id,s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ' || 'SERIAL#=' || s1.SERIAL# ||' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ' || 'SERIAL#=' || s2.SERIAL# ||' ) ' AS blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 order by s1.inst_id;{enter}
~ll2=col BLOCKING_STATUS for a120 {enter} select s1.inst_id ||' vs '||s2.inst_id ||' '||s1.CLIENT_IDENTIFIER ||'@'|| s1.machine||'(SID=' || s1.sid || ','||s1.SERIAL# ||' ) VS '|| s2.CLIENT_IDENTIFIER || '@'|| s2.machine ||'('||s2.sid|| ','||s2.SERIAL# ||' )' AS blocking_status from gv$lock l1, gv$session s1,gv$lock l2,gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 order by s1.inst_id ;{enter}
~l2=col ACTION for a40 {enter}select CLIENT_IDENTIFIER , ACTION , SID , SERIAL# , sql_id ,STATUS from gv$session where sid =&A; {enter}
~l3=select * from gv$sqltext where ADDRESS in (select SQL_ADDRESS from gv$session where SID=&sid) order by piece;{enter}
~l4=BREAK ON ROW SKIP 2 {enter}select A.client_identifier , A.action ,A.sql_id, A.sql_exec_start , A.status, A.* from gv$session A where sid =&sid;{enter}
~l5=select blocking_session,sid,serial#,wait_class,seconds_in_wait from gv$session where blocking_session is not NULL order by blocking_session;{enter}
~l6= select SID , SERIAL# , USERNAME, status , SQL_ID , MACHINE , LAST_CALL_ET from gv$session where STATUS='ACTIVE' and machine like '%&machine%' ;
~table_lock=col OSUSER for a10 {enter} col TABELL for a30 {enter} col Login_time for a30 {enter} col module for a20{enter} SELECT s.osuser, s.inst_id, l.session_id, s.serial#, s.module, TO_CHAR( s.logon_time,'yyyy-mon-dd hh24:mi:ss') , (SELECT object_name FROM dba_objects DO WHERE do.object_id = l.object_id AND object_type ='TABLE' ) tabell FROM gv$locked_object l, gv$session s WHERE s.sid =l.session_id AND s.inst_id = l.inst_id AND object_id IN (SELECT object_id FROM dba_objects WHERE 1=1 AND object_name LIKE '%' AND object_type='TABLE'); {enter}
~kill=select 'alter system kill session ' || '''' || sid || ',' || serial# ||',@'|| inst_id || '''' || ' ;' from gv$session where sid='&sid';{enter}
; To check Custom Locks
~cl1=col TABELL for a30{enter}col module for a40 {enter}col CLIENT_IDENTIFIER for a10 {enter} SELECT (s.client_identifier) , s.inst_id, l.session_id, s.serial#, s.module, TO_CHAR( s.logon_time,'yyyy-mon-dd hh24:mi:ss') ,to_char((sysdate - s.logon_time)*24*60,'9999999') MIN , (SELECT object_name FROM dba_objects DO WHERE do.object_id = l.object_id AND object_type ='TABLE' ) tabell FROM gv$locked_object l, gv$session s WHERE s.sid =l.session_id AND s.inst_id = l.inst_id AND object_id IN (SELECT object_id FROM dba_objects WHERE 1=1 AND object_name IN ('XXHS_SCANNED_DATA','XXHSO_INVPO_DRAFT') AND object_type='TABLE' ) and to_char((sysdate - s.logon_time)*24*60,'9999999') > 5;{enter}
~cl2=SELECT SID , STATUS , LOGON_TIME , machine ,process, MODULE , CLIENT_IDENTIFIER "USERNAME" FROM gv$session where CLIENT_IDENTIFIER = '&CLI' ; {enter}
~cl3=
;CCM Checks
~amitr1= col USER_NAME for a15 {enter} select sess.sid,sess.serial#,sess.sql_id, fusr.user_name user_name , fcp.user_concurrent_program_name progName,fcrsv.argument_text, to_char(fcr.actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate, fcr.request_id RequestId, to_char((sysdate - fcr.actual_start_date)*24*60*60,'999999999') SEC, to_char((sysdate - fcr.actual_start_date)*24*60,'9999999') MIN from fnd_concurrent_requests fcr, fnd_conc_req_summary_v fcrsv, fnd_concurrent_programs_tl fcp, fnd_user fusr, gv$session sess where fcrsv.request_id=fcr.request_id and fcp.concurrent_program_id = fcr.concurrent_program_id and fcr.program_application_id = fcp.application_id and fcp.language = 'US' and fcr.phase_code = 'R' and fcr.status_code = 'R' and fcr.requested_by = fusr.user_id and fcr.oracle_session_id = sess.audsid (+) order by 7 DESC ; {enter}
~amitr2= col USER_NAME for a15 {enter} select sess.sid,sess.serial#,sess.sql_id, fusr.user_name user_name , fcp.user_concurrent_program_name progName, to_char(fcr.actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate, fcr.request_id RequestId, to_char((sysdate - fcr.actual_start_date)*24*60*60,'999999999') SEC, to_char((sysdate - fcr.actual_start_date)*24*60,'9999999') MIN from fnd_concurrent_requests fcr, fnd_conc_req_summary_v fcrsv, fnd_concurrent_programs_tl fcp, fnd_user fusr, gv$session sess where fcrsv.request_id=fcr.request_id and fcp.concurrent_program_id = fcr.concurrent_program_id and fcr.program_application_id = fcp.application_id and fcp.language = 'US' and fcr.phase_code = 'R' and fcr.status_code = 'R' and fcr.requested_by = fusr.user_id and fcr.oracle_session_id = sess.audsid (+) order by 7 DESC ; {enter}
~amitr3import = SELECT sess.sql_id, sess.sid, sess.serial#, sess.sql_id, fusr.user_name user_name, fcp.user_concurrent_program_name progname, fcrsv.argument_text, to_char(fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') startdate, fcr.request_id requestid, to_char((sysdate -fcr.actual_start_date) * 24 * 60 * 60, '999999999') sec, to_char((sysdate - fcr.actual_start_date) * 24 * 60, '9999999') min FROM fnd_concurrent_requests fcr, fnd_conc_req_summary_v fcrsv, fnd_concurrent_programs_tl fcp, fnd_user fusr, gv$session sess WHERE fcrsv.request_id = fcr.request_id and fcp.concurrent_program_id = fcr.concurrent_program_id and fcr.program_application_id = fcp.application_id and fcp.language = 'US' and fcr.phase_code = 'R' and fcr.status_code ='R' and fcr.requested_by = fusr.user_id and fcr.oracle_session_id =sess.audsid (+) and fcp.user_concurrent_program_name ='Requisition Import' and fcrsv.argument_text LIKE' AFscan%' order by 7 desc ; {enter}
~rs=col USER_CONCURRENT_PROGRAM_NAME format a48 {enter} set head on verify on echo on pages 10000 lines 10000 linesize 250 {enter}col "PROGRAM NAME" for a50 {enter} col "QUEUE NAME" for a30{enter}col CONCURRENT_QUEUE_NAME for a50{enter} col USER_CONCURRENT_PROGRAM_NAME for a50 {enter} col USER_CONCURRENT_QUEUE_NAME for a50{enter} col USER_NAME for a10{enter}
~r1= select sess.sid,sess.serial#,sess.sql_id , fusr.description user_name , fcp.user_concurrent_program_name progName, to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate, request_id RequestId, to_char((sysdate - actual_start_date)*24*60*60,'999999999') SEC, to_char((sysdate - actual_start_date)*24*60,'9999999') MIN from fnd_concurrent_requests fcr, fnd_concurrent_programs_tl fcp, fnd_user fusr, gv$session sess where fcp.concurrent_program_id = fcr.concurrent_program_id and fcr.program_application_id = fcp.application_id and fcp.language = 'US' and fcr.phase_code = 'R' and fcr.status_code = 'R' and fcr.requested_by = fusr.user_id and fcr.oracle_session_id = sess.audsid (+) order by 8 DESC ; {enter}
~r2= col USER_CONCURRENT_QUEUE_NAME for a30{enter}select cq.user_concurrent_queue_name,fcq.max_processes,fcq.running_processes,count(cwr.request_id) PENDING_COUNT FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu,apps.fnd_concurrent_queues fcq WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R') AND cwr.hold_flag != 'Y' AND cwr.requested_start_date <= SYSDATE AND cwr.concurrent_queue_id = cq.concurrent_queue_id AND cwr.queue_application_id = cq.application_id and cq.LANGUAGE='US'and fcq.concurrent_queue_id=cq.concurrent_queue_id and fcq.application_id=cq.application_id AND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_name in (select unique user_concurrent_queue_name from apps.fnd_concurrent_queues_tl) group by cq.user_concurrent_queue_name,fcq.max_processes,fcq.running_processes; {enter}
~r3=col "PROGRAM NAME" for a40{enter} col "QUEUE NAME" for a30 {enter} select user_CONCURRENT_PROGRAM_NAME "PROGRAM NAME",concurrent_queue_name "QUEUE NAME",decode(phase_code,'P','Pending') "PHASE", decode(status_code,'A','Waiting','B','Resuming','C','Normal','D','Cancelled','E','Error','F', 'Scheduled','G','Warning','H','On Hold','I','Normal','M','No Manager','Q','Standby','R','Normal','S', 'Suspended','T','Terminating','U','Disabled','W','Paused','X','Terminated','Z','Waiting') " NAME", status_code,count(*) from fnd_concurrent_worker_requests where phase_code='P' and hold_flag!='Y' and requested_start_date<=sysdate and concurrent_queue_name<> 'FNDCRM' and concurrent_queue_name<> 'GEMSPS' group by user_CONCURRENT_PROGRAM_NAME, concurrent_queue_name,priority,phase_code,status_code order by count(*) desc ;{enter}
~r4=SELECT DECODE (CONCURRENT_QUEUE_NAME,'XXHSO_ALERT_MANAGER','HSO Alert Manager','XXHSO_UTSKRIFT','HSO Utskriftsjobber','FNDICM','Internal Manager','FNDCRM', 'Conflict Resolution Manager','AMSDMIN','Marketing Data Mining Manager','C_AQCT_SVC','C AQCART Service','FFTM','FastFormula Transaction Manager', 'FNDCPOPP','Output Post Processor','FNDSCH','Scheduler/Prereleaser Manager','FNDSM_AQHERP','Service Manager: AQHERP','FTE_TXN_MANAGER', 'Transportation Manager','IEU_SH_CS','Session History Cleanup','IEU_WL_CS','UWQ Worklist Items Release for Crashed session','INVMGR','Inventory Manager', 'INVTMRPM','INV Remote Procedure Manager','OAMCOLMGR','OAM Metrics Collection Manager','PASMGR','PA Streamline Manager','PODAMGR', 'PO Document Approval Manager','RCVOLTM','Receiving Transaction Manager','STANDARD','Standard Manager','WFALSNRSVC','Workflow Agent Listener Service', 'WFMLRSVC','Workflow Mailer Service','WFWSSVC','Workflow Document Web Services Service','WMSTAMGR','WMS Task Archiving Manager','XDP_APPL_SVC', 'SFM Application Monitoring Service','XDP_CTRL_SVC','SFM Controller Service','XDP_Q_EVENT_SVC','SFM Event Manager Queue Service','XDP_Q_FA_SVC', 'SFM Fulfillment Actions Queue Service','XDP_Q_FE_READY_SVC','SFM Fulfillment Element Ready Queue Service','XDP_Q_IN_MSG_SVC','SFM Inbound Messages Queue Service', 'XDP_Q_ORDER_SVC','SFM Order Queue Service','XDP_Q_TIMER_SVC','SFM Timer Queue Service','XDP_Q_WI_SVC','SFM Work Item Queue Service','XDP_SMIT_SVC','SFM SM Interface Test Service') AS "CONCURRENT_QUEUE_NAME",max_processes,running_processes FROM apps.fnd_concurrent_queues WHERE CONCURRENT_QUEUE_NAME IN ('XXHSO_ALERT_MANAGER','XXHSO_UTSKRIFT','FNDICM','FNDCRM','FNDCPOPP','FNDSCH','IEU_SH_CS','IEU_WL_CS','INVMGR', 'INVTMRPM','OAMCOLMGR','PASMGR','PODAMGR','RCVOLTM','STANDARD','WFALSNRSVC','WFMLRSVC','WFWSSVC')and running_processes !=0 order by max_processes desc;{enter}
~rr0=col "PROGRAM NAME" for a50 {enter} col "QUEUE NAME" for a30{enter} select user_CONCURRENT_PROGRAM_NAME "PROGRAM NAME",decode(PHASE_CODE, 'P','PENDING', 'R','RUNNING') "PHASE", count(*) from fnd_concurrent_worker_requests where (phase_code = 'P' OR phase_code = 'R') and hold_flag!='Y' and requested_start_date<=sysdate and concurrent_queue_name<> 'FNDCRM' and concurrent_queue_name<> 'GEMSPS' group by user_CONCURRENT_PROGRAM_NAME, concurrent_queue_name,priority,phase_code,status_code order by 1 desc ; {enter}
~rr1=col "PROGRAM NAME" for a50 {enter} col "QUEUE NAME" for a30{enter}select request_id,USER_CONCURRENT_PROGRAM_NAME, decode(PHASE_CODE, 'P','PENDING', 'R','RUNNING' ) phase_code, decode(STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M','No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status", user_concurrent_queue_name ,user_name FROM fnd_concurrent_worker_requests fcwr, fnd_concurrent_queues_tl fcqt, fnd_user fu WHERE (fcwr.phase_code = 'P' OR fcwr.phase_code = 'R') AND fcwr.hold_flag != 'Y' AND fcwr.requested_start_date <= SYSDATE AND fcwr.concurrent_queue_id = fcqt.concurrent_queue_id AND fcwr.queue_application_id = fcqt.application_id AND fcqt.LANGUAGE = 'US' AND fcwr.requested_by = fu.user_id ORDER BY 3 desc; {enter}
~rr2= col "PROGRAM NAME" for a50 {enter} col "QUEUE NAME" for a30{enter}select user_CONCURRENT_PROGRAM_NAME "PROGRAM NAME",concurrent_queue_name "QUEUE NAME",decode(PHASE_CODE, 'P','PENDING', 'R','RUNNING') "PHASE", decode(status_code,'A','Waiting','B','Resuming','C','Normal','D','Cancelled','E','Error','F', 'Scheduled','G','Warning','H','On Hold','I','Normal','M','No Manager','Q','Standby','R','Normal','S', 'Suspended','T','Terminating','U','Disabled','W','Paused','X','Terminated','Z','Waiting') " NAME",count(*) from fnd_concurrent_worker_requests where (phase_code = 'P' OR phase_code = 'R') and hold_flag!='Y' and requested_start_date<=sysdate and concurrent_queue_name<> 'FNDCRM' and concurrent_queue_name<> 'GEMSPS' group by user_CONCURRENT_PROGRAM_NAME, concurrent_queue_name,priority,phase_code,status_code order by 3 ; {enter}
~rr3= col "PROGRAM NAME" for a50 {enter} col "QUEUE NAME" for a30{enter} col USER_CONCURRENT_PROGRAM_NAME for a50 {enter}} select user_CONCURRENT_PROGRAM_NAME "PROGRAM NAME" ,decode(PHASE_CODE, 'P','PENDING', 'R','RUNNING') "PHASE", decode(status_code,'A','Waiting','B','Resuming','C','Normal','D','Cancelled','E','Error','F', 'Scheduled','G','Warning','H','On Hold','I','Normal','M','No Manager','Q','Standby','R','Normal','S', 'Suspended','T','Terminating','U','Disabled','W','Paused','X','Terminated','Z','Waiting') " NAME",count(*) from fnd_concurrent_worker_requests where (phase_code = 'P' OR phase_code = 'R') and hold_flag!='Y' and requested_start_date<=sysdate and concurrent_queue_name<> 'FNDCRM' and concurrent_queue_name<> 'GEMSPS' group by user_CONCURRENT_PROGRAM_NAME, concurrent_queue_name,priority,phase_code,status_code order by 2,4 desc ; {enter}
~rissue= select sess.sid,sess.serial#,sess.sql_id , fusr.description user_name , fcp.user_concurrent_program_name progName, to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate, request_id RequestId, to_char((sysdate - actual_start_date)*24*60*60,'999999999') SEC, to_char((sysdate - actual_start_date)*24*60,'9999999') MIN from fnd_concurrent_requests fcr, fnd_concurrent_programs_tl fcp, fnd_user fusr, gv$session sess where fcp.concurrent_program_id = fcr.concurrent_program_id and fcr.program_application_id = fcp.application_id and fcp.language = 'US' and fcr.phase_code = 'R' and fcr.status_code = 'R' and fcr.requested_by = fusr.user_id and fcp.user_concurrent_program_name like '%HSO - Skriv ut bestillinger%' and fcr.oracle_session_id = sess.audsid (+) order by 8 DESC ;{enter}
~ra=col "Who submitted" for a25{enter}col Parameters for a45 {enter}col "Concurrent Job" for a25{enter}SELECT distinct t.user_concurrent_program_name "Concurrent Job" , r.REQUEST_ID, to_char(r.ACTUAL_START_DATE,'dd-mm-yy hh24:mi:ss') "Started at", round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60)) "MIN", decode(r.PHASE_CODE,'C','Completed','I','Inactive','P ','Pending','R','Running','NA') phasecode, decode(r.STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M', 'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status", R.ARGUMENT_TEXT "Parameters", substr(u.description,1,25) "Who submitted" FROM APPS.FND_CONCURRENT_REQUESTS R , apps.fnd_concurrent_programs_tl t, apps.fnd_user u, apps.fnd_conc_req_summary_v v WHERE t.concurrent_program_id=r.concurrent_program_id AND r.REQUESTED_BY=u.user_id and V.REQUEST_ID=R.REQUEST_ID AND r.request_id in ('&A') order by TO_CHAR(R.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss') DESC ;{enter}
~rhist0=set linesize 250 {enter} col requested_start_date format a10 {enter} SELECT distinct fcr.request_id,FCR.ACTUAL_START_DATE , FCR.ACTUAL_COMPLETION_DATE , (fcr.actual_completion_date - fcr.actual_start_date) * 24*60*60 run_time_sec FROM apps.fnd_concurrent_programs_tl fcpt, apps.fnd_concurrent_requests fcr WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id AND fcr.program_application_id = fcpt.application_id AND fcpt.user_concurrent_program_name LIKE '%&A%' ORDER BY ACTUAL_START_DATE;
~rhist1=set linesize 250 {enter} col request_date format a15 {enter} col requested_start_date format a10 {enter} col actual_start_date format a20{enter} col actual_completion_date format a20 {enter} col argument_text format a50 {enter} col Elapsed format 9999.99 {enter} col parent_request_id format 99999999{enter}select * from (select a.request_id, a.PARENT_REQUEST_ID as parent, DECODE(a.phase_code, 'C','Completed', 'I','Inactive', 'P','Pending', 'R','Running') || ' ' || DECODE(a.status_code, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'G','Warning', 'H','On Hold', 'I',' Normal', 'M','No Manager', 'P','Scheduled', 'Q','Standby', 'R',' Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z',' Waiting') "PHASE_STATUS " , a.actual_start_date, a.actual_completion_date , (nvl(actual_completion_date,sysdate) - actual_start_date)*1440 "Elapsed", a.argument_text ,a.USER_CONCURRENT_PROGRAM_NAME from apps.FND_CONC_REQ_SUMMARY_V a where a.concurrent_program_id=(select concurrent_program_id from fnd_concurrent_requests where request_id=&1) order by a.request_id desc) where rownum < 100;{enter}
~rhist2=set linesize 250 {enter} col request_date format a15 {enter} col requested_start_date format a10 {enter} col actual_start_date format a20{enter} col actual_completion_date format a20 {enter} col argument_text format a50 {enter} col Elapsed format 9999.99 {enter} col parent_request_id format 99999999{enter}select * from (select a.request_id, a.PARENT_REQUEST_ID as parent, DECODE(a.phase_code, 'C','Completed', 'I','Inactive', 'P','Pending', 'R','Running') || ' ' || DECODE(a.status_code, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'G','Warning', 'H','On Hold', 'I',' Normal', 'M','No Manager', 'P','Scheduled', 'Q','Standby', 'R',' Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z',' Waiting') "PHASE_STATUS " , a.actual_start_date, a.actual_completion_date , (nvl(actual_completion_date,sysdate) - actual_start_date)*1440 "Elapsed", a.argument_text ,a.USER_CONCURRENT_PROGRAM_NAME from apps.FND_CONC_REQ_SUMMARY_V a where a.concurrent_program_id=(select concurrent_program_id from fnd_concurrent_requests where request_id=&1) order by a.request_id desc) where rownum < 10;{enter}
~rhist3= col user_concurrent_program_name for a55 {enter} SELECT distinct fcr.request_id, fcpt.user_concurrent_program_name,FCR.REQUESTED_START_DATE ,fcr.actual_start_date,fcr.actual_completion_date, (fcr.actual_start_date - fcr.REQUESTED_START_DATE) * 24*60*60 wait_time_sec, (fcr.actual_completion_date - fcr.actual_start_date) * 24*60*60 run_time_sec FROM apps.fnd_concurrent_programs_tl fcpt, apps.fnd_concurrent_requests fcr WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id AND fcr.program_application_id = fcpt.application_id AND fcpt.user_concurrent_program_name LIKE '%&A%' ORDER BY REQUESTED_START_DATE;{enter}
~rtime1= col user_concurrent_program_name for a55 {enter} SELECT distinct fcr.request_id , fcpt.user_concurrent_program_name, fcr.actual_start_date,fcr.actual_completion_date, (fcr.actual_completion_date - fcr.actual_start_date) * 24*60*60 run_time_sec FROM apps.fnd_concurrent_programs_tl fcpt, apps.fnd_concurrent_requests fcr WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id AND fcr.program_application_id = fcpt.application_id AND (fcr.actual_completion_date - fcr.actual_start_date) * 24*60*60 >100 and fcpt.user_concurrent_program_name LIKE '%&A%' order by 3; {enter}
~rtoday= col user_concurrent_program_name for a55 {enter}SELECT distinct fcr.request_id , fcpt.user_concurrent_program_name, fcr.actual_start_date,fcr.actual_completion_date, (fcr.actual_completion_date - fcr.actual_start_date) * 24*60*60 run_time_sec FROM apps.fnd_concurrent_programs_tl fcpt, apps.fnd_concurrent_requests fcr WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id AND fcr.program_application_id = fcpt.application_id AND (fcr.actual_completion_date - fcr.actual_start_date) * 24*60*60 >100 and fcpt.user_concurrent_program_name IN('&A','&B','&C')and trunc(fcr.actual_completion_date) = trunc(sysdate) order by 3; {enter}
~rimport= col user_concurrent_program_name for a55 {enter}SELECT distinct fcr.request_id , fcpt.user_concurrent_program_name, fcr.actual_start_date,fcr.actual_completion_date, (fcr.actual_completion_date - fcr.actual_start_date) * 24*60*60 run_time_sec FROM apps.fnd_concurrent_programs_tl fcpt, apps.fnd_concurrent_requests fcr WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id AND fcr.program_application_id = fcpt.application_id AND (fcr.actual_completion_date - fcr.actual_start_date) * 24*60*60 >100 and fcpt.user_concurrent_program_name IN('HSO Import Telepay files','HSO Import ISO20022 files - camt.054','HSO Import ISO20022 files - pain.002','HSO Import ISO20022 files')and trunc(fcr.actual_completion_date) = trunc(sysdate) order by 3;{enter}
~rlog=select logfile_name,outfile_name from fnd_concurrent_requests where request_id = &request_id;{enter}
~track1= SELECT SID , SERIAL# , last_call_et , STATUS , LOGON_TIME , CLIENT_IDENTIFIER FROM gv$session where Module like '%Trackingreport%' order by 5;{enter}
~track2= SELECT SID , SERIAL# , STATUS , LOGON_TIME , CLIENT_IDENTIFIER FROM gv$session where status = 'ACTIVE' and Module like '%Trackingreport%' order by 4;{enter}
~track3=SELECT SID , SERIAL# , USERNAME , STATUS , LOGON_TIME , CLIENT_IDENTIFIER FROM gv$session where Module like '%Trackingreport%' and STATUS ='ACTIVE'; {enter}
~userconnected=col NAME for a40; {enter}SELECT user_name username, description name, to_char(b.last_connect,'MM/DD/RR HH24:MI') lastconnect FROM apps.fnd_user a, (SELECT MIN (first_connect) first_connect, MAX (last_connect) last_connect, last_updated_by user_id FROM apps.icx_sessions GROUP BY last_updated_by) b WHERE a.user_id = b.user_id AND last_connect>SYSDATE-3/12 ORDER BY 3 DESC ;{enter}
~profilename= col PROFILE for a50{enter}col PROFILE_OPTION_VALUE for a30{enter}col LEVEL_VALUE for a30{enter}SELECT substr(pro1.user_profile_option_name,1,35) Profile, decode(pov.level_id, 10001,'Site', 10002,'Application', 10003,'Resp', 10004,'User') Option_Level, decode(pov.level_id, 10001,'Site', 10002,appl.application_short_name, 10003,resp.responsibility_name, 10004,u.user_name) Level_Value, nvl(pov.profile_option_value,'Is Null') Profile_option_Value FROM fnd_profile_option_values pov, fnd_responsibility_tl resp, fnd_application appl, fnd_user u, fnd_profile_options pro, fnd_profile_options_tl pro1 WHERE pro.profile_option_name = pro1.profile_option_name and pro.profile_option_id = pov.profile_option_id and pro1.user_profile_option_name like '%&A%' and pov.level_value = resp.responsibility_id (+) and pov.level_value = appl.application_id (+) and pov.level_value = u.user_id (+) order by 1,2;
~profilehc=col PROFILE_OPTION_NAME for a20 {enter}col USER_PROFILE_OPTION_NAME for a50{enter}col PROFILE_OPTION_VALUE for a30{enter}select val.level_id,opt.PROFILE_OPTION_NAME,tl.USER_PROFILE_OPTION_NAME,val.PROFILE_OPTION_VALUE from fnd_profile_options opt,fnd_profile_options_tl tl, fnd_profile_option_values val where opt.PROFILE_OPTION_ID=val.PROFILE_OPTION_ID and opt.PROFILE_OPTION_NAME=tl.PROFILE_OPTION_NAME and tl.USER_PROFILE_OPTION_NAME in ( 'ICX: Language','ICX: Limit connect','ICX: Limit time','ICX:Session Timeout','WF: Workflow Mailer Framework Web Agent') and val.level_id=10001;{enter}
~profilesso=col USER_PROFILE_OPTION_NAME for a40{enter}col PROFILE_OPTION_VALUE for a60{enter}select tl.user_profile_option_name, vl.profile_option_value from fnd_profile_option_values vl , fnd_profile_options p , fnd_profile_options_tl tl where vl.profile_option_id = p.profile_option_id and p.profile_option_name=tl.profile_option_name and tl.user_profile_option_name in ('Applications SSO Type','Application Authenticate Agent','Application SSO LDAP Synchronization','Applications SSO Auto Link User') and vl.level_id = 10001 order by 1;{enter}
~checkpatch=SELECT DISTINCT RPAD(a.bug_number, 11)|| RPAD(e.patch_name, 11)|| RPAD(TRUNC(c.end_date), 12)|| RPAD(b.applied_flag, 4) BUG_APPLIED FROM ad_bugs a, ad_patch_run_bugs b, ad_patch_runs c, ad_patch_drivers d , ad_applied_patches e WHERE a.bug_id = b.bug_id AND b.patch_run_id = c.patch_run_id AND c.patch_driver_id = d.patch_driver_id AND d.applied_patch_id = e.applied_patch_id AND a.bug_number in ('&a', '&b','&c') ORDER BY 1 DESC; {enter}
~jens=select trunc(i.first_connect) connection_date, to_char(first_connect,'HH24') first_hour, count(*) from applsys.fnd_logins l ,apps.icx_sessions i where l.login_id=i.login_id and trunc(first_connect)>=trunc(sysdate) group by trunc(i.first_connect) , to_char(first_connect,'HH24') order by 1,2 ;
; session
~sessactive= select A.client_identifier , A.action , A.* from gv$session A where STATUS='ACTIVE' ; {enter}
~sess0= select A.client_identifier , A.action , A.* from gv$session A where sid =&a;
~sess1= select SID , SERIAL# , USERNAME, status , SQL_ID , LAST_CALL_ET ,PROGRAM , CLIENT_IDENTIFIER from gv$session where username='APPS' ; {enter}
~sess2= select SID , SERIAL# , USERNAME, status , SQL_ID , LAST_CALL_ET ,PROGRAM , CLIENT_IDENTIFIER from gv$session where username='APPS' and STATUS='ACTIVE' ; {enter}
~sess3= select SID , SERIAL# , USERNAME, status , SQL_ID from gv$session where STATUS='ACTIVE' ; {enter}
~sess4= select SID , SERIAL# , USERNAME, status , SQL_ID , MACHINE , LAST_CALL_ET from gv$session where STATUS='ACTIVE' ; {enter}
~sess5= select * from gv$sqltext where ADDRESS in (select SQL_ADDRESS from gv$session where SID=&sid) order by piece; {enter}
~sess6= select SQL_TEXT fulltext from gv$sqltext where sql_id='&sql_id' order by PIECE; {enter}
~sessaccg=select SID , SERIAL# , USERNAME, status , SQL_ID , MACHINE , LAST_CALL_ET,LOGON_TIME from gv$session where MACHINE in ('alp-hso-accg01','alp-hso-accg02') ;
~sessoacore=select distinct PROGRAM , process from gv$session where MACHINE like 'al%-hso-eba%.hso.cosng.net' and program like 'JDBC%' ;
~sesscpu= col name format a26{enter}col username format a15{enter}col program format a40{enter}col SESS_CPU_SECS wra format 999,999,999.99{enter}col LAST_CPU_SECS wra format 999,999,999.99{enter}col logon_secs wra format 999,999,999{enter}col Percent wra format 999.99{enter}select sess_cpu.con_id, sess_cpu.sid, NVL(sess_cpu.username, 'Oracle Process') username, sess_cpu.status, sess_cpu.logon_time, round ((sysdate-sess_cpu.logon_time)*1440*60) logon_SECS, sess_cpu.value/100 SESS_CPU_SECS, (sess_cpu.value - call_cpu.value)/100 LAST_CPU_SECS, round((sess_cpu.value/100)/round((sysdate - sess_cpu.logon_time)*1440*60)*100,2) Percent, sess_cpu.sql_id from (select se.con_id,se.sql_id,ss.statistic#,se.sid, se.username, se.status, se.program, se.logon_time, sn.name, ss.value from v$session se, v$sesstat ss, v$statname sn where se.sid=ss.sid and sn.statistic#=ss.statistic# and sn.name in ('CPU used by this session') ) sess_cpu, (select se.con_id, ss.statistic#,se.sid, ss.value, value/100 seconds from v$session se, v$sesstat ss, v$statname sn where se.sid=ss.sid and sn.statistic#=ss.statistic# and sn.name in ('CPU used when call started') ) call_cpu where sess_cpu.sid=call_cpu.sid and sess_cpu.con_id=call_cpu.con_id order by SESS_CPU_SECS ;
~dwsess=col username format a15 {enter} select SID , SERIAL# , USERNAME, status , SQL_ID , LAST_CALL_ET ,PROGRAM , CLIENT_IDENTIFIER from gv$session where username ='UAT_DW' and STATUS ='ACTIVE'; {enter}
~clisess=col PROGRAM for a50 {enter}col MODULE for a50{enter} SELECT SID , SERIAL# , USERNAME , STATUS , LOGON_TIME ,PROGRAM , MODULE , CLIENT_IDENTIFIER FROM gv$session where CLIENT_IDENTIFIER = '&CLI';{enter}
~sess7= col module for a50 {enter} prompt TOTAL SESSIONS {enter} select s.module,count(s.sid) Total_Sessions from gv$session s, gv$process p where p.addr=s.paddr group by s.module ORDER BY 2 DESC; {enter}
~sidp0= select SID , SERIAL# , CLIENT_IDENTIFIER, status , SQL_ID from gv$session where STATUS='ACTIVE' and MACHINE ='&mach' and process= &process_num; {enter}
~sidp1=select SID , SERIAL# , CLIENT_IDENTIFIER , status , SQL_ID from gv$session where MACHINE = 'alp-hso-eba01.hso.cosng.net' and process= &process_num order by 3; {enter}
~sidp2=select SID , SERIAL# , CLIENT_IDENTIFIER,status , SQL_ID from gv$session where MACHINE = 'alp-hso-eba02.hso.cosng.net' and process= &process_num order by 3; {enter}
~sidp3=select SID , SERIAL# , CLIENT_IDENTIFIER,status , SQL_ID from gv$session where MACHINE = 'alp-hso-eba03.hso.cosng.net' and process= &process_num order by 3 ; {enter}
~sidp4=select SID , SERIAL# , CLIENT_IDENTIFIER,status , SQL_ID from gv$session where MACHINE = 'alp-hso-eba04.hso.cosng.net' and process= &process_num order by 3; {enter}
~sidp5=select SID , SERIAL# , CLIENT_IDENTIFIER,status , SQL_ID from gv$session where MACHINE = 'alp-hso-eba05.hso.cosng.net' and process= &process_num order by 3 ; {enter}
~sid0=select A.client_identifier , A.action ,A.sql_id, A.sql_exec_start , A.status, A.* from gv$session A where sid=&A;
~sidd= set head off verify off echo off pages 1500 linesize 100 lines 120 {enter} select /*+ CHOOSE*/ 'Session Id.............................................: '||s.sid, 'Serial Num..............................................: '||s.serial#, 'SQL_ID..................................................:' ||s.SQL_ID, 'User Name ..............................................: '||s.username, 'Session Status .........................................: '||s.status,'Login Time..............................................: '||s.LOGON_TIME, 'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client, 'Schema Name ..... ......................................: '||s.SCHEMANAME, 'Program ...............................................: '||s.program, 'Module .................................................: '|| s.module, 'Action .................................................: '||s.action, 'Client Machine .........................................: '||s.machine, 'client_identifier ......................................: '||s.client_identifier, 'LAST_CALL_ET ...........................................: '||s.last_call_et, 'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600 from gv$session s, gv$process p where p.addr=s.paddr and s.sid=nvl('&sid',s.sid) and s.serial#=nvl('&serial',s.serial#) ; {enter}
~biprodconn=sqlplus system/Oracle123@"(DESCRIPTION = (CONNECT_TIMEOUT = 10) (TRANSPORT_CONNECT_TIMEOUT = 3) (ADDRESS = (PROTOCOL = TCP)(HOST = exa06-clu2-scan.hso.cosng.net)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = OBIPROD) ))"
~up1= cd $ADMIN_SCRIPTS_HOME {enter}
~up2=./adstrtal.sh apps/
ttt= tail -100f nohup.out {enter}
rrr= tail -100f
~tt=apps4hsotest{enter}
~pp=pwd4hsoprd{enter}
~qweb=pwd4hsoqa1{enter}
~qapps=apps4hsouat {enter}
#saqapps=apps4saqa9211
#saqweb=pwd4sahfqa {enter}
~ee=Regards,{enter}Harmeet Luthra
lsl=ls -ltrh {enter}
hgmail=harmeetluthra@gmail.com
~he=harmeet.luthra@evry.com
~ht=harmeet.luthra@tietoevry.com
harl=harmeet.luthra
Opass=12@Millions{enter}
hpan=ACLPL6217H{enter}
mpass=Darkcave30{enter}
epass=12@Millions66
yyy=Darkcave30
#a=br@veFox32HSO{enter}
~hhh=12millions{enter}
~psj=ps -ef | grep java | grep -v grep | grep Dweblogic.Name=
~w=weblogic
~m=manager1{enter}
~Q=Qalogic4sahf{enter}
~cfile= echo $CONTEXT_FILE{enter}
~op= export ORACLE_HOME=`pwd` ; export PATH=$ORACLE_HOME/OPatch:$PATH ; echo $PATH ; echo $ORACLE_HOME ; which opatch {enter}
~ol= opatch lsinventory
; DB commands
~smon=ps -ef | grep smon {enter}
~sql=sqlplus "/as sysdba" {enter}
~sel=select * from
~sd= sqlplus apps{enter} apps4hsodev {enter}
~sp= sqlplus apps {enter} apps4hsoprd461 {enter}
~sq= sqlplus apps {enter} apps4hsouat {enter}
~st= sqlplus apps {enter} apps4hsotest {enter}
~ss= sqlplus apps/apps {enter}
~sys=Oracle123{enter}
; Apps Commands
~fnd = history|grep FNDCPASS {enter}
wpd=pwd
~lsf = ls -l | egrep -v '^d'
;Instance Specifuc
~inva=set pagesize 1000{enter}COLUMN object_name FORMAT A30{enter}COLUMN status FORMAT A9 {enter}COLUMN owner FORMAT A15{enter}COLUMN OBJECT_TYPE for a12{enter}Select name from v$database;{enter}Select count(1) FROM dba_objects WHERE status = 'INVALID';{enter}select object_name,object_type,owner,LAST_DDL_TIME from dba_objects where status = 'INVALID'order by 1;
~inv1=Select count(1) FROM dba_objects WHERE status = 'INVALID'; {enter} select owner , COUNT(*) from dba_objects where status='INVALID' group by owner; {enter}
~inv2=SELECT distinct 'EXECUTE DBMS_UTILITY.COMPILE_SCHEMA ('''|| owner ||''', FALSE);' FROM dba_objects WHERE status = 'INVALID';{enter}
sshjump= ssh 146.213.0.134{enter}
scpdeva= scp ald-eba-01:/u02/amibud/patch
scpq=scp applmgr@alq-hso-eba01:/u01/amibud
scpp= scp applmgr@alp-hso-eba01:/u01/amibud
scpd1= scp applmgr@alt-hso-deba01:/u02/amibud
scpt= scp applmgr@alt-hso-teba:/u01/amibud
~deploy=cd /u01/wildfly/wildfly-10.0.0.Final/standalone/deployments/
~histf= history|grep FNDCPASS
~g=grep java.lang.OutOfMemoryError
~inva1=set pagesize 1000 {enter}COLUMN object_name FORMAT A30 {enter}COLUMN status FORMAT A9 {enter}COLUMN owner FORMAT A15 {enter}COLUMN OBJECT_TYPE for a12 {enter}Select name from v$database; {enter}Select count(1) FROM dba_objects WHERE status = 'INVALID'; {enter}select object_name,object_type,owner,LAST_DDL_TIME from dba_objects where status = 'INVALID'; {enter}
SELECT distinct 'EXECUTE DBMS_UTILITY.COMPILE_SCHEMA ('''|| owner ||''', FALSE);' FROM dba_objects WHERE status = 'INVALID';
~nls1=alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; {enter}
~set =set pages 10000 lines 10000 colsep " | "{enter}SET UNDERLINE ={enter}col SID FORMAT 99999 {enter}col 'SERIAL#' FORMAT 999999{enter}col PROCESS FORMAT 999999{enter}col INST_ID for 99{enter}col module for a20{enter}col PROGNAME for a44{enter}col USER_NAME for a30{enter}col ETIME heading "Elapsed time" Format 999999{enter}set tim on;{enter}
~sa= set feed off UNDERLINE = TIMING ON head on verify on echo on feedback on time on pages 10000 lines 10000 colsep " | ";{enter}col USER_CONCURRENT_PROGRAM_NAME format a48{enter}alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; {enter}col ARGUMENT_TEXT for a65{enter}col SID FORMAT 99999{enter}col SERIAL# FORMAT 999999 {enter}col PROCESS FORMAT 999999 {enter}col INST_ID for 99{enter}col module for a20 {enter}col PROGNAME for a44 {enter}col USER_NAME for a30 {enter}column ETIME heading "Elapsed time" Format 999999 {enter}col MACHINE for a30{enter}col USERNAME for a10{enter}col CLIENT_IDENTIFIER for a15 {enter}col PROGRAM for a48{enter}col ACTION for a30{enter}CLEAR BREAK {enter}!clear{enter}
~setp = SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> "
~sett = set time on SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> " timi on
#wfstatus= SELECT FSC.COMPONENT_NAME,FSC.COMPONENT_STATUS FROM APPS.FND_CONCURRENT_QUEUES_VL FCQ, apps.FND_SVC_COMPONENTS FSC WHERE FSC.CONCURRENT_QUEUE_ID=FCQ.CONCURRENT_QUEUE_ID(+) ORDER BY COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;
#knut= select round( (select count (*) from wf_notifications where begin_date > sysdate-1 and begin_date < sysdate-1/24/60*5 and nvl(status,'XXX') = 'OPEN' and mail_status ='MAIL') / (select count (*) from wf_notifications where begin_date > sysdate-1 and begin_date < sysdate-1/24/60*5 and nvl(status,'XXX') = 'OPEN' and mail_status in ('SENT', 'MAIL')) * 100 ,2) Knut_checks from dual ;
#wf0= select sess.sid,sess.serial#,sess.sql_id , fusr.description user_name , fcp.user_concurrent_program_name progName, to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate, request_id RequestId, to_char((sysdate - actual_start_date)*24*60*60,'999999999') SEC, to_char((sysdate - actual_start_date)*24*60,'9999999') MIN from fnd_concurrent_requests fcr, fnd_concurrent_programs_tl fcp, fnd_user fusr, gv$session sess where fcp.concurrent_program_id = fcr.concurrent_program_id and fcr.program_application_id = fcp.application_id and fcp.language = 'US' and fcr.phase_code = 'R' and fcr.status_code = 'R' and fcr.requested_by = fusr.user_id and fcp.user_concurrent_program_name like 'Workflow%' and fcr.oracle_session_id = sess.audsid (+) order by 8 DESC ;
#wf1=COLUMN COMPONENT_NAME FORMAT A45 {enter}COLUMN COMPONENT_STATUS FORMAT A20{enter}select count(*) from wf_notifications where status = 'OPEN' and mail_status = 'MAIL' and (sysdate - begin_date)*24*60 > 20; {enter} SELECT FSC.COMPONENT_NAME,FSC.COMPONENT_STATUS{enter}FROM APPS.FND_CONCURRENT_QUEUES_VL FCQ, apps.FND_SVC_COMPONENTS FSC WHERE FSC.CONCURRENT_QUEUE_ID=FCQ.CONCURRENT_QUEUE_ID(+) ORDER BY COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;{enter} select w.user_data.itemtype,msg_state,count(*) as AMIT_CHECKS_wf_deferred_table from applsys.aq$wf_deferred_table_m w group by w.user_data.itemtype , msg_state order by 1,2 ;{enter}select corrid, decode(state, 0, '0 = Ready', to_char(state)) State, count(*) HARMEET_CHECKS from wf_deferred where corrid like 'APPS:oracle.apps.wf.notification%' and state=0 group by corrid, state;{enter} select round( (select count (*) from wf_notifications where begin_date > sysdate-1 and begin_date < sysdate-1/24/60*5 and nvl(status,'XXX') = 'OPEN' and mail_status ='MAIL') / (select count (*) from wf_notifications where begin_date > sysdate-1 and begin_date < sysdate-1/24/60*5 and nvl(status,'XXX') = 'OPEN' and mail_status in ('SENT', 'MAIL')) * 100 ,2) Knut_checks from dual ; {enter} select w.user_data.itemtype,msg_state,count(*) as DEFERRED_READY from applsys.aq$wf_deferred_table_m w where msg_state='READY' group by w.user_data.itemtype , msg_state order by 1,2 ; {enter}
#wf2=select w.user_data.itemtype,msg_state,count(*) as DEFERRED_READY from applsys.aq$wf_deferred_table_m w where msg_state='READY' group by w.user_data.itemtype , msg_state order by 1,2 ;
#wf3=col SID FORMAT 99999{enter}col SERIAL#FORMAT 999999{enter} col PROCESS FORMAT 999999{enter} set pages 10000 lines 10000{enter} col module for a20 {enter} col argument_text for a15 {enter} SELECT distinct fcr.request_id , fcr.actual_start_date, fcr.actual_completion_date,fcr.argument_text , (fcr.actual_completion_date - fcr.actual_start_date) * 24*60*60 run_time_sec FROM apps.fnd_concurrent_programs_tl fcpt, apps.fnd_concurrent_requests fcr WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id AND fcr.program_application_id = fcpt.application_id AND fcpt.user_concurrent_program_name LIKE '%Workflow Background Process%' AND trunc(actual_start_date) > trunc(sysdate-1) ORDER BY 1; {enter}
~dba_dir=COLUMN owner FORMAT A5 {enter} COLUMN directory_name FORMAT A30{enter} COLUMN directory_path FORMAT A100{enter} SELECT owner , directory_name , directory_path FROM dba_directories ORDER BY owner, directory_name;{enter}
~user1=select CLIENT_IDENTIFIER, count(*) from gv$session where CLIENT_IDENTIFIER is not null and CLIENT_IDENTIFIER not in ('SYSADMIN') and PROGRAM not like 'RCVOLTM%' and CLIENT_IDENTIFIER not in ('ANONYMOUS') having count(*) > 1 group by CLIENT_IDENTIFIER order by count(*) desc ;
~user2=SELECT LOGON_TIME Logged_IN , CLIENT_IDENTIFIER USER_NAME FROM gv$session where CLIENT_IDENTIFIER is not null and CLIENT_IDENTIFIER not in ('SYSADMIN') and PROGRAM not like 'RCVOLTM%' and CLIENT_IDENTIFIER not in ('ANONYMOUS') order by 1;
~user3= col CLIENT_IDENTIFIER for a15{enter} col DESCRIPTION for a38{enter} col email_address for a40{enter}select distinct b.CLIENT_IDENTIFIER, a.description, a.email_address from gv$session b, fnd_user a where b.CLIENT_IDENTIFIER is not null and CLIENT_IDENTIFIER not in ('SYSADMIN') and PROGRAM not like 'RCVOLTM%' and CLIENT_IDENTIFIER not in ('ANONYMOUS') and b.CLIENT_IDENTIFIER =a.user_name ; {enter}
~user4= col CLIENT_IDENTIFIER for a10{enter} col DESCRIPTION for a20{enter} col MACHINE for a15 {enter} col email_address for a30{enter}select distinct b.CLIENT_IDENTIFIER,b.sid, b.sql_id , b.MACHINE, b.LOGON_TIME Logged_IN , a.description, a.email_address from gv$session b, fnd_user a where b.CLIENT_IDENTIFIER is not null and CLIENT_IDENTIFIER not in ('SYSADMIN') and PROGRAM not like 'RCVOLTM%' and CLIENT_IDENTIFIER not in ('ANONYMOUS') and b.CLIENT_IDENTIFIER =a.user_name order by 5; {enter}
~user5= select distinct b.CLIENT_IDENTIFIER,b.sid, b.process , b.sql_id , b.MACHINE, b.LOGON_TIME Logged_IN , a.description, a.email_address from gv$session b, fnd_user a where b.CLIENT_IDENTIFIER is not null and CLIENT_IDENTIFIER not in ('SYSADMIN') and PROGRAM not like 'RCVOLTM%' and CLIENT_IDENTIFIER not in ('ANONYMOUS') and b.CLIENT_IDENTIFIER =a.user_name order by 1,5; {enter}
~pdb=export ORACLE_PDB_SID=
~uptime= Select instance_name as "Database_name",to_char(startup_time,'dd-mm-yyyy hh24:mi:ss') as "The_Startup_Time" from gv$instance; {enter}
#nothin=
Comments
Post a Comment