AppsDba used scripts

1. SQL statements for the current database sessions with PID.

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
c.value AS &1,
a.lockwait,
a.status,
a.module,
a.machine,
a.program,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$sesstat c,
v$statname d
WHERE a.sid = c.sid
AND c.statistic# = d.statistic#
AND d.name = DECODE(UPPER('&1'), 'READS', 'session logical reads',
'EXECS', 'execute count',
'CPU', 'CPU used by this session',
'CPU used by this session')
ORDER BY c.value DESC;

2 . Memory allocation for the current running sessions.

COLUMN username FORMAT A20
COLUMN module FORMAT A20

SELECT NVL(a.username,'(oracle)') AS username,
a.sid,
a.module,
a.program,
Trunc(b.value/1024) AS memory_kb
FROM v$session a,
v$sesstat b,
v$statname c
WHERE a.sid = b.sid and a.username is not null
AND b.statistic# = c.statistic#
AND c.name = 'session pga memory'
AND a.program IS NOT NULL ORDER BY b.value DESC;

3. DB Info
column hostname format a13
column stime format a35
column uptime format a35
column instance_name format a10
prompt "DB Info"
select host_name Hostname
,instance_name InstanceName
,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
trunc( 24*((sysdate-startup_time) -
trunc(sysdate-startup_time))) || ' hour(s) ' ||
mod(trunc(1440*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
mod(trunc(86400*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from sys.v_$instance;

4. Block Contention

SELECT class,sum(count) total_waits, sum(time) total_time FROM v$waitstat GROUP BY class;

5. Latch Contention

SELECT a.name,100.*b.sleeps/b.gets ratio1,100.*b.immediate_misses/decode((b.immediate_misses+b.immediate_gets),0,1) ratio2
FROM v$latchname a, v$latch b WHERE
a.latch# = b.latch# AND b.sleeps > 0;

6. Find Chained rows

select owner, table_name, pct_free, pct_used, avg_row_len, num_rows, chain_cnt,(chain_cnt/num_rows*100) as perc from dba_tables where owner not in ('SYS','SYSTEM') and table_name not in (select table_name from dba_tab_columns
where data_type in ('RAW','LONG RAW') )
and chain_cnt > 0 order by chain_cnt desc;

7. Locks

select /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
/

8. Information on top sessions ordered by Reads / executions

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
c.value AS &1,
a.lockwait,
a.status,
a.module,
a.machine,
a.program,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$sesstat c,
v$statname d
WHERE a.sid = c.sid
AND c.statistic# = d.statistic#
AND d.name = DECODE(UPPER('&1'), 'READS', 'session logical reads',
'EXECS', 'execute count',
'CPU', 'CPU used by this session',
'CPU used by this session')
ORDER BY c.value DESC;

9. Top SQL statements that are using the most resources

SELECT *
FROM (SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 2 DESC) WHERE rownum <= 10

10. Undo information on relevant database sessions

SELECT s.username,
s.sid,
s.serial#,
t.used_ublk,
t.used_urec,
rs.segment_name,
r.rssize,
r.status
FROM v$transaction t,
v$session s,
v$rollstat r,
dba_rollback_segs rs
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

11. I/O information on top Physical reads/ Consistent get sessions

COLUMN username FORMAT A15

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
si.block_gets,
si.consistent_gets,
si.physical_reads,
si.block_changes,
si.consistent_changes
FROM v$session s,
v$sess_io si
WHERE s.sid = si.sid
ORDER BY s.username, s.osuser;

12. List of Non Indexed Foreign key.

SELECT t.table_name,
c.constraint_name,
c.table_name table2,
acc.column_name
FROM all_constraints t,
all_constraints c,
all_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name
AND c.table_name = acc.table_name
AND c.constraint_name = acc.constraint_name
AND NOT EXISTS (SELECT '1'
FROM all_ind_columns aid
WHERE aid.table_name = acc.table_name
AND aid.column_name = acc.column_name)
ORDER BY c.table_name;

How do I know which version of EBS I have.? 
select release_name from apps.fnd_product_groups;

free/used temporary table space :

 SELECT tablespace_name,SUM(bytes_used),SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name;

free/used  table space :

 select t.tablespace, t.totalspace as " Totalspace(MB)",

 round((t.totalspace-fs.freespace),2) as "Used Space(MB)",

 fs.freespace as "Freespace(MB)", round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used",

 round((fs.freespace/t.totalspace)*100,2) as "% Free"

 from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace

 from dba_data_files d group by d.tablespace_name) t,

 (select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace

 from dba_free_space f group by f.tablespace_name) fs

 where t.tablespace=fs.tablespace order by t.tablespace;