Daily used queries

select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time" from v$instance;
lsof|grep CLOSE_WAIT|grep -i f60web
alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS';
select WAITING_SESSION,HOLDING_SESSION  from dba_waiters;
select spid,addr from v$process where spid=&1;

select sid,serial#,program,module,action,status,last_call_et from v$session where sid=&1;

select sid,serial#,program,module,action,status,username,event,last_call_et from v$session where paddr='&1';

select concurrent_program_name,USER_CONCURRENT_PROGRAM_NAME,CONCURRENT_PROGRAM_ID from apps.fnd_concurrent_programs_vl where concurrent_program_name='&1';

select request_id,ACTUAL_START_DATE,ACTUAL_COMPLETION_DATE,phase_code,status_code from apps.fnd_concurrent_requests where CONCURRENT_PROGRAM_ID=&1 order  by ACTUAL_START_DATE;

select request_id,ACTUAL_START_DATE,ACTUAL_COMPLETION_DATE from apps.fnd_concurrent_requests where CONCURRENT_PROGRAM_ID=32766 and ACTUAL_START_DATE like '%22-APR-10%';


select EMAIL_ADDRESS from apps.fnd_user where USER_NAME='&username';


SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,APPLIED  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,APPLIED  FROM V$ARCHIVED_LOG where SEQUENCE#=&1 ;

SELECT DEST_ID "ID",STATUS "DB_status",DESTINATION "Archive_dest",ERROR "Error" FROM V$ARCHIVE_DEST WHERE DEST_ID <=5;


select sid,serial#,program,module,action,status,username from v$session where process='&1';



select * from (select request_id,ACTUAL_START_DATE,ACTUAL_COMPLETION_DATE from apps.fnd_concurrent_requests where CONCURRENT_PROGRAM_ID=32766 order  by ACTUAL_START_DATE DESC) WHERE rownum<=30;






Checking locks on tables v$session,v$lock,v$locked_object(OBJECT_ID),dba_objects(OBJECT_ID)

SELECT * FROM (SELECT HIREDATE FROM emp ORDER BY HIREDATE DESC) WHERE rownum<=5;


SELECT * FROM (select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_ID from apps.fnd_concurrent_requests where OS_PROCESS_ID=14340 order by ACTUAL_START_DATE DESC) WHERE rownum<=5;

select * from (select request_id,ACTUAL_START_DATE,ACTUAL_COMPLETION_DATE,phase_code,status_code from apps.fnd_concurrent_requests where CONCURRENT_PROGRAM_ID=&1 order  by ACTUAL_START_DATE desc) WHERE rownum<=5;

netstat -anp | grep 834
lsof -i | grep 834


select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s join v$sqltext_with_newlines q on s.sql_address = q.address where status='ACTIVE' and type <>'BACKGROUND' and last_call_et> 60 order by sid,serial#,q.piece;


select request_id,ACTUAL_START_DATE from applsys.fnd_concurrent_requests where hold_flag='Y';

select * from (select request_id,ACTUAL_START_DATE,ACTUAL_COMPLETION_DATE,phase_code,status_code from applsys.fnd_concurrent_requests where CONCURRENT_PROGRAM_ID=&1 order by ACTUAL_START_DATE desc) WHERE rownum<=30;



select concurrent_program_name,USER_CONCURRENT_PROGRAM_NAME,CONCURRENT_PROGRAM_ID from apps.fnd_concurrent_programs_vl where CONCURRENT_PROGRAM_ID ='&1';


select sid,serial#,program,module,action,status,username,last_call_et from v$session where ACTION like '%FRM%' and status='INACTIVE' order by LAST_CALL_ET


select concurrent_program_name,USER_CONCURRENT_PROGRAM_NAME,CONCURRENT_PROGRAM_ID from apps.fnd_concurrent_programs_vl where USER_CONCURRENT_PROGRAM_NAME  like '&1';


select * from (select request_id,ACTUAL_START_DATE,ACTUAL_COMPLETION_DATE,phase_code,status_code,REQUESTOR from apps.FND_CONC_REQ_SUMMARY_V where CONCURRENT_PROGRAM_ID=&1 order by ACTUAL_START_DATE desc) WHERE rownum<=10;




select sid,serial#,program,module,action,status,last_call_et from v$session where paddr



select sid,start_time,totalwork sofar,(sofar/totalwork) * 100 pct_done from v$session_longops where totalwork > sofar AND opname NOT LIKE '%aggregate%' AND opname like 'RMAN%';



select * from (select opname, target, sofar, totalwork,units, elapsed_seconds, message from v$session_longops where sid = <sid> and serial# = <serial#> order by start_time desc) where rownum <=1;



SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;



SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
/


Display a list of snapshots

 set lines 100 pages 999
select snap_id
, snap_level
, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
from dba_hist_snapshot
order by 1
/


To see the snapshot interval and retention period

 col snap_interval format a30
col retention format a30
select snap_interval
, retention
from dba_hist_wr_control
/



select sid,serial#,program,module,action,status,username,event,last_call_et from v$session where sid='&n';





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 V$LOCK WHERE lmode = 0)
ORDER BY id1,request
/