Helpful Queries

Query to check the number of Users logged in

select count(distinct d.user_name)

from apps.fnd_logins a, gv$session b, gv$process c, apps.fnd_user d

where b.paddr = c.addr

and a.pid=c.pid

and a.spid = b.process

and d.user_id = a.user_id

and (d.user_name = 'USER_NAME' OR 1=1);

How to check the database growth in previous months

select to_char(creation_time, 'RRRR Month') "Month",sum(bytes)/1024/1024

"Growth in MB" from sys.v_$datafile

where creation_time > SYSDATE-365

group by creation_time

Query to find the long running session ids and their work done

SELECT SID, SERIAL#, OPNAME, TARGET, SOFAR, TOTALWORK, UNITS,

TO_CHAR(START_TIME,'DD/MON/YYYY HH24:MI:SS') START_TIME,

TO_CHAR(LAST_UPDATE_TIME,'DD/MON/YYYY HH24:MI:SS') LAST_UPDATE_TIME,

TIME_REMAINING, ELAPSED_SECONDS, MESSAGE, USERNAME

FROM V$SESSION_LONGOPS

WHERE TIME_REMAINING != 0;

Query for finding the Blocking Sessions

SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) sess,

       id1, id2, lmode, request, type, inst_id

FROM GV$LOCK

WHERE (id1, id2, type) IN

   (SELECT id1, id2, type FROM GV$LOCK WHERE request>0)

     ORDER BY id1, request;

If the environment is RAC Enabled else use V$ instead of GV$

Query to get the session id from the request id

SELECT ses.sid,
ses.serial#
FROM v$session ses,
v$process pro
WHERE ses.paddr = pro.addr
AND pro.spid IN (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = &request_id);

Query to get the SPID

SELECT spid

FROM v$process

WHERE addr IN

(SELECT paddr FROM v$session WHERE sid=<> AND serial#=<>);


Query to get the request_id from the sid

select f.request_id, v.spid,s.sid, s.username,s.serial#, s.osuser, s.status

from gv$process v, gv$session s, applsys.fnd_concurrent_requests f

where 1=1

--and s.username=&USER_NAME

and s.paddr=v.addr

and f.oracle_process_id=v.spid

and trunc(f.request_date)=trunc(sysdate)

and sid=give the sid

Query to get the ddl of the database objects

select dbms_metadata.get_ddl('OBJECT_TYPE', 'OBJECT_NAME') from dual;

for example

select dbms_metadata.get_ddl('DB_LINK', 'APPS_TO_APPS') from dual;