ORA-00054: resource busy and acquire with NOWAIT

There’s More than One Solution I would Love to Share :

1-Before 11g, you can mark the tablespace read-only for the duration of the alter table 


Alter Tablespace Test read only

2-in 11g you can mark table read-only :

Alter table test Read only

And You can Check Read Only Tables By :


Select    table_name, read_only from    dba_tables
Where owner = ‘myowner’ and table_name = ‘mytab’;

Just To Make Sure After You Alter table to Read Only prevent Update by Create Trigger


create or replace trigger
   tabl_read_only
before
   insert or update or delete
on mytab
begin
raise_application_error (-999999, ‘Table Is Read Only Now’);
end; /

3- You Can Check if there’s Lock on your Database :


SQL> set linesize 130
SQL> set pages 100
SQL> col username       format a20
SQL> col sess_id        format a10
SQL> col object format a25
SQL> col mode_held      format a10
SQL> select     oracle_username || ‘ (‘ || s.osuser || ‘)’ username
,  s.sid || ‘,’ || s.serial# sess_id
,  owner || ‘.’ || object_name object
,  object_type
,  decode( l.block
,       0, ‘Not Blocking’
,       1, ‘Blocking’
,       2, ‘Global’) status
,  decode(v.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(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username
,  session_id
/

  Use

SQL> alter system kill session ‘SID,Serial# ‘;

Enjoy 
OSama Mustafa

 

Locking In Oracle

Locking in Oracle is one of the most common problem we will face as database administrator.

 is the locking Effect on Database performance ?

Yes . impede a transaction from finishing , since the Lock query Take long time running .

When the Locking Happened ?

I will Give you example :

Let assume that we have two Users Each Of them Update on the same table like the following :

User 1 :

SQL> update test set name=’lock’ where id=1;

1 row updated.

User didn’t commit here .

User 2 :
SQL> update test set name=’lock2′ where id=1;


User 2 will be waiting

Inforamtion about locks :
1-Locks scripts One.
2-Locks Scripts Two

Another way to Lock :

performing a DDL (alter,create….) and get an ORA-00054 error.

ORA-00054: resource busy and acquire with NOWAIT specified

 to solve this issue

SQL> select object_id from dba_objectswhere owner=’Username’  and object_name=’Table’;

 OBJECT_ID
———-
 

SELECT c.owner,
 c.object_name,
 c.object_type,
 b.sid,
 b.serial#,
 b.status,
 b.osuser,
 b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.sid = a.session_id AND a.object_id = c.object_id
and a.object_id=;

Refer also to :
1-Locks

Osama Mustafa