Oracle Database: Utility Queries

--*****************************************************************************************************
--***************************** To find value of parameters set in init.ora****************************
--*****************************************************************************************************
select name,value from v$parameter where name = 'utl_file_dir'
--*****************************************************************************************************
--***************************** To find object information ****************************************
--*****************************************************************************************************
-- for all objects user dba_<xxxx> views
select * from dba_objects -- gives details of all types of objects
select * from dba_procedures
select * from dba_tables
select * from dba_indexes
-- for objects owned by user use user_<xxxx> views
select * from user_objects -- gives details of all types of objects
select * from user_procedures
select * from user_tables
select * from user_indexes
--*****************************************************************************************************
--***************************** To find current sessions running on a database*************************
--*****************************************************************************************************
select distinct
sid
,s.serial#
,s.module
,lockwait
,s.schemaname
,s.username
,osuser
,machine
,s.terminal
,s.program
,status
,q.sql_text
from v$session s
,v$process p
,v$sqlarea q
where p.addr(+)=s.paddr
and q.hash_value(+)=s.sql_hash_value
and q.address(+)=s.sql_address
and status = 'ACTIVE'
order by sid desc
--*****************************************************************************************************
--***************************** To kill a session on a database *************************************
--*****************************************************************************************************

--ALTER SYSTEM KILL SESSION 'SID, SER#';
alter system kill session '5,390'; -- SID and SERIAL# received from above query.

--*****************************************************************************************************
--***************************** To get the current execution of cursor *******************************
--*****************************************************************************************************
SELECT Executions,
loads,
first_load_time ,
elapsed_time
FROM V$sqlarea
WHERE hash_value in (SELECT sql_hash_value
FROM V$session
WHERE sid = 48 );
--*****************************************************************************************************
--**************** To check which processes have locked a resource (generally a Table)*****************
--*****************************************************************************************************

SELECT c.sid,
c.serial#,
c.username,
a.object_id,
b.object_name,
c.program,
c.status,
d.name,
c.osuser
FROM v$Locked_object a,
all_objects b,
v$session c,
audit_actions d
WHERE a.object_id=b.object_id
AND a.session_id =c.sid(+)
AND c.command=d.action;
--*****************************************************************************************************
--***************************** To check ACTIVE session in any instnace *******************************
--*****************************************************************************************************
SELECT s.osuser, s.sid, s.sql_hash_value, s.module, w.event
FROM v$session s, v$session_wait w
WHERE s.sid = w.sid
AND s.status = 'ACTIVE'
AND event not like 'rdbms ipc message'
AND event not like 'pipe get'
AND event not like 'queue messages'
ORDER BY s.module

--*****************************************************************************************************
--***************************** To check current activity done by the REQUEST_ID **********************
--*****************************************************************************************************
SELECT fcr.request_id
,st.sql_text
,fcr.status_code
,s.sid
,s.serial#
,s.*
FROM apps.fnd_concurrent_requests fcr
,v$session s
,v$sqltext st
WHERE fcr.os_process_id = s.process (+)
AND s.sql_hasH_VALUE = st.hash_value (+)
AND fcr.request_id = 18740879
ORDER BY fcr.request_id, st.piece
--*****************************************************************************************************
--****************************************** To check deadlock ****************************************
--*****************************************************************************************************
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 ;