Oracle Database Up Time

The below query help  you out to find the open_mode,log_mode and startup time of the oracle database.

set linesize 200
set trimout on
set space 1
col INSTANCE for a10
col MODE for a11
col ROLE for a10
col HOST for a20
col STATUS for a7
col STARTUP_TIME for a20

select NAME "DB_NAME",INSTANCE_NAME "INSTANCE",OPEN_MODE,LOG_MODE,STATUS,DATABASE_ROLE "ROLE",HOST_NAME "HOST", to_char(STARTUP_TIME,'MON/DD/YYYY hh24:mi:ss')startup_time from gv$database NATURAL JOIN gv$instance; 

DB_NAME   INSTANCE    OPEN_MODE   LOG_MODE       STATUS   ROLE           HOST                 STARTUP_TIME
--------- ---------- ----------- ------- ---------- -------------------- ------------------------------------------------------------

PROD         PROD     READ WRITE        ARCHIVELOG    OPEN    PRIMARY   ibxdsdgfhhf         AUG/10/2015 12:10:26

 Checking for Blocking Locks

 Connect to you Database and query the "dba_blockers" view

SQL> select * from dba_blockers;

HOLDING_SESSION
---------------
           121

 Query to check which Session  is blocking

SQL> 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 ;

BLOCKING_STATUS
--------------------------------------------------------------------

 ( SID=121 ) is blocking  ( SID=345 )
 Killing Blocking Locks

ALTER SYSTEM KILL SESSION '121';