OraFAQ Forum: SQL & PL/SQL » Reason for ORA-00054: resource busy and acquire with NOWAIT specified

Home » SQL & PL/SQL » SQL & PL/SQL » Reason for ORA-00054: resource busy and acquire with NOWAIT specified

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Reason for ORA-00054: resource busy and acquire with NOWAIT specified [message #146033]

Tue, 08 November 2005 04:55

sreek_s
Messages: 45
Registered: May 2005
Location: Andaman Nikobar

Member

Hi,
I have a scenario where i need to execute some SQL queries in parallell. Infact the SQL query calls a PL/SQL procedure. Inside that procedure i have a "Truncate Table" command.

Problem is when i am trying to execute such SQL queries in parrallel it is throwing "ORA-00054: resource busy and acquire with NOWAIT specified" Error at the line where i am 'Truncating' the table.

How could i solve this problem when i am executing queries in parallel?

Regds,
Srikanth

Report message to a moderator

Re: Reason for ORA-00054: resource busy and acquire with NOWAIT specified [message #146039 is a reply to message #146033]

Tue, 08 November 2005 05:08

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Don't truncate the table, perform a delete instead. TRUNCATE is a DDL command, locking the entire table and thrashing the HWM, making sure all data is inaccessible (read: lost forever) from that moment on.

MHE

[Updated on: Tue, 08 November 2005 05:08]

Report message to a moderator

Re: Reason for ORA-00054: resource busy and acquire with NOWAIT specified [message #146040 is a reply to message #146033]

Tue, 08 November 2005 05:09

dmitry.nikiforov
Messages: 723
Registered: March 2005

Senior Member

When you perform DML operations over the table data, Oracle
applies the specific lock (TM type) on the table definition in Oracle dictionary - it prevents the changes over the table which
can be made by DDL operations.

For example:

The first session:

Code: [Select all] [Show/ hide]

SQL> insert into t values(1,2);

1 row created.

The second session:

Code: [Select all] [Show/ hide]

SQL> truncate table t;
truncate table t
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


SQL> alter table t add (id number);
alter table t add (id number)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Table is locked:

Code: [Select all] [Show/ hide]

SQL> select object_name, o.object_id from user_objects o, v$locked_object l
  2  where o.object_id = l.object_id
  3  /

OBJECT_NAM  OBJECT_ID
---------- ----------
T               66272

Session which inserts data owns two locks:

Code: [Select all] [Show/ hide]

SQL> select ID1, ID2, type from v$lock where sid = (select sid from v$mystat where rownum = 1);

       ID1        ID2 TY
---------- ---------- --
    262160      24012 TX
     66272          0 TM

where ID2 is the object ID in the dictionary.

So you can't execute DDL statement over the table (TRUNCATE
is DDL operation).

Rgds.

Report message to a moderator

Re: Reason for ORA-00054: resource busy and acquire with NOWAIT specified [message #146051 is a reply to message #146039]

Tue, 08 November 2005 06:00

sreek_s
Messages: 45
Registered: May 2005
Location: Andaman Nikobar

Member

Hi Maaher,
If i use DELETE command instead on TRUNCATE command what happens in the following scenario.

1. Session-1 inserts some rows in my table.
2. Session-1 doing some process on the inserted rows.
3. Session-2 inserts some rows in the same table.
4. Session-1 DELETES the rows from table.

In this case will all the rows inserted by Session-2 also gets deleted ?
Or it will delete only rows inserted by Session-1 ?

Regds,
Srikanth

Report message to a moderator

Re: Reason for ORA-00054: resource busy and acquire with NOWAIT specified [message #146053 is a reply to message #146051]

Tue, 08 November 2005 06:16

Maaher
Messages: 7065
Registered: December 2001

Senior Member

All depends whether session 2 has committed his data. If it's uncommitted, session 1 won't see it and neither will the rows be deleted.

I get the feeling that you are trying to emulate a GLOBAL TEMPORARY TABLE. Am I right?

excerpt from the manuals

Oracle can create temporary tables to hold session-private data that exists only for the duration of a transaction or session.

The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table that can be transaction-specific or session-specific. For transaction-specific temporary tables, data exists for the duration of the transaction. For session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data. DML locks are not acquired on the data of the temporary tables. The LOCK statement has no effect on a temporary table, because each session has its own private data.

A TRUNCATE statement issued on a session-specific temporary table truncates data in its own session. It does not truncate the data of other sessions that are using the same table.

DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Data from the temporary table is automatically dropped in the case of session termination, either when the user logs off or when the session terminates abnormally such as during a session or instance failure.

MHE

Report message to a moderator

Re: Reason for ORA-00054: resource busy and acquire with NOWAIT specified [message #146055 is a reply to message #146051]

Tue, 08 November 2005 06:22

dmitry.nikiforov
Messages: 723
Registered: March 2005

Senior Member

>>In this case will all the rows inserted by Session-2 also gets
>>deleted ?

The answer depends on does Session-2 commit before Session-1
deletes rows or not.

If yes - Session-1 sees changes made by Session-2 and rows,
inserted by Session-2 will be deleted. If not - Session-1
doesn't see these changes.

Compare 2 cases:

1-th

Code: [Select all] [Show/ hide]

SQL> insert into t values(1,2);

1 row created.

2-th

Code: [Select all] [Show/ hide]

SQL> delete from t;

0 rows deleted.

No commit, no deletion.

With commit:

1-th

Code: [Select all] [Show/ hide]

SQL> insert into t values(1,2);

1 row created.

SQL> commit;

Commit complete.

2-th

Code: [Select all] [Show/ hide]

SQL> delete from t;

1 row deleted.

2-th see rows which have been inserted before if commit has been done.

Rgds.

Oops, sorry, Maarten, I made some echo...

[Updated on: Tue, 08 November 2005 06:23]

Report message to a moderator

Re: Reason for ORA-00054: resource busy and acquire with NOWAIT specified [message #146058 is a reply to message #146053]

Tue, 08 November 2005 06:28

sreek_s
Messages: 45
Registered: May 2005
Location: Andaman Nikobar

Member

Hi Maaher,
Thanks for your elaborated reply. Its very useful to me.
I have one more doubt here.
Can i create a session specific temporary table inside a pl/sql procedure? (Please help me out by giving some code snippet as i am new to this concept)

Currenly i am creating a static table and using it as a temporaty table which is causing all the probelms. I think if i use temporary table, then my problems will be solved.

Regds,
Srikanth

Report message to a moderator

Re: Reason for ORA-00054: resource busy and acquire with NOWAIT specified [message #146060 is a reply to message #146058]

Tue, 08 November 2005 06:43

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Please don't tell me you're creating and dropping objects on the fly! That's a very bad idea, IMO. You create the temporary table ONCE like you would create a normal table. You just don't have to clean up explicitly, you don't have to worry about users interfering with each other's data,....

If you want a table to exist only within your procedure, I'd consider a user defined SQL type or a PL/SQL table.

again, from these Fine Manuals

It is also possible to create a temporary table. The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table. You use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. The ON COMMIT keywords indicate if the data in the table is transaction-specific (the default) or session-specific:

ON COMMIT DELETE ROWS specifies that the temporary table is transaction specific and Oracle truncates the table (delete all rows) after each commit.
ON COMMIT PRESERVE ROWS specifies that the temporary table is session specific and Oracle truncates the table when you terminate the session.

This example creates a temporary table that is transaction specific:

Code: [Select all] [Show/ hide]

CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS;

MHE

[edit: nasty typo]

[Updated on: Tue, 08 November 2005 06:43]

Report message to a moderator

Re: Reason for ORA-00054: resource busy and acquire with NOWAIT specified [message #146170 is a reply to message #146060]

Wed, 09 November 2005 00:18

sreek_s
Messages: 45
Registered: May 2005
Location: Andaman Nikobar

Member

Hi Maaher,
Can i use NESTED TABLE along with a TEMPORARY TABLE?

I want to make the following table as TEMPORARY TABLE.

CREATE TABLE TEMP_DAV (
starttime DATE,
endtime DATE,
avalues IND_VALUE_ARRAY
) NESTED TABLE avalues STORE AS avalues_tab;

Regds,
Srikanth

Report message to a moderator

Re: Reason for ORA-00054: resource busy and acquire with NOWAIT specified [message #146401 is a reply to message #146170]

Thu, 10 November 2005 04:04

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Have you tried it?

MHE

Report message to a moderator

Re: Reason for ORA-00054: resource busy and acquire with NOWAIT specified [message #146557 is a reply to message #146401]

Fri, 11 November 2005 01:07

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Again, from the docs (note that this is the third time that I'm quoting from the documentation in the same thread):

Oracle9i SQL Reference Release 2 (9.2)Part Number A96540-02, Chapter 15: SQL Statements: CREATE SYNONYM to CREATE TRIGGER

Restrictions on Temporary Tables

Temporary tables cannot be partitioned, clustered, or index organized.
You cannot specify any foreign key constraints on temporary tables.
Temporary tables cannot contain columns of nested table or varray type.
You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, logging_clause, MONITORING or NOMONITORING, or LOB_index_clause.
Parallel DML and parallel queries are not supported for temporary tables. (Parallel hints are ignored. Specification of the parallel_clause returns an error.)
You cannot specify the segment_attributes_clause, nested_table_col_properties, or parallel_clause.
Distributed transactions are not supported for temporary tables.

A link to the documentation is in the sticky on top of the newbie,SQL Experts and PL/SQL experts forums.

MHE

[Updated on: Fri, 11 November 2005 01:08]

Report message to a moderator

Previous Topic:	Problem while fetching Bulk Collect INTO for RECORD Type
Next Topic:	Getting a Date component

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Mar 14 09:21:30 CDT 2025