Oracle – ALTER TABLE ENABLE TABLE LOCK

alteroracle

I did ALTER TABLE DISABLE TABLE LOCK on one of our tables and now I can't enable table lock back. When I'm executing on this table:

ALTER TABLE x ENABLE TABLE LOCK;

I'm getting:

SQL Error: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

I checked that there was a blocking session from other user but he killed his session and now I still can't enable table lock. There is new locking session, looking like some system session (OSUSER=SYSTEM, PROGRAM=ORACLE.EXE (DIA0), TYPE=BACKGROUND).

Could you please help me with this enable table lock?

Edit: After database restart we were able to enable table lock.

Best Answer

Run this query to identify the sessions that are locking your object:

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;

With results from this query you can run ALTER SYSTEM KILL SESSION 'sid,serial#'; that should clear the locks and you can retry acquiring the lock.

If you are having a process that re-spawns and acquires the lock before your session you can, you can write a while loop that checks if the lock can be acquired by catching the ORA-00054 error and retrying until the lock can be placed using LOCK TABLE x IN EXCLUSIVE MODE NOWAIT;

Related Solutions

Sql – Get list of all tables in Oracle

SELECT owner, table_name
  FROM dba_tables

This is assuming that you have access to the DBA_TABLES data dictionary view. If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table, or, that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role (either of which would allow you to query any data dictionary table). Of course, you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of Oracle tables that you probably don't care about.

Alternatively, if you do not have access to DBA_TABLES, you can see all the tables that your account has access to through the ALL_TABLES view:

SELECT owner, table_name
  FROM all_tables

Although, that may be a subset of the tables available in the database (ALL_TABLES shows you the information for all the tables that your user has been granted access to).

If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES:

SELECT table_name
  FROM user_tables

Since USER_TABLES only has information about the tables that you own, it does not have an OWNER column – the owner, by definition, is you.

Oracle also has a number of legacy data dictionary views-- TAB, DICT, TABS, and CAT for example-- that could be used. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle has not changed these views in a long time so they often have problems with newer types of objects. For example, the TAB and CAT views both show information about tables that are in the user's recycle bin while the [DBA|ALL|USER]_TABLES views all filter those out. CAT also shows information about materialized view logs with a TABLE_TYPE of "TABLE" which is unlikely to be what you really want. DICT combines tables and synonyms and doesn't tell you who owns the object.

Sql – ORA-00054: resource busy and acquire with NOWAIT specified

Step 1:

select object_name, s.sid, s.serial#, p.spid 
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;

Step 2:

alter system kill session 'sid,serial#'; --`sid` and `serial#` get from step 1

More info: http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php

Best Answer

Related Solutions

Sql – Get list of all tables in Oracle

Sql – ORA-00054: resource busy and acquire with NOWAIT specified

Related Topic