SQls

Display the current cursor being executed by a session.
select a.sid, a.username, b.sql_textfrom v$session a, v$open_cursor b where a.saddr = b.saddr;

Display the entire SQL statement being executed by a session.
select a.sid, a.username, b.sql_textfrom v$session a, v$sqltext bwhere a.sql_address = b.addressand a.sql_hash_value = b.hash_valueorder by a.sid, a.username, b.piece;

Display each cursor of the shared cursor cache, including the times executed and loaded into the cache.
select sql_text, version_count, executions, loads from v$sqlarea;

Displays licensing information, including the maximum number of licensed concurrent or named users, the current number of active concurrent sessions, and the highest number of users that were concurrently logged on at anyone time since the last database startup.
select sessions_max, sessions_current, sessions_highwater, users_max from v$license;

Display all the init.ora parameters and the current values, along with signifying if the value is the default value.
select name, value, isdefault from v$parameter order by name;

Redo LogsDisplay details on the redo log files, including the filename, size, and archive status. select a.member, b.*from v$logfile a, v$log bwhere a.group# = b.group#;

Display a history of the archive logs created along with the file names.
select * from v$log_history;

Rollback SegmentsDisplay rollback information and determine if more segments are needed. If the waits to gets goes over one, then add more rollback segments.
select a.name, b.extents, b.rssize, b.xacts, b.waits, b.gets, optsize, statusfrom v$rollname a, v$rollstat bwhere a.usn = b.usn;

Determine the number of sessions for each user.
select username, count(*) from v$sessiongroup by username;
Display detail information about a session, including the operating system username and process, and terminal.
select sid, username, program, osuser, process, machine, terminal, typefrom v$session;

Display detailed statistics per session to determine a session’s resource usage.
select a.sid, a.username, b.name, c.valuefrom v$session a, v$statname b, v$sesstat cwhere a.sid = c.sidand b.statistic# = c.statistic#and a.username = upper(‘&username’)order by a.sid, a.username, b.name;

Determine the objects that are being accessed by each session.
select a.sid, a.username, b.owner, b.object, b.ob_typfrom v$session a, v$access bwhere a.sid = b.sid;

Display the processes for each session. If the process is a background process, then the operating system and identifier are retrieved from the process table.
select a.sid, decode(b.background,1,b.program,a.username) "user", b.pid,decode (b.background,1,b.spid,a.audsid) "os id"from v$session a, v$process bwhere a.paddr = b.addr;

How to get the Patch set level
Select patch_level from fnd_product_installation where patch_level like ‘%AD%’;

SELECT a.sid, a.username, b.sql_textFROM v$session a, v$sqltext bWHERE a.sql_address = b.addressAND a.sql_hash_value = b.hash_value
ORDER BY a.username, a.sid, b.piece

The following query can be used to determine all objects that are currently being accessed by a user.
SELECT a.sid, a.username, b.owner, b.object, b.ob_typFROM v$session a, v$access bWHERE a.sid = b.sid

What Indexes Are Available on a Table?
SELECT index_name, column_name, column_ positionFROM all_ind_columnsWHERE table_name = ‘ACCOUNT’ORDER BY index_name,column_position

How to get the responsibility of the users

SELECT UNIQUE u.user_id, u.user_name,r.responsibility_name,a.application_name FROM fnd_user u, fnd_user_resp_groups g, fnd_application_tl a, fnd_responsibility_tl r WHERE g.user_id = u.user_id AND g.responsibility_application_id = a.application_id AND a.application_id = r.application_id AND g.responsibility_id = r.responsibility_id and user_name like 'userid'
How to get concurrent request ... sid
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,apps.fnd_concurrent_processes b,
v$process c,v$session d WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.phase_code = 'P'
AND a.request_id = xxxxxxxxxx;

select REQUEST_ID , PHASE_CODE , STATUS_CODE, CONCURRENT_PROGRAM_ID, PARENT_REQUEST_ID ,ORACLE_PROCESS_ID,
ORACLE_SESSION_ID, OS_PROCESS_ID,QUEUE_ID from fnd_concurrent_requests where REQUEST_ID=55243326;
How to kill the concurrent request
update fnd_concurrent_requests set status_code ='D', phase_code = 'C' where request_id= request number;
How to find the version of WF
select TEXT from WF_RESOURCES where NAME='WF_VERSION';

LocK
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;

SELECT s.sid,s. serial#, s.last_call_et, s.MODULE, s.status,
s.machine,p. spid, s.osuser FROM v$process p, v$session s
WHERE p.addr = s.paddr and s.module like '&MODULE' and
s.last_call_et>3600;

select SID,SERIAL# ,LAST_CALL_ET,MODULE,STATUS from v$session where LAST_CALL_ET>3600 and MODULE='&MODULE';
select * from v$lock where LMODE=6 and block=1 order by sid;

select * from sys.dba_dml_locks order by session_id;
dead lock.... select * from v$lock where lmode>0 and id1 in (select distint id1 from v$lock where request >0)

fnd_concurrent_program_vl

select s.SID,s.SERIAL#,l.TIME_REMAINING,s.SQL_HASH_VALUE from v$session_longops l,v$session s where s.sid=l.sid and s.serial#=l.serial# and s.sid=$sid; .... to get the time for the query

select sum(BYTES/1024/1024) from dba_free_space where TABLESPACE_NAME like %XYZ%';
to get the free space

select sum( BYTES/1024/1024) from dba_data_files where TABLESPACE_NAME like '%SDBD%';
to get the total space

select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 from dba_data_files where TABLESPACE_NAME like '% BOMX %’
toget the location of the datafile

alter database datafile '/busdata/rp01/eur0/_lgi/oracle/vol24/gld02.dbf ' resize 3072M.... to alter the datafile



How to check the NLS patchs applied or not

SELECT driver.driver_file_name
,TO_CHAR(run.start_date,'DD-Mon HH24:MI:SS') start_date
,TO_CHAR(run.end_date ,'DD-Mon HH24:MI:SS') end_date
,lang.language
FROM ad_patch_runs run
,ad_patch_driver_langs lang
,ad_patch_drivers driver
,ad_applied_patches applied
WHERE run.patch_driver_id = driver.patch_driver_id
AND driver.applied_patch_id = applied.applied_patch_id
AND applied.patch_name = '&p_patch_number'
AND lang.patch_driver_id = driver.patch_driver_id
ORDER BY 1,2,3;



Workfolw
==========
select fsc.COMPONENT_NAME,fsc.STARTUP_MODE, fsc.COMPONENT_ STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_ queue_id( +)
and fcq.USER_CONCURRENT_QUEUE_NAME like '%Mail%';

Check Maintanance Mode
============ ========= =
select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;

Enable/Disable Maintanance Mode
============ ========= =========
sqlplus -s apps/@$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE
sqlplus -s apps/@$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

Find invalid Object Count
============ ========= =
Select count(*) from dba_objects where object_Name= 'INVALID' ;

Compile invalid object
============ =======
Alter package APPS.MSC_CL_PRE_PROCESS compile body;

Compile all invalid objects
============ ========= ==
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Find Applications Release
============ ========= ==
select release_name from fnd_product_ groups;


Find Number of Languages installed in Oracle Applications
============ ========= ========= ========= ========= ====
select nls_language, language_code,installed_flag
from apps.fnd_languages where installed_flag in ('I','B');

Database Name and Version
============ ========= ===
select instance_name, version from v$instance;

Find Number of Nodes in instance
============ ========= ========
select node_name,support_cp from fnd_nodes;


Perl Version
==========
perl -version

Java Version
===========
$AFJVAPRG -version

Apache Server Version
============ ========
$IAS_ORACLE_ HOME/Apache/Apache/bin/httpd -version

Form Server Version
============ =======
f60gen |grep -i version

Reports Server Version
============ =========
rwcon60 |grep -version

Find workflow version
============ =======
exec $FND_TOP/sql/wfver.sql

Find apps password
============ =====
grep password $APACHE_TOP/modplsql/cfg/wdbsvr.app

Find URL of the isntance
============ =========
env |grep -i forms

select profile_option_ value from apps.fnd_profile_option_values
where profile_option_value like '%http%';

How to get the concurrent Manger Name from requested

SELECT b.user_concurrent_queue_name

FROM 

fnd_concurrent_processes a

,fnd_concurrent_queues_vl b

,fnd_concurrent_requests c

WHERE 1=1

AND a.concurrent_queue_id = b.concurrent_queue_id

AND a.concurrent_process_id = c.controlling_manager

AND c.request_id = &request_id