| 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
|
|
|
|
|
|
| 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:
SQL> insert into t values(1,2);
1 row created.
The second session:
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:
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:
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.
|
|
|
|
| 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
|
|
|
|
|
|
| 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
SQL> insert into t values(1,2);
1 row created.
2-th
SQL> delete from t;
0 rows deleted.
No commit, no deletion.
With commit:
1-th
SQL> insert into t values(1,2);
1 row created.
SQL> commit;
Commit complete.
2-th
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
|
|
|
|
| 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:
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 #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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
