RSS

Monthly Archives: December 2012

[HTD000055] How to Use Oracle FNDLOAD Utility [PDF]

 

Query: SESSION ADMINISTRATION

---SESSION ADMINISTRATION
select sesion.sid,
 sesion.username,
 optimizer_mode,
 hash_value,
 address,
 cpu_time,
 elapsed_time,
 sql_text
 from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
 and sesion.sql_address = sqlarea.address
 and sesion.username is not null 
and lower(SQL_TEXT) like lower('%'|| :part_of_code_or_object ||'%')
order by 1 desc
 ;
 
----------------
 
select sid,
 to_char(logon_time,'MMDDYYYY:HH24:MI') logon_time,
 username,
 type,
 status,
 process,
 sql_address,
 sql_hash_value
 from v$session
where username is not null 
-- AND SID=:sid_number
;

--shows SID and its correspondent sql statment
 select sesion.sid,
 sql_text
 from v$sqltext sqltext, v$session sesion
 where sesion.sql_hash_value = sqltext.hash_value
 and sesion.sql_address = sqltext.address
 and sesion.username is not null
 order by 1 desc , sqltext.piece
 ;
 
 
 -- shows SID and correspondent physical, logical reads
 select sess_io.sid,
 sess_io.block_gets,
 sess_io.consistent_gets,
 sess_io.physical_reads,
 sess_io.block_changes,
 sess_io.consistent_changes
 from v$sess_io sess_io, v$session sesion
 where sesion.sid = sess_io.sid
 and sesion.username is not null
 order by 1 desc 
 ;
------


Cheers

Muhammad Rashed
 
Leave a comment

Posted by on December 27, 2012 in Queries

 

Tags: , , , ,

11i,R12List Requests timing

SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ' HOURS ::' ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
||' MINUTES ::' ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference
, DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name
||'['||f.description||']',p.concurrent_program_name)concurrent_program_name
, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase
, f.status_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
--and f.request_id='999999' -- if you want to search specific request
ORDER by 
f.request_id desc
--f.actual_completion_date-f.actual_start_date desc
;

Cheers

Muhammad Rashed

 
Leave a comment

Posted by on December 20, 2012 in Queries

 

Tags: , , , , ,

Quote

Cheers,

Muhammad Rashed.

Tips For Personalizing The E-Business Suite 11i & R12 Login Page [PDF]

 
Leave a comment

Posted by on December 20, 2012 in Tips

 

LINUX make a command alias

Make aliases permanent (bash syntax)

The alias c remains in effect only during the current login session. Once you logs out or reboot the system the alias c will be gone. To avoid this problem, add alias to your ~/.bashrc file, enter:

 
vi ~/.bashrc

The alias c for the current user can be made permanent by entering the following line:

 
alias c='clear'

Save and close the file. System-wide aliases (i.e. aliases for all users) can be put in the /etc/bashrc file. Please note that the alias command is built into a various shells including ksh, tcsh/csh, ash, bash and others.

A note about privileged access

You can add code as follows in ~/.bashrc:

 
# if user is not root, pass all commands via sudo #
if [ $UID -ne 0 ]; then
    alias reboot='sudo reboot'
    alias update='sudo apt-get upgrade'
fi

---

A good use of alias to write less parameter and also to hide passwords e.g.:

alias sql='sqlplus apps/apps'

————

Cheers

Muhammad Rashed.

 
1 Comment

Posted by on December 20, 2012 in OS

 

Tags: , ,

Oracle EBS: How to know all (running/pending/completed/inactive) requests.

This query lists requests according to their status. it’s equivalent to Requests form “FNDRSRUN.fmx”

———

SELECT

FCR.REQUEST_ID REQUEST_ID
,FCPT.USER_CONCURRENT_PROGRAM_NAME REQUEST_NAME
,FCR.ACTUAL_START_DATE START_DATE
,DECODE(FCR.PHASE_CODE, ‘C’, ‘Completed’,
‘I’, ‘Inactive’,
‘P’, ‘Pending’,
‘R’, ‘Running’) PHASE
,DECODE(FCR.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
,FU.USER_NAME REQUESTED_BY
FROM FND_CONCURRENT_PROGRAMS FCP,
FND_CONCURRENT_PROGRAMS_TL FCPT,
FND_CONCURRENT_REQUESTS FCR,
FND_USER FU
WHERE   –TRUNC(((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)/(1/24))*60) > NVL(‘&MIN’,45)
FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCP.APPLICATION_ID
AND FCR.CONCURRENT_PROGRAM_ID = FCPT.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCPT.APPLICATION_ID
AND FU.USER_ID = FCR.REQUESTED_BY
AND FCPT.LANGUAGE = USERENV(‘Lang’)
AND FCR.PHASE_CODE = ‘R’
/* ‘C’, ‘Completed’,
‘I’, ‘Inactive’,
‘P’, ‘Pending’,
‘R’, ‘Running’*/
ORDER BY FCR.ACTUAL_START_DATE DESC;

————

Cheers

Muhammad Rashed.

 
1 Comment

Posted by on December 5, 2012 in Queries

 

Tags: , ,