ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 552BA2283C9B4ED484FEF23DD62ABB81Y select gs.inst_id, gs.sid||' - '||gs.serial# sid_serial#, xxen_util.user_name(gs.module,gs.action,gs.client_identifier) user_name, xxen_util.responsibility(gs.module,gs.action) responsibility, xxen_util.module_type(gs.module,gs.action) module_type, xxen_util.module_name(gs.module,gs.program) module_name, fcr.request_id, fcr.user_concurrent_program_name concurrent_program, gs.status, xxen_util.time(gs.last_call_et) status_duration, gs.last_call_et status_seconds, xxen_util.client_time(gs.logon_time) logon_time, decode(gs.state,'WAITING',gs.event,'ON CPU') event, decode(gs.state,'WAITING',gs.wait_class) wait_class, decode(gs.state,'WAITING',xxen_util.time(gs.wait_time_micro/1000000)) wait_time, gs.blocking_instance blocking_inst_id, gs.blocking_session blocking_sid, gs.sql_id, &sql_text xxen_util.client_time(gs.sql_exec_start) sql_exec_start, (select dp.object_name||case when dp.procedure_name is not null then '.'||dp.procedure_name end from dba_procedures dp where gs.plsql_entry_object_id=dp.object_id and gs.plsql_entry_subprogram_id=dp.subprogram_id) entry_procedure, gs.prev_sql_id, gp.pga_used_mem/1000000 pga_used, gp.pga_alloc_mem/1000000 pga_allocated, gtu.temp_used, gt.undo_used, gt.undo_records, xxen_util.client_time(gt.transaction_start) transaction_start, gsi.logical_reads, gsi.physical_reads, gs.username db_user, gs.osuser, gs.machine, gs.program, gs.module, gs.action, gs.client_identifier client_id, gs.service_name, gs.type session_type, gp.spid os_process_db, gs.process client_process, case when gs.type<>'BACKGROUND' then 'alter system disconnect session '''||gs.sid||','||gs.serial#||',@'||gs.inst_id||''' immediate;' end disconnect_db_session, case when gs.type<>'BACKGROUND' then 'kill -9 '||gp.spid end kill_server_process from gv$session gs, gv$process gp, ( select fcr.request_id, fcr.oracle_session_id, fcpv.user_concurrent_program_name from fnd_concurrent_requests fcr, fnd_concurrent_programs_vl fcpv where fcr.phase_code='R' and fcr.program_application_id=fcpv.application_id and fcr.concurrent_program_id=fcpv.concurrent_program_id ) fcr, ( select gtu.inst_id, gtu.session_addr, sum(gtu.blocks*(select dt.block_size from dba_tablespaces dt where gtu.tablespace=dt.tablespace_name))/1000000 temp_used from gv$tempseg_usage gtu group by gtu.inst_id, gtu.session_addr ) gtu, ( select gt.inst_id, gt.ses_addr, sum(gt.used_ublk)*(select to_number(vp.value) from v$parameter vp where vp.name='db_block_size')/1000000 undo_used, sum(gt.used_urec) undo_records, min(gt.start_date) transaction_start from gv$transaction gt group by gt.inst_id, gt.ses_addr ) gt, (select gsi.inst_id, gsi.sid, gsi.block_gets+gsi.consistent_gets logical_reads, gsi.physical_reads from gv$sess_io gsi) gsi where 1=1 and gs.inst_id=gp.inst_id(+) and gs.paddr=gp.addr(+) and gs.audsid=fcr.oracle_session_id(+) and gs.inst_id=gtu.inst_id(+) and gs.saddr=gtu.session_addr(+) and gs.inst_id=gt.inst_id(+) and gs.saddr=gt.ses_addr(+) and gs.inst_id=gsi.inst_id(+) and gs.sid=gsi.sid(+) order by decode(gs.status,'ACTIVE',1,2), gs.last_call_et desc US DBA Sessions Current database sessions with their Oracle EBS user, responsibility, form or concurrent request, the SQL being executed, wait event, blocking session, and PGA, temp and undo usage. Status Duration is the time since the session last changed between active and inactive, so for an active session it is the run time of the current call. Temp Used and Undo Used are the space the session holds at the time of the report. The Disconnect DB Session and Kill Server Process columns contain the commands to end a session. Enginatics &sql_text 1=1 1 10 1=1 gs.status='ACTIVE' LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Active only 2 20 1=1 gs.last_call_et>=:status_minutes_from*60 Number US Status Duration Minutes From Show only sessions that have been active or inactive for at least this number of minutes. Combined with Active only, it lists long running calls. 3 30 1=1 gs.blocking_session is not null LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Blocked Sessions only 4 40 1=1 gs.type=decode(:session_type,'Foreground','USER','BACKGROUND') LOV DBA Session Type 8E2FF36EDEF079D2E0530100007F1FF2 Y select 'Foreground' value, 'User sessions only' description from dual union all select 'Background' value, 'DB internal processes only' description from dual Foreground US Session Type 5 50 1=1 gs.client_identifier=:user_name LOV FND User Name 8E2FF36EDE8479D2E0530100007F1FF2 Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name US User Name 6 60 1=1 xxen_util.module_type(gs.module,gs.action)=:module_type LOV DBA Module Type 8E2FF36EDF1F79D2E0530100007F1FF2 Y select 'Concurrent Request' value, null description from dual union all select 'Concurrent Manager' value, null description from dual union all select 'Form' value, null description from dual union all select 'Oracle Application Framework' value, null description from dual union all select 'Workflow' value, null description from dual union all select 'Business Event' value, null description from dual union all select 'Blitz Report' value, null description from dual union all select 'Discoverer' value, null description from dual US Module Type 7 70 1=1 lower(gs.module) like '%'||lower(:module)||'%' Char US Module contains 8 80 1=1 fcr.request_id=:request_id Number US Request Id 9 90 1=1 gs.sql_id=:sql_id Char US SQL Id 10 100 1=1 gs.username=:schema LOV DBA Username 8E2FF36EDEDA79D2E0530100007F1FF2 Y select du.username value, null description from dba_users du order by du.username US Schema 11 110 1=1 gs.machine=:machine LOV custom Y select gs.machine value, count(*) description from gv$session gs group by gs.machine order by count(*) desc, gs.machine US Machine 12 120 1=1 gs.inst_id=:inst_id LOV DBA Instance Id 8E2FF36EDF1279D2E0530100007F1FF2 Y select gi.inst_id value, gi.instance_name||' '||gi.host_name||' '||gi.startup_time description from gv$instance gi US Instance Id 13 130 &sql_text (select gsa.sql_fulltext from gv$sqlarea gsa where gs.inst_id=gsa.inst_id and gs.sql_id=gsa.sql_id) sql_text, LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Show SQL Text