ORA-00054 RESOURCE BUSY OR TIMEOUT EXPIRED.

•  I got this Alter script from my developer which i have to execute from sys user of my Produciton database.
• But unfortunately one user not log out from his session and oracle is showing ORA-00054 error.

                                     #################   Alter-SCRIPT     ###################



ALTER TABLE HMIS.OVRVNEVENTS MODIFY(EVN_VISOR_REMARK VARCHAR2(4000 BYTE));


ALTER TABLE HMIS.OVRVNEVENTS MODIFY(EVN_DETAILS VARCHAR2(4000 BYTE));


ALTER TABLE HMIS.OVRVNEVENTS MODIFY(EVN_REMARKS VARCHAR2(4000 BYTE));


ALTER TABLE HMIS.OVRVNEVENTS MODIFY(APP_COMMENT VARCHAR2(4000 BYTE));

ALTER TABLE HMIS.OVRVNEVENTS MODIFY(EVN_TYPE VARCHAR2(1));


ALTER TABLE HMIS.OVRVNEVENTS MODIFY(EVN_PREVENT VARCHAR2(4000 CHAR));


ALTER TABLE HMIS.OVRVNEVENTS MODIFY(EVN_CONT_OTH VARCHAR2(4000 CHAR));

================================================


SQL> conn / as sysdba
Connected.

SQL> ALTER TABLE HMIS.OVRVNEVENTS
MODIFY(EVN_PREVENT VARCHAR2(4000 CHAR));

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL> alter table HMIS.OVRVNEVENTS read only;
alter table HMIS.OVRVNEVENTS read only
                *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


>> Here we cannot able to update this table columns because the user has not logout from his session.

>> First find out the object_id of that particular user.


SQL> select object_id from dba_objects where object_name='OVRVNEVENTS';

OBJECT_ID
--------------
    80407
   117027


>> Now find out the username by using OJECT_ID


SQL> select object_id,oracle_username from gv$locked_object where object_id='117027';

 OBJECT_ID           ORACLE_USERNAME
---------- ------------------------------------------------
    117027                     CLROEL



>> Find out the SID to kill the session.



SQL> select sid,username,serial# from gv$session where username='CLROEL';

       SID        USERNAME            SERIAL#
---------- ---------------------------------------------------
       204        CLROEL                     51183


SQL> alter system kill session '204,51183,@2' immediate;

System altered.


USER is "SYS"


SQL> ALTER TABLE HMIS.OVRVNEVENTS MODIFY(EVN_VISOR_REMARK VARCHAR2(4000 BYTE));

Table altered.


SQL> ALTER TABLE HMIS.OVRVNEVENTS MODIFY(EVN_DETAILS VARCHAR2(4000 BYTE));

Table altered.


SQL> ALTER TABLE HMIS.OVRVNEVENTS MODIFY(EVN_REMARKS VARCHAR2(4000 BYTE));

Table altered.


SQL> ALTER TABLE HMIS.OVRVNEVENTS  MODIFY(APP_COMMENT VARCHAR2(4000 BYTE));

Table altered.


SQL> ALTER TABLE HMIS.OVRVNEVENTS MODIFY(EVN_TYPE VARCHAR2(1));

Table altered.


SQL> ALTER TABLE HMIS.OVRVNEVENTS MODIFY(EVN_PREVENT VARCHAR2(4000 CHAR));

Table altered.


SQL> ALTER TABLE HMIS.OVRVNEVENTS MODIFY(EVN_CONT_OTH VARCHAR2(4000 CHAR));

Table altered.


HAPPY LEARINING ...