Resource busy; not able to modify the table

step 1 // identify object id
 select OBJECT_ID from dba_objects where OBJECT_NAME='EMP

step 2 // identify who is locking
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   gv$locked_object a ,
   gv$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id
   and c.object_id=1101;
 
 
solution
steps 3  //  modify ddl table lock time for 10 seconds
alter session set ddl_lock_timeout = 10;

step 4 // modify table
alter table // full modify table command

Blocking Issue

// *******************************************************************************--  blocking issue : enq: TX - row lock contention
// *******************************************************************************
step 1
  // get blocking session and blocked sessions

  set lines 180
  col USERNAME format a10
  col event    format a15
  col WAIT_CLASS format a15
  Select  blocking_session, sid, serial#, username, event, wait_class, seconds_in_wait siw
    from v$session  where blocking_session is not NULL  order by blocking_session;    

step 2
 //  detail locking information :  sid, serial and object name    
 
   set linesize 150;
    set head on;
    col sid_serial form a13
    col ora_user for a15;
    col object_name for a35;
    col object_type for a10;
    col lock_mode for a15;
    col last_ddl for a8;
    col status for a10;
   
    break on sid_serial;
   
   SELECT l.session_id||','||v.serial# sid_serial,
          l.ORACLE_USERNAME ora_user,
          o.object_name,
          o.object_type,
          DECODE(l.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(l.locked_mode)
              ) lock_mode,
             o.status,
             to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
   FROM dba_objects o, gv$locked_object l, v$session v
   WHERE o.object_id = l.object_id
     and l.SESSION_ID=v.sid
   order by 2,3;



step 3
//  which sid and stmt is waiting for lock

with blocking_tab as ( 
  Select  distinct blocking_session  sid   
        from v$session  where blocking_session is not NULL
),
blocking_sess as (
  select s.sid, s.serial#, sql_id, prev_sql_id from v$session s, blocking_tab b where s.sid=b.sid
  ),
blocked_sess as (    
  select s.sid, s.serial#, sql_id, prev_sql_id from v$session s, blocking_tab b where s.blocking_session=b.sid    ),
wait_sess  as  (
  select sid, serial#, sql_id, prev_sql_id from blocking_sess  union  select sid, serial#, sql_id,   prev_SQL_id from blocked_sess  )
select s.sid, s.serial#, sql_text from v$sql q, wait_sess s where 
q.sql_id = s.sql_id or
    q.sql_id = s.prev_sql_id
/ 
 
step 4
// kill blocking session

with temptbl1 as (
  Select  distinct blocking_session  sid   
        from v$session  where blocking_session is not NULL
)
select 'alter system kill session '||chr(39)||sid||','||serial#||chr(39)||';' from v$session natural  join  temptbl1 ;


// *******************************************************************************