Locks Info

----------------------------------
- Database Locks Information
-Gives Locks Details
-Session Information for specified Session ID
-Sql text for specified Session Info
-File Name : locks.sql
-Usage : locks
-----------------------------------

select l1.sid,'is blocking',l2.sid from v$lock l1,v$lock l2
where   l1.id1=l2.id1 and l1.id2=l2.id2 and l1.block=1 and l2.request>0 order by l1.sid
/
set line 200
col program for a16
col MODULE for a16
col action for a35
col 'Time Hrs' for 999999.99
col user@machine  for a35
col (SID,SERIAL#)  for a13
set line 150

select sid||','||serial# "(SID,SERIAL#)",status,last_call_et/3600 "Time Hrs",username||'@'||machine as "user@machine",program,module,action,type 
from gv$session where sid=&SID
/
select s.sql_text
FROM v$process p,v$session a,v$sqltext s,v$sqlarea sa
where 
    a.sid = &SID
and s.address = a.prev_sql_addr
AND p.addr = a.paddr
AND s.address=sa.address
AND a.username is not null
order by a.username, a.osuser, a.sid, s.piece
/

Concurrent Request Info

----------------------------------
-Concurrent Request Details
-Prompts for Request Id
-cr_info.sql
-usage : @cr_info
-----------------------------------

column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 100
set pages 22
set head off

SELECT '****************************************************************************************
Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'Execution Method  :' ||DECODE (execname.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
'N/A'
),'Executable Name  : '||execname.execution_file_name|| execname.subroutine_name,
'Phase : '||DECODE (req.phase_code,
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',
'N/A'
)||' | Status : '||DECODE (req.status_code,
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
'N/A'
),
'PID :'||proc.spid || ' | SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module,
'Actual Start Date :'||to_char(req.actual_start_date,'DD-MON-YYYY HH24:MI:SS')||' | Actual Completion Date :'||to_char(req.actual_completion_date,'DD-MON-YYYY HH:MI:SS'),
'Duration Hours :'||floor(((req.actual_completion_date-req.actual_start_date)*24*60*60)/3600)||
' Min :'||floor((((req.actual_completion_date-req.actual_start_date)*24*60*60) -
floor(((req.actual_completion_date-req.actual_start_date)*24*60*60)/3600)*3600)/60)||
' Sec :'||round((((req.actual_completion_date-req.actual_start_date)*24*60*60) -
floor(((req.actual_completion_date-req.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((req.actual_completion_date-req.actual_start_date)*24*60*60) -
floor(((req.actual_completion_date-req.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )),
'Log File Name : '||req.logfile_name,
'Out File Name : '||req.outfile_name,
'****************************************************************************************'
from apps.fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, apps.fnd_concurrent_programs_vl prog,
apps.fnd_executables execname
where req.request_id = &Request_ID
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id
/

Tablespace Free Space

----------------------
-Tablespace Free Space
-ts_free.sql
-usage : @ts_free
----------------------

SELECT tablespace_name,SUM(bytes)/1024/1024 "Free in MB"
FROM dba_free_space where tablespace_name='&T_NAME' GROUP BY tablespace_name
/

Sync Status Check on Primary Database

----------------------------------
-Sync Status to checked on Primary
-File Name : sync.sql
-USage : sync
-----------------------------------

select name,open_mode,database_role from v$database
/

set line 200
col DEST_NAME for a50
col BINDING for a10

select DEST_ID,DEST_NAME,STATUS,BINDING,ERROR from v$ARCHIVE_DEST where status<>'INACTIVE'
/

select PROCESS,STATUS,GROUP#,THREAD#,SEQUENCE#,BLOCK#,DELAY_MINS from v$managed_standby
/

col name for a30
col value for a40
select name,value from v$parameter where value like '%SERVICE%'
/

SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 999999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,LOG_ARCHIVED-LOG_APPLIED LOG_GAP FROM(SELECT NAME DB_NAME FROM v$DATABASE),
(SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),(INSTR(HOST_NAME,'.')-1))))) HOSTNAME FROM v$INSTANCE),
(SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM v$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),(SELECT MAX(SEQUENCE#) LOG_APPLIED FROM v$ARCHIVED_LOG
WHERE DEST_ID=2 AND APPLIED='YES' ),(SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM v$ARCHIVED_LOG
WHERE DEST_ID=2 AND APPLIED='YES')
/

DML Locks

----------------
-Locked Objects
----------------

select
            distinct to_name object_locked
from
            v$object_dependency
where
            to_address in
(
select /*+ ordered */
        w.kgllkhdl address
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
)
/

Locked Objects

----------------
-Locked Objects
----------------

set pages 100
set line 200
col "User Name" for a10
col owner for a10
col Object_type for a10
col "Lock Type" for a15
select substr(lpad(' ',decode(l.xidusn,0,3,0))||l.oracle_username,1,10) "User Name",substr(l.session_id,1,5)Sid,substr(S.SERIAL#,1,5) serial#,
s.status,to_char(s.logon_time,'dd-mon-yy hh24:mi') logon_time,substr(o.owner,1,10) Owner, o.object_name,substr(o.object_type,1,10) Object_type,
substr(decode(l.locked_mode,2,'Row Share',3,'Row exclusive',4,'Share mode',5,'Share row exclusive',6,'Exclusive mode'),1,15) "Lock Type" from 
gv$locked_object l ,all_objects o,gv$SESSION S where l.object_id=o.object_id and s.sid=l.session_id order by logon_time
/

Tablespace Info

-----------------------------------
-Tablespace less than 20% free
-Hot Backup Status
-Datafiles info for given Tablespace
------------------------------------

SELECT a.tablespace_name,b.size_kb,a.free_kb,Trunc((a.free_kb/b.size_kb) * 100) "FREE_%" FROM
(SELECT tablespace_name,Trunc(Sum(bytes)/1024) FREE_KB FROM   dba_free_space GROUP BY tablespace_name) a,
(SELECT tablespace_name,Trunc(Sum(bytes)/1024) SIZE_KB FROM   dba_data_files GROUP BY tablespace_name) b
WHERE  a.tablespace_name = b.tablespace_name AND Round((a.free_kb/b.size_kb) * 100,2) < 20
/
select unique status from v$backup
/
col file_name for a60
set line 150
col "Auto Extend" for a10
SELECT tablespace_name,file_name,autoextensible "Auto Extend",bytes/1024/1024 "Used in MB",maxbytes/1024/1024 "Max in MB",increment_by
FROM dba_data_files where tablespace_name='&T_NAME' order by file_name