Check locked Objects in Oracle database

LOCKED DATABASE OBJECTS

SELECT l.session_id || ',' || v.serial# sid_serial,       
       l.ORACLE_USERNAME ora_user,       
       o.object_name,       
       o.object_type,       
       DECODE(l.locked_mode,              
              0,
              'None',              
              1,
              'Null',              
              2,
              'Row-S (SS)',              
              3,
              'Row-X (SX)',              
              4,
              'Share',              
              5,
              'S/Row-X (SSX)',              
              6,
              'Exclusive',              
              TO_CHAR(l.locked_mode)              
              ) lock_mode,       
       o.status,       
       to_char(o.last_ddl_time, 'dd.mm.yy') last_ddl
  FROM dba_objects o, gv$locked_object l, v$session v
 WHERE o.object_id = l.object_id      
   and l.SESSION_ID = v.sid;

select a.sid, a.serial#, OBJECT_NAME , a.*
  from v$session a, v$locked_object b, dba_objects c
 where b.object_id = c.object_id
   and a.sid = b.session_id;
 
ALTER SYSTEM KILL SESSION 'sid,serial#';

SELECT T.*, sa.SQL_TEXT, sa.SQL_FULLTEXT, sa.BIND_DATA
  FROM (select c.owner,
               c.object_name,
               c.object_type,
               b.sid,
               b.serial#,
               b.status,
               a.ORACLE_USERNAME,
               b.osuser,
               b.machine,
               DECODE(a.locked_mode,
                      0,
                      'None',
                      1,
                      'Null',
                      2,
                      'Row-S (SS)',
                      3,
                      'Row-X (SX)',
                      4,
                      'Share',
                      5,
                      'S/Row-X (SSX)',
                      6,
                      'Exclusive',
                      TO_CHAR(a.locked_mode)) alock_mode,
               b.STATUS SESSION_STATUS,
               b.LOGON_TIME,
               b.SQL_HASH_VALUE
          from v$locked_object a, v$session b, dba_objects c
         where b.sid = a.session_id
           and a.object_id = c.object_id) T
  left join v$sqlarea sa
    ON sa.HASH_VALUE = t.SQL_HASH_VALUE;
 

select a.sid, a.serial#, OBJECT_NAME, a.LOGON_TIME
  from v$session a, v$locked_object b, dba_objects c
 where b.object_id = c.object_id
   and a.sid = b.session_id;
    
    
SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION
  FROM v$session
 WHERE BLOCKING_SESSION IS NOT NULL;
  
  
 SELECT l1.sid || ' is blocking ' || l2.sid blocking_sessions
  FROM v$lock l1, v$lock l2
 WHERE l1.block = 1
   AND l2.request > 0
   AND l1.id1 = l2.id1
   AND l1.id2 = l2.id2;

SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid ||
       ' )  is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||
       s2.sid || ' ) ' AS blocking_status
  FROM v$lock l1, v$session s1, v$lock l2, v$session s2
 WHERE s1.sid = l1.sid
   AND s2.sid = l2.sid
   AND l1.BLOCK = 1
   AND l2.request > 0
   AND l1.id1 = l2.id1
   AND l1.id2 = l2.id2;
    

select * from v$lock where type='TX' and request>0;
select * from v$lock where type='TX' and lmode>0;

Reference:

• Finding oracle locked objects

Oracle Sql scripts

User , Role Privileges

select * from dba_tab_privs;
select * from dba_role_privs WHERE granted_ROLE= 'SHARED_USER';
select * from dba_sys_privs;
 
select * from all_tab_privs;
select * from all_role_privs;
select * from all_sys_privs;
 
select * from user_tab_privs;
select * from user_role_privs;
select * from user_sys_privs;
 
select * from role_tab_privs;
select * from role_role_privs;
select * from role_sys_privs;
 
 
select * from role_tab_privs where role = 'SHARED_USER';
select * from role_role_privs  where role = 'SHARED_USER';
select * from role_sys_privs  where role =  'SHARED_USER'
 
select count(*) from dba_users;
select * from user_role_privs;
select * from dba_roles where role = 'ADMIN'

Stats Details

-To verify stats
 
select * from user_tab_statistics;
select * from user_tab_col_statistics;

Force Index to use

To use a full scan or a range scan.
http://www.dba-oracle.com/t_v_sql_hint.htm
 
 
 
 
SELECT /*+ FULL(m)         */ b FROM mytable m WHERE b=10; -- full table scan
SELECT /*+ INDEX_RS_ASC(m) */ b FROM mytable m WHERE b=10; -- index range scan
SELECT /*+ INDEX_FFS(m)    */ b FROM mytable m WHERE b=10; -- index fast full scan

Get the Explain plan for sql query

select sql_id, child_number , s.*  
from gv$sql s 
WHERE upper(SQL_FULLTEXT) like '%TABLE_NAME%' 
order by last_active_time desc;

SELECT * 
FROM TABLE(dbms_xplan.display_cursor('frmxr4w3tb9wv', 0));

Check time consuming SQL queries in Oracle

SELECT * FROM
(SELECT
    sql_fulltext,
    sql_id,
    child_number,
    disk_reads,
    executions,
    first_load_time,
    last_load_time
FROM    v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 20
 
 
 
SELECT Disk_Reads DiskReads, Executions, SQL_ID, SQL_Text SQLText,
   SQL_FullText SQLFullText
FROM
(
   SELECT Disk_Reads, Executions, SQL_ID, LTRIM(SQL_Text) SQL_Text,
      SQL_FullText, Operation, Options,
      Row_Number() OVER
         (Partition By sql_text ORDER BY Disk_Reads * Executions DESC)
         KeepHighSQL
   FROM
   (
       SELECT Avg(Disk_Reads) OVER (Partition By sql_text) Disk_Reads,
          Max(Executions) OVER (Partition By sql_text) Executions,
          t.SQL_ID, sql_text, sql_fulltext, p.operation,p.options
       FROM v$sql t, v$sql_plan p
       WHERE t.hash_value=p.hash_value AND p.operation='TABLE ACCESS'
       AND p.options='FULL' AND p.object_owner NOT IN ('SYS','SYSTEM')
       AND t.Executions > 1
   )
   ORDER BY DISK_READS * EXECUTIONS DESC
)
WHERE KeepHighSQL = 1
AND rownum <=5;
 
 
SELECT username,
       buffer_gets,
       disk_reads,
       executions,
       buffer_get_per_exec,
       parse_calls,
       sorts,
       rows_processed,
       hit_ratio,
       module,
       sql_text
       -- elapsed_time, cpu_time, user_io_wait_time, ,
  FROM (SELECT sql_text,
               b.username,
               a.disk_reads,
               a.buffer_gets,
               trunc(a.buffer_gets / a.executions) buffer_get_per_exec,
               a.parse_calls,
               a.sorts,
               a.executions,
               a.rows_processed,
               100 - ROUND (100 * a.disk_reads / a.buffer_gets, 2) hit_ratio,
               module
               -- cpu_time, elapsed_time, user_io_wait_time
          FROM v$sqlarea a, dba_users b
         WHERE a.parsing_user_id = b.user_id
           AND b.username NOT IN ('SYS', 'SYSTEM', 'RMAN','SYSMAN')
           AND a.buffer_gets > 10000
         ORDER BY buffer_get_per_exec DESC)
 WHERE ROWNUM <= 20

Dynamic Oracle Views

SELECT * FROM V$FIXED_VIEW_DEFINITION;
SELECT * FROM V$FIXED_TABLE;
SELECT NAME, TYPE FROM V$FIXED_TABLE WHERE NAME LIKE 'V$%';

LOCKED DATABASE OBJECTS

SELECT l.session_id || ',' || v.serial# sid_serial,      
       l.ORACLE_USERNAME ora_user,      
       o.object_name,      
       o.object_type,      
       DECODE(l.locked_mode,             
              0,
              'None',             
              1,
              'Null',             
              2,
              'Row-S (SS)',             
              3,
              'Row-X (SX)',             
              4,
              'Share',             
              5,
              'S/Row-X (SSX)',             
              6,
              'Exclusive',             
              TO_CHAR(l.locked_mode)             
              ) lock_mode,      
       o.status,      
       to_char(o.last_ddl_time, 'dd.mm.yy') last_ddl
  FROM dba_objects o, gv$locked_object l, v$session v
 WHERE o.object_id = l.object_id     
   and l.SESSION_ID = v.sid;


select a.sid, a.serial#, OBJECT_NAME , a.*
  from v$session a, v$locked_object b, dba_objects c
 where b.object_id = c.object_id
   and a.sid = b.session_id;
 
 
ALTER SYSTEM KILL SESSION 'sid,serial#';
 
 
--Dynamic spool name
column filename new_val filename
select 'alarm_query_'||  sys_context('userenv','db_name') || '_' || to_char(sysdate, 'yyyymmdd' ) filename from dual;
spool '&filename'
  
set heading OFF;
--set serveroutput on;
select 'User : ' || user from dual;
select 'Database : ' || ora_database_name from dual;

SQL