8 Replies Latest reply: Dec 10, 2012 11:08 AM by Srini Chavali-Oracle RSS

    Unable to run a procedure on Oracle 11.2.0.3

    Saurabh Gupta-OC
      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
          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
            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
              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
                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
                  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
                    >
                    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
                      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
                        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