This discussion is archived
8 Replies Latest reply: Dec 10, 2012 9:08 AM by Srini Chavali-Oracle RSS

Unable to run a procedure on Oracle 11.2.0.3

Saurabh Gupta-OC Newbie
Currently Being Moderated
Hi,

We have created a new database 11.2.0.3 on linux environment. We tried to execute a procedure "TEST_PRC1" using sys but getting below error message:

ORA-04020: deadlock detected while trying to lock object SYS.TEST_PRC1

I am able to run the same procedure on production DB.

I checked the below notes but it is not related to the suggested options.

Ora-04020 Deadlocks: Most Common Causes (Doc ID 166924.1)

Finally I did the below steps:

1. Bounced the DB
2. Connected to Sys
3. Checked, No User is connected
4. Checked, no object is INVALID under Sys User
5. Executed the procedure TEST_PRC1 using SYS user
6. Got same error message again.

Below is the procedure detail:


create or replace
PROCEDURE test_prc1(
p_l1_tbl_name VARCHAR2,
p_l2_tbl_name VARCHAR2,
p_job_code VARCHAR2
)
IS
sqltxt VARCHAR2 (250);
v_cnt NUMBER;
v_row_count NUMBER;
v_row_count1 NUMBER;
v_error_rec VARCHAR2 (30);
v_tbl_l1 VARCHAR2 (40);
v_tbl_l2 VARCHAR2 (40);
v_job_code VARCHAR2 (10);

BEGIN

v_error_rec := CHR (39) || 'DUPLICATE ROWS IN SOURCE' || CHR (39);
v_tbl_l1 := CHR (39) || p_l1_tbl_name || CHR (39);
v_job_code := CHR (39) || p_job_code || CHR (39);
v_tbl_l2 := CHR (39) || p_l2_tbl_name || CHR (39);


sqltxt := 'create table test_prc1 as SELECT * FROM test_prc';

EXECUTE IMMEDIATE sqltxt;


Please suggest.

Regards
Saurabh
  • 1. Re: Unable to run a procedure on Oracle 11.2.0.3
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    ORA-04020:deadlock detected while trying to lock object stringstringstringstringstring
    Cause: While trying to lock a library object, a deadlock is detected.
    Action:Retry the operation later.
  • 2. Re: Unable to run a procedure on Oracle 11.2.0.3
    user639304 Explorer
    Currently Being Moderated
    Hi,

    Post the output of the following query:
    select *
    from dba_ddl_locks
    where upper(type) like '%PROCEDURE%';
  • 3. Re: Unable to run a procedure on Oracle 11.2.0.3
    Saurabh Gupta-OC Newbie
    Currently Being Moderated
    its long list, pls find below:

    1082     SYS     STANDARD     Table/Procedure/Type     Null     None
    1080     SYS     STANDARD     Table/Procedure/Type     Null     None
    1080     SYS     DBMS_PRVT_TRACE     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_STATS_INTERNAL     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_OUTPUT     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_UTILITY     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_SQLTUNE_UTIL0     Table/Procedure/Type     Null     None
    788     SYS     AQ$_SYS$SERVICE_METRICS_TAB_N     Table/Procedure/Type     Null     None
    1082     SYS     AQ$_SYS$SERVICE_METRICS_TAB_N     Table/Procedure/Type     Null     None
    788     SYS     DBMS_IJOB     Table/Procedure/Type     Null     None
    1082     SYS     IS_VPD_ENABLED     Table/Procedure/Type     Null     None
    100     SYS     IS_VPD_ENABLED     Table/Procedure/Type     Null     None
    100     SYS     DBMS_RMIN     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_ADVISOR     Table/Procedure/Type     Null     None
    1082     SYS     SYSTEM_GRANT     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_SYSTEM     Table/Procedure/Type     Null     None
    788     SYS     DBMS_SYSTEM     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_SESSION     Table/Procedure/Type     Null     None
    1082     SYS     WRI$_REPT_FORMAT_ID_SEQ     Table/Procedure/Type     Null     None
    1082     SYS     SQL_TK_CHK_ID     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_REGISTRY_SYS     Table/Procedure/Type     Null     None
    1080     SYS     DBMS_HA_ALERTS_PRVT     Table/Procedure/Type     Null     None
    788     SYS     DBMS_AQADM_SYSCALLS     Table/Procedure/Type     Null     None
    100     SYS     DBMS_AQADM_SYSCALLS     Table/Procedure/Type     Null     None
    788     SYS     DBMS_PRVTAQIS     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_SQLDIAG_INTERNAL     Table/Procedure/Type     Null     None
    1082     SYS     IDGEN1$     Table/Procedure/Type     Null     None
    100     SYS     IDGEN1$     Table/Procedure/Type     Null     None
    1082     SYS     PRVT_ADVISOR     Table/Procedure/Type     Null     None
    1082     SYS     SQLSET_ROW     Table/Procedure/Type     Null     None
    1082     SYS     ORA_TQ_BASE$     Table/Procedure/Type     Null     None
    1082     SYS     XMLSEQ_IMP_T     Table/Procedure/Type     Null     None
    1082     SYS     DICTIONARY_OBJ_NAME     Table/Procedure/Type     Null     None
    100     SYS     DICTIONARY_OBJ_NAME     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_STANDARD     Table/Procedure/Type     Null     None
    100     SYS     DBMS_STANDARD     Table/Procedure/Type     Null     None
    100     SYS     DBMS_RESOURCE_MANAGER     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_SQLDIAG     Table/Procedure/Type     Null     None
    1082     SYS     WRI$_REPT_REPT_ID_SEQ     Table/Procedure/Type     Null     None
    1082     SYS     AW_DROP_PROC     Table/Procedure/Type     Null     None
    1082     SYS     WRI$_REPT_COMP_ID_SEQ     Table/Procedure/Type     Null     None
    1082     SYS     PLITBLM     Table/Procedure/Type     Null     None
    1080     SYS     PLITBLM     Table/Procedure/Type     Null     None
    1082     SYS     XMLTYPE     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_APPLICATION_INFO     Table/Procedure/Type     Null     None
    1082     SYS     DICTIONARY_OBJ_OWNER     Table/Procedure/Type     Null     None
    100     SYS     DICTIONARY_OBJ_OWNER     Table/Procedure/Type     Null     None
    1082     SYS     AQ$_TRANS_SEQUENCE     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_SQLTUNE_INTERNAL     Table/Procedure/Type     Null     None
    1082     SYS     WRI$_ADV_SEQ_TASK     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_LOB     Table/Procedure/Type     Null     None
    1082     SYS     AW_TRUNC_PROC     Table/Procedure/Type     Null     None
    788     SYS     DBMS_AQADM_SYS     Table/Procedure/Type     Null     None
    1082     SYS     DICTIONARY_OBJ_TYPE     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_REGISTRY     Table/Procedure/Type     Null     None
    788     SYS     DBMS_LOGSTDBY     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_STATS     Table/Procedure/Type     Null     None
    1082     SYS     WRI$_REPT_FILE_ID_SEQ     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_SMB_INTERNAL     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_SQLTUNE     Table/Procedure/Type     Null     None
    788     SYS     DBMS_ASSERT     Table/Procedure/Type     Null     None
    1082     SYS     DBMS_ASSERT     Table/Procedure/Type     Null     None
    1082     SYS     OBJECT_GRANT     Table/Procedure/Type     Null     None

    Regards
    Saurabh
  • 4. Re: Unable to run a procedure on Oracle 11.2.0.3
    Dom Brooks Guru
    Currently Being Moderated
    Why are you creating a table as SYS?
    Why are you using a proc and dynamic sql to create a table?

    The detection of a deadlock will cause an entry to be put into the alert log detailing the associated trace file.
    Look in that trace file for information related to both sides of the deadlock.
  • 5. Re: Unable to run a procedure on Oracle 11.2.0.3
    Saurabh Gupta-OC Newbie
    Currently Being Moderated
    Hi,

    Pls find my comments against your questions:


    Why are you creating a table as SYS? - because I faced this issue with a schema, then thought to give it a try with sys first. What is the issue if I create a test table using sys?

    Why are you using a proc and dynamic sql to create a table? - This same proc is running fine on other databases, then why not on this? I am able to create table but not able to execute the proc. this issue is limited to this database only.

    The detection of a deadlock will cause an entry to be put into the alert log detailing the associated trace file. --found "Global Enqueue Services Deadlock detected. More info in file" error msg in alert log.
    Look in that trace file for information related to both sides of the deadlock.

    Now checking trace files....

    Regards
    Saurabh
  • 6. Re: Unable to run a procedure on Oracle 11.2.0.3
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    >
    Why are you creating a table as SYS? - because I faced this issue with a schema, then thought to give it a try with sys first. What is the issue if I create a test table using sys?
    >

    http://docs.oracle.com/cd/E11882_01/server.112/e25494/dba005.htm#ADMIN11043

    HTH
    Srini
  • 7. Re: Unable to run a procedure on Oracle 11.2.0.3
    Saurabh Gupta-OC Newbie
    Currently Being Moderated
    Thank You Srini.

    But we are facing this issue with all schemas whereas the same procedure is running smoothly on other DBs.

    Regards
    Saurabh
  • 8. Re: Unable to run a procedure on Oracle 11.2.0.3
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    As noted, you should not be using the SYS schema.

    The error indicates something is incorrect or different in this database compared to the others. These MOS Docs may help

    How to Analyze ORA-04021 and ORA-4020 Errors [ID 1486712.1]
    Ora-04020 Deadlocks: Most Common Causes [ID 166924.1]

    HTH
    Srini

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points