Handy collection of SQL's for Oracle

This is a collection of Oracle SQL’s that will come handy when you are in trouble. This includes finding who locked a record, finding SQL which is still executing, getting full SQL executed by application, find long running jobs, get time when the table was created, finding uptime of database. This is gathered from various blogs and stackoverflow.com answers.

All these SQL uses V$ views and one will need SELECT_CATALOG_ROLE role to run these queries.

Find sessions that blocks each other

Find locks on records that are caused by two update operations on same record in different sessions. This often happens when one person updates a record using a SQL client and does not commit the change and the application/batch job tries to updates the same record. Then the second application waits forever hoping to acquire the lock.

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 l2.id2 = l2.id2 ;

Find tables which are locked

Whenever an UPDATE/DELETE/SELECT FOR UPDATE operation is performed, Oracle will have a lock on that table. These locks are not dead locks. Following SQL displays such locks.


SELECT object_name, s.sid, s.serial#, p.spid, s.username, s.machine FROM v$locked_object l, dba_objects o, v$session s, v$process p
    WHERE l.object_id = o.object_id AND l.session_id = s.sid AND s.paddr = p.addr;

Find open cursors in the database

This can be used to find out what SQL is currently executed. Also one can look at this table to find out what SQL’s are not closed by the application. When the cursors are not closed Oracle will give maximum open cursors exceeded error. This can be a reason for connection leaks. Look for SQL_ID and SQL_TEXT fields. SQL_ID can be used to get full text of executed SQL.

SELECT * FROM v$open_cursor

Find full text of executed SQL

Get the SQL_ID from V$OPEN_CURSOR. Even if the SQL execution was complete, this will be available in GV$SQL table.

SELECT * FROM gv$sql WHERE sql_id = '0d2h15rhjpc9x'

Find long running operations in the database

Using this we can check the progress of long running queries. This also shows Oracle back ground jobs like generating statistics, archival logs etc.

SELECT sid, to_char(start_time,'hh24:mi:ss') start_time,
      trunc(sysdate-start_time) || ':' ||
      trunc(mod((sysdate-start_time)*24, 24))  || ':'||
      trunc(mod((sysdate-start_time)*24*60, 60)) || ':' ||
      trunc(mod((sysdate-start_time)*24*60*60, 60)) duration,
      message,Round((sofar/totalwork)* 100,2) percent
FROM v$session_longops WHERE sofar/totalwork < 1 AND totalwork > 0

Find when a table was created 

It is useful to know when a table was created.

SELECT object_name,owner,created FROM dba_objects

 WHERE object_name = EMP AND owner = 'SCOTT' AND object_type = 'TABLE'

Find uptime of the database

This query returns when the database was started. Useful to troubleshoot whether the connection error in application is caused by a database server restart.

SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM sys.v_$instance;