Some useful queries used in day to day operations

Checking locks in database

SELECT b.session_id AS sid, NVL(b.oracle_username, ‘(oracle)’) AS username,

a.owner AS object_owner,a.object_name,Decode(b.locked_mode, 0, ‘None’,

1, ‘Null (NULL)’,2, ‘Row-S (SS)’,3, ‘Row-X (SX)’,

4, ‘Share (S)’,5, ‘S/Row-X (SSX)’,6, ‘Exclusive (X)’,

b.locked_mode) locked_mode,b.os_user_name

FROM   dba_objects a,v$locked_object b

WHERE  a.object_id = b.object_idORDER BY 1, 2, 3, 4;

Memory allocation per session:

SELECT NVL(a.username,’(oracle)’) AS username,

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    b.statistic# = c.statistic#

AND    c.name = ’session pga memory’ AND    a.program IS NOT NULL

ORDER BY b.value DESC; n  Monitor session details:

SELECT s.sid, s.status, s.process, s.schemaname,  s.osuser, a.sql_text,

p.program FROM   v$session s, v$sqlarea a, v$process p

WHERE  s.SQL_HASH_VALUE = a.HASH_VALUE

AND    s.SQL_ADDRESS = a.ADDRESS

AND    s.PADDR = p.ADDR

Find user privileges

SELECT LPAD(‘ ‘, 2*level) || granted_role “USER PRIVS”

FROM (SELECT NULL grantee,  username granted_role

FROM dba_users WHERE username LIKE UPPER(‘%&uname%’)

UNION SELECT grantee, granted_role

FROM dba_role_privs UNION

SELECT grantee, privilege FROM dba_sys_privs)

START WITH grantee IS NULL

CONNECT BY grantee = prior granted_role;

Find current SQL in database:

select u.sid, substr(u.username,1,12) user_name,

s.sql_text from

v$sql s, v$session u

wheres.hash_value = u.sql_hash_value

and

sql_text not like ‘%from v$sql s, v$session u%’

order by u.sid;