Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2013
    Posts
    7

    Unanswered: Need help: cannot compile my procedure,getting ORA-04021 error

    Hi,

    When tried to recompile my procedure,
    it gave ORA-04021:timeout occured while waiting to lock object error,
    When before giving the error, PLSQL developer got stuck,
    had to force shutdown all windows,
    as it was showing as not responding,

    How can I compile the procedure?

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    Have a look at this thread:
    https://forums.oracle.com/thread/2321256
    It may shed some light

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Does package do DML?
    If yes, has COMMIT or ROLLBACK been issued?
    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
    Sep 2013
    Posts
    7
    Quote Originally Posted by pablolee View Post
    Have a look at this thread:
    https://forums.oracle.com/thread/2321256
    It may shed some light
    In that link,they have mentioned:

    You can join v$session to dba_ddl_locks or you can use v$acess

    How do I do that?
    How do I find out if any session ID is accessing my package?

  5. #5
    Join Date
    Sep 2013
    Posts
    7
    Quote Originally Posted by anacedent View Post
    Does package do DML?
    If yes, has COMMIT or ROLLBACK been issued?
    Yes,there are some insert and update commands in that procedure,
    Also this procedure invokes some procedures and packages
    Dont know about whether the COMMIT and ROLLBACK has been issued or not,
    How to find that?
    Sorry, if my questions are silly,I am really new to databases.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Dont know about whether the COMMIT and ROLLBACK has been issued or not,
    does package contain either COMMIT or ROLLBACK statement?

    Package can not be compiled when it has issued uncommitted DML.
    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.

  7. #7
    Join Date
    Sep 2013
    Posts
    7
    Quote Originally Posted by anacedent View Post
    >Dont know about whether the COMMIT and ROLLBACK has been issued or not,
    does package contain either COMMIT or ROLLBACK statement?

    Package can not be compiled when it has issued uncommitted DML.
    Package doesnt contain any COMMIT or ROLLBACK stmt,
    and the commit and rollback icons on the toolbar are greyed out,
    so i guess it has issued COMMIT or ROLLBACK,
    Uncommitted DML isnt there..

  8. #8
    Join Date
    Sep 2013
    Posts
    7

    Resolved the issue with the help of a friend,
    used the command

    ALTER SYSTEM KILL SESSION ('SID','SERIAL#')

    to kill the session which was accessing the procedure

    The procedure got compiled successfully after that.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If the session that was killed had issued either COMMIT or ROLLBACK,
    then package could have been compiled again.

    You can not compile a package which has issued uncommitted DML in order to maintain data consistency.
    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.

Posting Permissions

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