Looking for Sessions Blocked

[agdo]

Personal I'm looking for a way to know what sections are getting lock on my BD I found a tutorial that gives me the following script:

Select all

 
 select h.session_id Sessao_Travadora, ub.username Usuario_Travador , 
w.session_id Sessao_Esperando, uw.username Usuario_Esperando, 
w.lock_type, 
h.mode_held, 
w.mode_requested, 
w.lock_id1, 
w.lock_id2 
from dba_locks w, dba_locks h, v$session ub, v$session uw 
where h.blocking_others = 'Blocking' 
and h.mode_held != 'None' 
and h.mode_held != 'Null' 
and h.session_id = ub.sid 
and w.mode_requested != 'None' 
and w.lock_type  = h.lock_type 
and w.lock_id1  = h.lock_id1 
and w.lock_id2 = h.lock_id2 
and w.session_id = uw.sid; 

But I do not have This DBA_Locks table on my Oracle.

Does anyone know if that's right? I thought it was Oracle's standard.

Use Oracle 11G v11.2.0.3.0.

If anyone knows otherwise to see who is "locking" I thank you.

[adrianoturbo]

Brother, following this "DBA_LOCKS" table that you are calling table that is not really a table is a synonym that in turn reference to View dba_lock.

Synonym DBA_Locks is a nickname given to View DBA_LOCK, I recommend that you change DBA_Locks by DBA_Lock in your script that will work out.
and to prove what I'm talking about about Synonym follows a script detailing this reference.

Select all

 
select owner, object_name, object_type from dba_objects 
 where object_name like 'DBA_LOCK%'; 

and to finish that neither settlement, follows a script if you need to use to find Locks.

Select all

 
SELECT     session_id, 
 lock_type, 
 mode_held, 
 mode_requested, 
blocking_others, 
lock_id1 
FROM     dba_lock  
WHERE      lock_type NOT IN ('Media Recovery', 'Redo Thread') 
 

[agdo]

Adrianoturbo Thank you very much for help.
But unfortunately I did not find this view dba_lock so much that when I run its script

Select all

SELECT     session_id, 
lock_type, 
mode_held, 
mode_requested, 
blocking_others, 
lock_id1 
FROM     dba_lock 
WHERE      lock_type NOT IN ('Media Recovery', 'Redo Thread')

I have a return
or-00942: Table or View Does Not Exists.

Strange I do not know what I'm doing wrong.
I'm like Sysdba by SQLPlus.

[adrianoturbo]

Brother, very strange, came to put the point and virgulum at the end in SQLPlus?
Try as follows:

Select all

 
SELECT     session_id, 
lock_type, 
mode_held, 
mode_requested, 
blocking_others, 
lock_id1 
FROM     dba_lock 
WHERE      lock_type NOT IN ('Media Recovery', 'Redo Thread'); 

[Trevisolli]

Agdo, Good morning.

Only one more consideration, besides those Adriano placed here:

You are Connecting on BD with which user?

Embrace,

Trevisolli

[agdo]

Good morning people
I am logging in as follows

SQLPLUS / AS SYSDBA

[agdo]

Brother, very strange, came to put the point and virgulum in the end in SQLPlus?
Try as follows:

Select all

 
SELECT     session_id, 
lock_type, 
mode_held, 
mode_requested, 
blocking_others, 
lock_id1 
FROM     dba_lock 
WHERE      lock_type NOT IN ('Media Recovery', 'Redo Thread'); 

/ quote]

Select all

 
[oracle@srvoracle scripts]$ 
[oracle@srvoracle scripts]$ sqlplus / as sysdba 
 
SQL*Plus: Release 11.2.0.3.0 Production on Qua Fev 4 09:12:31 2015 
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved. 
 
 
Conectado a: 
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production 
 
SQL> select session_id from dba_lock where lock_type NOT IN ('Media Recovery' , 'Redo Thread'); 
select session_id from dba_lock where lock_type NOT IN ('Media Recovery' , 'Redo Thread') 
                       * 
ERRO na linha 1: 
ORA-00942: a tabela ou view n▒o existe 
 
 
SQL> 
 
 

[adrianoturbo]

Brother, detected error, when typing the command, you even duplicated the Select .OLA only error below:

Select all

SQL*Plus: Release 11.2.0.3.0 Production on Qua Fev 4 09:12:31 2015 
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved. 
 
 
Conectado a: 
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production 
 
SQL> select session_id from dba_lock where lock_type NOT IN ('Media Recovery' , 'Redo Thread'); 
select session_id from dba_lock where lock_type NOT IN ('Media Recovery' , 'Redo Thread') 
                       * 
ERRO na linha 1: 
ORA-00942: a tabela ou view não existe'

[adrianoturbo]

Tip: Connect with SYS user that way:

Select all

 
[oracle@adriano root]$ sqlplus '/as sysdba' 
 
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 5 18:12:48 2015 
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved. 
 
 
Connected to: 
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production