How to Get the Locked Tables List In Oracle

Get the Locked Tables List

SELECT l.inst_id,

    SUBSTR (L.ORACLE_USERNAME, 1, 8) ORA_USER,

    SUBSTR (L.SESSION_ID, 1, 3) SID, S.serial#,

    SUBSTR (O.OWNER || '.' || O.OBJECT_NAME, 1, 40) OBJECT,

    P.SPID OS_PID,

    DECODE (L.LOCKED_MODE,

           0, 'NONE',

           1, 'NULL',

           2, 'ROW SHARE',

           3, 'ROW EXCLUSIVE',

           4, 'SHARE',

           5, 'SHARE ROW EXCLUSIVE',

           6, 'EXCLUSIVE',

           NULL)

      LOCK_MODE

FROM sys.GV_$LOCKED_OBJECT L,

    DBA_OBJECTS O,

    sys.GV_$SESSION S,

    sys.GV_$PROCESS P

WHERE L.OBJECT_ID = O.OBJECT_ID

    AND l.inst_id = s.inst_id

    AND L.SESSION_ID = S.SID

    AND s.inst_id = p.inst_id

    AND S.PADDR = P.ADDR(+)

ORDER BY   l.inst_id;

And to get the details of a particular session given by the sid in the above query use this query

select STATUS ,  PROCESS , PROGRAM , LOGON_TIME  from v$session where sid=

This will give you queries currently running for more than 60 seconds. Note that it prints multiple lines per running query if the SQL has multiple lines. Look at the sid,serial# to see what belongs together.

SELECT s.username,

    s.sid,

    s.serial#,

    s.last_call_et / 60 mins_running,

    q.sql_text    

FROM v$session s

JOIN v$sqltext_with_newlines q ON s.sql_address = q.address

WHERE status = 'ACTIVE' AND TYPE <> 'BACKGROUND' AND last_call_et > 60

ORDER BY sid, serial#, q.piece

Setting ORACLE DB into noarchivelog mode

Steps when the database state is down:
1. Login to sqlplus
  - sqlplus /nolog
  - connect / as sysdba
2. The db must be mounted EXCLUSIVE and not open for operation
  - startup mount;
3. Check the log mode status of the database
  - select log_mode from v$database;
4. Setting it to noarchivelog mode
  - alter database noarchivelog;
5. Setting db open for user operation
  - alter database open;
6. Reverting back to archivelog mode
  - alter database archivelog;