Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003

    Post Unanswered: Unable to run alter table command

    When running an alter table command on a 9i database SQL PLUS hangs, indicating that the table I am trying to alter is some how locked. However when I look a V$session there are no active users.

    The users access the system via a 9iAS Oracle forms front end.

    My question is this: what common factors prevent an alter table command to run, as I am trying to establish a few trends without much luck.

    Any answers or pointers to relevant web based resources would be appreciated.

  2. #2
    Join Date
    Jan 2004
    Monitor v$session_wait while your session is hung.
    Check this view several times, every 5 seconds.
    From the seq#, wait events and wait times you should be able to tell if the session is stuck waiting for a resource (and what that resource is), or just running very slowly.

  3. #3
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1

    Lead, follow, or at least get out of the way!

    >what common factors prevent an alter table command to run
    Typically an uncommitted transaction will prevent "ALTER TABLE" from completing.
    It is bad, bad, bad procedure to be changing production objects while users are accessing the system.
    SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
    id1, id2, lmode, request, type
    WHERE (id1, id2, type) IN
    (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
    ORDER BY id1, request
    ================================================== =
    The SQL above will show the SID for the session holding the lock, &
    the SID of those sessions waiting on the lock.
    The HOLDER needs to either COMMIT or ROLLBACK the pending transaction.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Apr 2002
    California, USA
    Check which session waits for what - V$SESSION_WAIT:

    column sid format 990
    column seq# format 99990
    column wait_time heading 'WTime' format 99990
    column event format a30
    column p1 format 9999999990
    column p2 format 9999999990
    column p3 format 9990
    select sid,event,seq#,p1,p2,p3,wait_time 
       from V$session_wait
    order by sid;


    clio_usa - OCP 8/8i/9i DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts