Useful Queries for Oracle Core/Apps/EBS/Exadata Database

1) Query to find out the responsibilities attached to a user in Oracle Application

select b.user_name, c.responsibility_name, a.START_DATE, a.END_DATE from fnd_user_resp_groups_direct a, fnd_user b, fnd_responsibility_tl c
where a.user_id = b.user_id
and a.responsibility_id = c.responsibility_id
and b.user_name='&Username';

2) Query to find out Free Tablespace with Graph in %

set lines 200
select  total.ts tablespace,
        DECODE(total.mb,null,'OFFLINE',dbat.status) status,
    total.mb total_mb,
    NVL(total.mb - free.mb,total.mb) used_mb,
    NVL(free.mb,0) free_mb,
        DECODE(total.mb,NULL,0,NVL(ROUND((total.mb - free.mb)/(total.mb)*100,2),100)) pct_used,
    CASE WHEN (total.mb IS NULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'
    ELSE '['|| DECODE(free.mb,
                             null,'XXXXXXXXXXXXXXXXXXXX',
                             NVL(RPAD(LPAD('X',trunc((100-ROUND( (free.mb)/(total.mb) * 100, 2))/5),'X'),20,'-'),
        '--------------------'))||']'
         END as GRAPH
from
    (select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,
    (select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free,
        dba_tablespaces dbat
where total.ts=free.ts(+) and
      total.ts=dbat.tablespace_name
UNION ALL
select  sh.tablespace_name,
        'TEMP',
    SUM(sh.bytes_used+sh.bytes_free)/1024/1024 total_mb,
    SUM(sh.bytes_used)/1024/1024 used_mb,
    SUM(sh.bytes_free)/1024/1024 free_mb,
        ROUND(SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free)*100,2) pct_used,
        '['||DECODE(SUM(sh.bytes_free),0,'XXXXXXXXXXXXXXXXXXXX',
              NVL(RPAD(LPAD('X',(TRUNC(ROUND((SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free))*100,2)/5)),'X'),20,'-'),
                '--------------------'))||']'
FROM v$temp_space_header sh
GROUP BY tablespace_name order by 6;

3) Query To Check Blocking & Waiting Sessions in RAC

select vsb.sid bsid, vsb.serial#, vsb.inst_id binst, vsb.username blocker, vsb.client_identifier bcid, vsb.module bmod,
       vsw.sid wsid, vsw.serial#,  vsw.inst_id winst, vsw.username waiter, vsw.client_identifier wcid, vsw.module wmod,
       trunc(vlw.ctime/60) minutes
  from (select inst_id,sid, id1, id2, ctime from gv$lock where request > 0) vlw,
       (select inst_id,sid, id1, id2, ctime from gv$lock where block > 0) vlb,
       gv$session vsb,
       gv$session vsw
  where vsw.sid=vlw.sid
    and vsb.sid=vlb.sid
    and vsb.inst_id=vlb.inst_id
    and vsw.inst_id=vlw.inst_id
    and vlb.id1=vlw.id1
    and vlb.id2=vlw.id2
order by blocker, minutes desc;

4) Query to find object or statement that is causing excessive redo generation

SELECT dhso.object_name,
sum(db_block_changes_delta)
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND begin_interval_time BETWEEN to_date('2012_05_02 04','YYYY_MM_DD HH24') AND to_date('2012_05_02 07','YYYY_MM_DD HH24')
GROUP BY dhso.object_name
order by sum(db_block_changes_delta) desc;

OBJECT_NAME                    SUM(DB_BLOCK_CHANGES_DELTA)

—————————— —————————
XXXXXXX                                     315744448

Find the statement related to it.

SELECT distinct dbms_lob.substr(sql_text,4000,1)
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE '%XXXXXXXXXX%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND dhss.sql_id = dhst.sql_id and rownum<2;

DBMS_LOB.SUBSTR(SQL_TEXT,4000,1)
——————————————————————————–
update XXXXXXXX set LATEST_VERSION=:1  where ID=:2  and VERSION<:3

5) To find the sid from Concurrent Request ID and associated SQL Statement

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$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.request_id =&req_id

AND a.phase_code = 'R';

select a.sid, a.serial#, b.sql_text
from gv$session a, gv$sqlarea b
where a.sql_address=b.address

and a.sid = &sid and a.inst_id=&inst_id;

6)  To find Problematic Particular Query through sid on Exadata

set long 50000000 
set pagesize 10000 

select name,value from v$sesstat a, v$statname b where 
(a.STATISTIC# = b.STATISTIC#) and 
(a.sid) = <sid> and 
(name in ( 'cell physical IO interconnect bytes returned by smart scan', 
'physical read IO requests', 
'physical read requests optimized', 
'cell physical IO bytes saved by storage index', 
'cell physical IO bytes eligible for predicate offload', 
'cell num smart IO sessions using passthru mode due to cellsrv', 
'cell num smart IO sessions using passthru mode due to user', 
'cell flash cache read hits', 
'db block gets', 
'db block gets from cache', 
'db block gets from cache (fastpath)', 
'db block gets direct')) order by name;