ORA-00054: resource busy and acquire with NOWAIT specified

First of all, i must say this message maybe is not really scary, because you might think with some patience the problem can be overridden. Well, there is a possible scenario giving some headaches to a DBA:

alter table  tn disable constraint tnc;

Everything is fine 'till now, you have disabled a constraint in order to update some data.

After all these updates, and after you verify the data is compliant with the bussiness rules enforced by the constraint tnc:

alter table  tn enable constraint tnc;

 ORA-00054: resource busy and acquire with NOWAIT specified

Now you are in serious trouble. Working hours mean lots of data inserted in your tables, not anymore protected by that constraint, and the users can broke the bussiness rule from now.

Of course, you may say the same problem can occur during that few seconds while you ran the two DDL commands and the DML commands which modified th table tn. Agree, was not a good practice way of doing things, but this kind of manipulating data can be seen very often in practice, when a DBA or a developer doesn't care or can't wait for a maintenance window, in order to manipulate data, ignoring the possibility of any other session, locking a table.

Unfortunately, there isn't any complete solution for this problem. You can run the following code (for Oracle <10g), which tries for n minutes to achieve the lock on the table and run the DDL command:

DECLARE

  is_ok  BOOLEAN;

  t_l    EXCEPTION;

  PRAGMA EXCEPTION_INIT(t_l, -00054);

  start_time     DATE; -- the moment of the first cycle

  no_min number; --how many minutes the program will run, trying to

                 -- achieve the lock

BEGIN

  is_ok := FALSE;

  SELECT SYSDATE INTO start_time FROM DUAL;

  WHILE (NOT is_ok) AND (SYSDATE < start_time + no_min/24/60)

  LOOP

    BEGIN

      EXECUTE IMMEDIATE ('alter table  tn enable constraint tnc');

        is_ok := TRUE;

      DBMS_OUTPUT.PUT_LINE('Succes!');

    EXCEPTION

    WHEN t_l THEN

      NULL;

      DBMS_LOCK.SLEEP(0.1);

    END;

  END LOOP;

  IF is_ok = FALSE THEN

    DBMS_OUTPUT.PUT_LINE('the constraint is still disabled!');

  END IF;

END;

/

For Oracle versions >10g, there is a parameter, ddl_lock_timeout , doing the same job like the code from above. Any DDL command issued against a table will try for a number of seconds equal to ddl_lock_timeout.

And there is another solution, involving   the QUIESCE concept, but i don't like it and i don't recommend it to anyone.

Good luck!