Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    98

    Unanswered: error compiling procedure

    This is a strange problem I am encountering...
    I am accessing a 9i db hosted from a solaris machine

    I tried to compile a procedure, and it just simply hangs on me without return anything. Usually if there is something syntactically wrong, it would show an error msg.

    is there any way I can start troubleshooting this problem?
    eg. how to check the status of this particular procedure?

    btw, when i try to drop the procedure, it hangs on me too.

    TIA

    Mark

  2. #2
    Join Date
    Nov 2003
    Location
    Newcastle upon Tyne
    Posts
    6
    Hi Mark,
    when this has happened to me in the past it has been because of a badly-formed sql query or infinite loop, which sets off processes which take up large amounts of memory.
    You may need to stop the process on the server as it may keep trying to carry out whatever task it's doing - that's why it hangs.
    Hope that helps!
    Hazel

  3. #3
    Join Date
    Nov 2002
    Posts
    98
    Originally posted by hsisson
    Hi Mark,
    when this has happened to me in the past it has been because of a badly-formed sql query or infinite loop, which sets off processes which take up large amounts of memory.
    You may need to stop the process on the server as it may keep trying to carry out whatever task it's doing - that's why it hangs.
    Hope that helps!
    Hazel
    My particular problem occurs at the compillation.. so not sure if you mean the same thing as what i am asking...

    if i execute the procedure and it appears to hang.. then it might be the problem you are describing.

    but my particular problem occurs after I type @xxxproc.sql or drop procedure xxxproc at the sqlplus prompt...

    Hope it clarifies my problem

    TIA

    Mark

  4. #4
    Join Date
    Nov 2003
    Location
    Newcastle upon Tyne
    Posts
    6
    I see..
    I'm not sure then, sorry!
    Maybe someone else can help.
    Hazel

  5. #5
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Probably someone is using your procedure, and so locking it.

    Look at this:

    SQL> create or replace procedure p is
    2 begin
    3 dbms_lock.sleep (10);
    4 end;
    5 /

    Procedure created.

    SQL> exec p;

    This procedure will run for 10 seconds - if you try, in another session, to replace or drop the procedure- the other will hang because it is waiting for the exec to complete. When the exec completes, your replace/drop will execute.

    HTH
    Alberto

  6. #6
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: error compiling procedure

    Originally posted by mchih
    This is a strange problem I am encountering...
    I am accessing a 9i db hosted from a solaris machine

    I tried to compile a procedure, and it just simply hangs on me without return anything. Usually if there is something syntactically wrong, it would show an error msg.

    is there any way I can start troubleshooting this problem?
    eg. how to check the status of this particular procedure?

    btw, when i try to drop the procedure, it hangs on me too.

    TIA

    Mark
    In almost all the cases when you are compiling a procedure and it hangs up is because some or at least one resource that the procedure is looking for is locked. I can ensure that is your case.

    Do you have administrator privileges to run a script to see all locks in the database ?
    Joel Pérez

  7. #7
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: error compiling procedure

    Script to see what locks are in the database.

    REM
    REM



    col object_name format a20
    col username format a10
    col oracle_username format a10
    col process format a15
    col owner format a10
    prompt ************************************************** **************
    prompt *** Object Lock Contention ***
    prompt ************************************************** **************
    set pages 0
    set linesize 150
    select 'Date : '||to_char(sysdate,'DD/MM/YYYY')||' Time : '||to_char(sysdate,'HH:MIS') from dual;
    select 'Database Name : '||name from sys.v_$database;
    set pages 1000
    SELECT DISTINCT
    O.OBJECT_NAME,
    SH.USERNAME,
    SH.SID,
    SW.USERNAME,
    SW.SID,
    DECODE(LH.LMODE,
    1, 'null',
    2, 'row share',
    3, 'row exclusive',
    4, 'share',
    5, 'share row exclusive',
    6, 'exclusive')
    FROM DBA_OBJECTS O,
    V$SESSION SW,
    V$LOCK LW,
    V$SESSION SH,
    V$LOCK LH
    WHERE LH.ID1 = O.OBJECT_ID
    AND LH.ID1 = LW.ID1
    AND SH.SID = LH.SID
    AND SW.SID = LW.SID
    AND SH.LOCKWAIT IS NULL
    AND SW.LOCKWAIT IS NOT NULL
    AND LH.TYPE = 'TM'
    AND LW.TYPE = 'TM'
    /

    prompt Press Enter to continue ...
    pause
    prompt ************************************************** **********
    prompt *** Object Lock Information ***
    prompt ************************************************** **********

    SELECT
    A.OBJECT_NAME,
    A.OWNER,
    C.SERIAL#,
    B.OBJECT_ID,
    B.SESSION_ID,
    B.ORACLE_USERNAME,
    B.OS_USER_NAME,
    B.PROCESS,
    DECODE(B.LOCKED_MODE,
    0,'None',
    1,'Null',
    2,'Row-S (SS)',
    3,'Row-X (SX)',
    4,'Share',
    5,'S/Row-X (SSX)',
    6,'Exclusive') LMODE
    FROM DBA_OBJECTS A, V$LOCKED_OBJECT B, V$SESSION C
    WHERE A.OBJECT_ID = B.OBJECT_ID AND C.SID = B.SESSION_ID
    ORDER BY A.OWNER, A.OBJECT_NAME, C.SERIAL#
    /
    Joel Pérez

Posting Permissions

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