7 Replies Latest reply: Oct 8, 2012 6:00 AM by Purvesh K RSS

    ORA-00054: resource busy and acquire with NOWAIT specified

    885842
      Im running 2 updates in the same schema but to 2 completely different tables via sql loader.

      Due to timing issues they sometimes run at the same time. However i keep getting error :

      ORA-00054: resource busy and acquire with NOWAIT specified

      When i look this error up it says its due to a lock on a table but they are different tables.

      Any ideas?

      Many thanks
        • 1. Re: ORA-00054: resource busy and acquire with NOWAIT specified
          sb92075
          882839 wrote:
          Im running 2 updates in the same schema but to 2 completely different tables via sql loader.

          Due to timing issues they sometimes run at the same time. However i keep getting error :

          ORA-00054: resource busy and acquire with NOWAIT specified

          When i look this error up it says its due to a lock on a table but they are different tables.

          Any ideas?

          Many thanks
          error could be from other DML being involved
          • 2. Re: ORA-00054: resource busy and acquire with NOWAIT specified
            Mark Malakanov (user11181920)
            ORA-00054: resource busy and acquire with NOWAIT specified
            this usually comes from DDLs, not DMLs.

            It is hard to generate ideas without seeing both procs code, and knowing where this error happens.

            Do you use Direct load?
            Direct load want exclusive lock on a table.
            If tables are related by FK constraint load into one may lock another.
            So second load may fail.

            You have to schedule direct load properly. Or do not use direct load.

            Edited by: user11181920 on Oct 5, 2012 1:00 PM
            • 3. Re: ORA-00054: resource busy and acquire with NOWAIT specified
              Marco V.
              As stated on note:
              SQL*Loader Error: SQL*Loader-926: OCI Error while Parsing Truncate (due to TRUNCATE keyword) for Table CE.CE_STATEMENT_HEADERS_INT_ALL [ID 1391108.1]
              This error can be because of an Environment/Database issue. To resolve this issue resubmit the SQL*Loader.
              • 4. Re: ORA-00054: resource busy and acquire with NOWAIT specified
                Osama_Mustafa
                Error: ORA-00054
                Text: resource busy and acquire with NOWAIT specified or timeout expired
                =========================================================================
                Cause: Resource interested is busy or the timeout specified by DDL_LOCK_TIMEOUT has expired
                Action: Retry the command with a higher DDL_LOCK_TIMEOUT if desired
                • 5. Re: ORA-00054: resource busy and acquire with NOWAIT specified
                  Mark Malakanov (user11181920)
                  SQL*Loader Error: SQL*Loader-926: OCI Error while Parsing Truncate (due to TRUNCATE keyword) for Table
                  Do not use TRUNCATE keyword.
                  • 6. Re: ORA-00054: resource busy and acquire with NOWAIT specified
                    Mahesh Gholap
                    You can use commit/rollback to overcome this issue.
                    • 7. Re: ORA-00054: resource busy and acquire with NOWAIT specified
                      Purvesh K
                      882839 wrote:
                      Im running 2 updates in the same schema but to 2 completely different tables via sql loader.

                      Due to timing issues they sometimes run at the same time. However i keep getting error :

                      ORA-00054: resource busy and acquire with NOWAIT specified

                      When i look this error up it says its due to a lock on a table but they are different tables.

                      Any ideas?
                      Are you sure that these Two tables are themselves contributing to the ORA-00054? Did you check the Locks that were held?
                      Also, since you haven't mentioned about the environment and situation details, I am presuming that the SQL*Loader is loading the data into tables while other transactions are modifying the data.

                      And if it does, then you are vulnerable to such errors. Because if the SQL* Loader is trying to update the record of say, Table A, and the same record has been locked by
                      1. A Long Running SQL
                      2. SELECT FROM Table_A FOR UPDATE
                      3. A Procedure that is in execution for a long time without firing a TCL (rollback/Commit)
                      4. Session, that may not have Intentionally but modified the data, which was required for SQL* Loader.

                      The list is not exhaustive, but the resolution is to Investigate the cause.

                      You will havet to yourself/ask DBA to, investigate the Locks held by the User/Sessions when you encountered the error and launch measures to rectify the situation.