Basic handy scripts for Apps DBA on reguler support

SQL> select instance_name,status,to_char(startup_time,'dd Mon, yyyy hh24:mi:ss') from gv$instance;

###########Script to get locks information########

column sess format a8 word_wrapped
column id1   format 999999999999
column id2   format 99999999
column req     format 999
column type  format a4
column "Module" format a30 word_Wrapped
SELECT lpad('-->',DECODE(request,0,0,5),' ')||sid sess
       , id1
       , id2
       , lmode
       , ctime
      ,  request req, type --, sanj_sessf(sid) "Module"
FROM V$LOCK
 WHERE id1 IN (SELECT id1 FROM gV$LOCK WHERE lmode = 0)
 ORDER BY id1,request;

###########Blocking sessions######

column blocker format a25
column blockee format a25
column sid_serial format a10

select
 (select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
 a.sid || ', ' ||
 (select serial# from v$session where sid=a.sid) sid_serial,
 ' is blocking ',
 (select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
 b.sid || ', ' ||
 (select serial# from v$session where sid=b.sid) sid_serial
 from v$lock a, v$lock b
 where a.block = 1
   and b.request > 0
   and a.id1 = b.id1
   and a.id2 = b.id2;




###########Scripts to get locks in RAC env ######################
SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
inst_id,sid sess, id1, id2, lmode, request, type,addr
FROM GV$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request > 0)
ORDER BY id1, request;

SQL> select sql_text from v$sqltext where HASH_VALUE = (select SQL_HASH_VALUE from v$session where sid = &n);

SQL> select sid,serial#,status,action,module,LAST_CALL_ET,WAIT_TIME,PROCESS from v$session where sid = &n;

###Steps to verify DR Synch###

select sequence# - 1
from v$log
where status = 'CURRENT';


select sequence#, applied,to_char(first_time, 'mm/dd/yy hh24:mi:ss') first from   v$archived_log order by first_time;


SQL> select * from (select SEQUENCE#,FIRST_TIME,APPLIED from v$archived_log order by SEQUENCE# desc) where rownum <=150;


#####Script for Nodes Information#############

spool fnd_nodes
set pagesize 50
col node_name format a15
col server_id format a9
col server_address format a15
col platform_code format a4
col webhost format a12
col domain format a18
col virtual_ip format a12
set linesize 132
select
  node_id,
  platform_code,
  support_db D,
  support_cp C,
  support_admin A,
  support_forms F,
  support_web W,
  node_name,
  server_id,
  server_address,
  domain,
  webhost,
  virtual_ip
from
  fnd_nodes
order by node_id;

###############Invalids##############
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;

col object_name for a30
col owner for a10
select object_name,owner,object_type,status
from DBA_OBJECTS where status like 'INVALID%'
order by object_name;


select owner, object_name,object_type from dba_objects where status ='INVALID';