12 Replies Latest reply on Jun 30, 2020 1:36 AM by John Thorton

    how to overcome ora-00054 and ora-04022?

    4080076

      Is there any method in Oracle 12c to try (as a loop)for an increased time period (retry for 10 mins) certain operations  to overcome both

      - ora-00054 (resource busy and acquire with NOWAIT)

      - ora-04022 (nowait requested, but had to wait to lock dictionary object)

       

      Thank you

        • 1. Re: how to overcome ora-00054 and ora-04022?
          John Thorton

          4080076 wrote:

           

          Is there any method in Oracle 12c to try (as a loop)for an increased time period (retry for 10 mins) certain operations to overcome both

          - ora-00054 (resource busy and acquire with NOWAIT)

          - ora-04022 (nowait requested, but had to wait to lock dictionary object)

           

          Thank you

          My first guess is that YOU have the object LOCKED in a different session.

          Likely solution is to ENSURE that you LOGOUT or TERMINATE every existing session that you started today

          As POGO once declared, "We have met the enemy, & they is US!"

           

          It is possible/likely that you have a procedure doing DML and there is no corresponding COMMIT or ROLLBACK.

          This fact then means Oracle no longer allows you to change procedure code while uncommitted DML exists for this package/procedure.

          • 2. Re: how to overcome ora-00054 and ora-04022?
            Solomon Yakobson

            BEGIN

                V_SQL := '...'; -- SQL that can raise resource busy

                LOOP

                  EXIT WHEN V_RESOURCE_BUSY_ATTEMPT_COUNT > 10000;

                  BEGIN

                      V_THIS_RESOURCE_BUSY_ATTEMPTS := V_THIS_RESOURCE_BUSY_ATTEMPTS + 1;

                      DBMS_OUTPUT.PUT_LINE(

                                           'BEFORE(ATTEMPT ' || V_THIS_RESOURCE_BUSY_ATTEMPTS || '): ' ||

                                           TO_CHAR(SYSTIMESTAMP,'MM/DD/YYYY HH24:MI:SS.FF')

                                          );

                      EXECUTE IMMEDIATE V_SQL; -- SQL that can raise resource busy

                      DBMS_OUTPUT.PUT_LINE(

                                           'AFTER(ATTEMPT ' || V_THIS_RESOURCE_BUSY_ATTEMPTS || '): ' ||

                                           TO_CHAR(SYSTIMESTAMP,'MM/DD/YYYY HH24:MI:SS.FF')

                                          );

                      DBMS_OUTPUT.PUT_LINE(

                                           V_SQL || ' COMPLETED IN ' ||

                                           V_THIS_RESOURCE_BUSY_ATTEMPTS || ' ATTEMPT(S)'

                                          );

                      EXIT;

                    EXCEPTION

                      WHEN RESOURCE_BUSY

                        THEN

                          DBMS_LOCK.SLEEP(1); -- wait for 1 second

                          V_RESOURCE_BUSY_ATTEMPT_COUNT := V_RESOURCE_BUSY_ATTEMPT_COUNT + 1;

                  END;

                END LOOP;

             

             

            SY.

            • 3. Re: how to overcome ora-00054 and ora-04022?
              4080076

              Thanks for the ideas!

               

              I am getting some errors:

               

              Error report -

              ORA-06550: line 15, column 11:

              PLS-00201: identifier 'V_THIS_RESOURCE_BUSY_ATTEMPTS' must be declared

              ORA-06550: line 15, column 11:

              PL/SQL: Statement ignored

              ORA-06550: line 18, column 12:

              PLS-00221: 'V_SQL' is not a procedure or is undefined

              ORA-06550: line 18, column 12:

              PL/SQL: Statement ignored

              ORA-06550: line 26, column 16:

              PLS-00201: identifier 'RESOURCE_BUSY' must be declared

              ORA-06550: line 13, column 7:

              PL/SQL: Statement ignored

              06550. 00000 -  "line %s, column %s:\n%s"

              *Cause:    Usually a PL/SQL compilation error.

               

               

              How about something simple as :

               

              ALTER SESSION SET ddl_lock_timeout=300;

               

              Should I be concerned about using such method?

              • 4. Re: how to overcome ora-00054 and ora-04022?
                John Thorton

                With free advice, sometimes you get what you paid for it.

                • 5. Re: how to overcome ora-00054 and ora-04022?
                  BEDE

                  So, it is obvious you should declare those variables in the declare section of your procedure or PL/SQ block where you refer then.

                  So, in the declare section of the procedure you should have:

                   

                  V_THIS_RESOURCE_BUSY_ATTEMPTS number;

                  RESOURCE_BUSY exception; --- user-defined exception

                  pragme exception_init(RESOURCE_BUSY, -54);

                   

                  And I think this should suffice

                  • 6. Re: how to overcome ora-00054 and ora-04022?
                    4080076

                    pragme exception_init(RESOURCE_BUSY, -54);

                     

                    Is it going to cover ora-04022 (nowait requested, but had to wait to lock dictionary object) too?

                    • 7. Re: how to overcome ora-00054 and ora-04022?
                      BEDE

                      The famous -54 I have had to deal with on several occasions, but I haven't had problems with this -4022 until now. If need be you may declare and initiate an exception for that one too. Just see how it works with these.

                      • 8. Re: how to overcome ora-00054 and ora-04022?
                        4080076

                        it did work for -54.

                         

                        does it support more than one exception?

                        pragme exception_init(RESOURCE_BUSY, -54);

                        • 9. Re: how to overcome ora-00054 and ora-04022?
                          BEDE

                          RTM on pragma exception_init! You mai assign different Oracle error codes to different user-defined exceptions, which means you may have several such pragmas, only make sure that one user-defined exception is assigned to one Oracle error code and one Oracle error code is used only with one pragma.

                          I think your main problem is that you haven't read the manuals.

                          • 10. Re: how to overcome ora-00054 and ora-04022?
                            BluShadow

                            4080076 wrote:

                             

                            it did work for -54.

                             

                            does it support more than one exception?

                            pragme exception_init(RESOURCE_BUSY, -54);

                             

                             

                            Yes, pragma exception_init can be used for multiple exception declarations.

                            Community document...

                            PL/SQL 101 : Exception Handling

                             

                            Just declare multiple exception variables and use the pragma for each one to associate it with the error number.

                            • 11. Re: how to overcome ora-00054 and ora-04022?
                              Solomon Yakobson

                              Oops, missed DECLARE part:

                               

                              DECLARE

                                    V_RESOURCE_BUSY_ATTEMPT_COUNT  NUMBER := 1;

                                    V_SQL                          CLOB;

                                    RESOURCE_BUSY EXCEPTION;

                                    PRAGMA EXCEPTION_INIT(RESOURCE_BUSY,-54);

                              BEGIN

                                  V_SQL := '...'; -- SQL that can raise resource busy

                                  LOOP

                                    EXIT WHEN V_RESOURCE_BUSY_ATTEMPT_COUNT > 10000;

                                    BEGIN

                                        DBMS_OUTPUT.PUT_LINE(

                                                             'BEFORE(ATTEMPT ' || V_RESOURCE_BUSY_ATTEMPT_COUNT || '): ' ||

                                                             TO_CHAR(SYSTIMESTAMP,'MM/DD/YYYY HH24:MI:SS.FF')

                                                            );

                                        EXECUTE IMMEDIATE V_SQL; -- SQL that can raise resource busy

                                        DBMS_OUTPUT.PUT_LINE(

                                                             'AFTER(ATTEMPT ' || V_RESOURCE_BUSY_ATTEMPT_COUNT || '): ' ||

                                                             TO_CHAR(SYSTIMESTAMP,'MM/DD/YYYY HH24:MI:SS.FF')

                                                            );

                                        DBMS_OUTPUT.PUT_LINE(

                                                             V_SQL || ' COMPLETED IN ' ||

                                                             V_RESOURCE_BUSY_ATTEMPT_COUNT || ' ATTEMPT(S)'

                                                            );

                                        EXIT;

                                      EXCEPTION

                                        WHEN RESOURCE_BUSY

                                          THEN

                                            DBMS_LOCK.SLEEP(1); -- wait for 1 second

                                            V_RESOURCE_BUSY_ATTEMPT_COUNT := V_RESOURCE_BUSY_ATTEMPT_COUNT + 1;

                                    END;

                                  END LOOP;

                               

                              SY.

                              • 12. Re: how to overcome ora-00054 and ora-04022?
                                John Thorton

                                4080076 wrote:

                                 

                                Is there any method in Oracle 12c to try (as a loop)for an increased time period (retry for 10 mins) certain operations to overcome both

                                - ora-00054 (resource busy and acquire with NOWAIT)

                                - ora-04022 (nowait requested, but had to wait to lock dictionary object)

                                 

                                Thank you

                                what exactly was the SQL you issued that produced errors above?