| ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #659467] |
Wed, 18 January 2017 08:28  |
jaysorcale
Messages: 5
Registered: February 2008
|
Junior Member |
|
|
Hi All,
Step 1;
DROP TABLE XX_RA_CUSTOMER_TRX_ALL_1
Step 2: Create custom table XX_RA_CUSTOMER_TRX_ALL_1
EXECUTE IMMEDIATE 'CREATE TABLE XX_RA_CUSTOMER_TRX_ALL_1
AS
SELECT CUSTOMER_TRX_ID,TRX_NUMBER,BILL_TO_CUSTOMER_ID,ORG_ID, CREATION_DATE FROM RA_CUSTOMER_TRX_ALL
WHERE ORG_ID = <Condition>'
Step 3: Create Index on the above created Table
EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX XX_RA_CUST_TRX_ALL_U21
ON XX_RA_CUSTOMER_TRX_ALL_1(CUSTOMER_TRX_ID)';
Problem:
I get the below error and this does not occur always.
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.
Any help on the above will be of great help.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #659499 is a reply to message #659486] |
Thu, 19 January 2017 07:34  |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
If you are connected as a user with access to the sys views simply run the following command and it will show all the users locking your table
SELECT C.Owner,
C.Object_name,
C.Object_type,
B.Sid,
B.Serial#,
B.Status,
B.Osuser,
B.Machine
FROM V$locked_object A, V$session B, Dba_objects C
WHERE B.Sid = A.Session_id
AND A.Object_id = C.Object_id
AND A.Object_id = (SELECT Object_id
FROM Dba_objects
WHERE Owner = 'MY_SCHEMA' AND Object_name = 'MY_TABLE'); |
|
|
|
|
|
| Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #659502 is a reply to message #659482] |
Thu, 19 January 2017 07:46 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
joy_division wrote on Wed, 18 January 2017 18:47
<snip>
Wow, I never knew of this?
John, how different is this from the 11gR2 alter session set ddl_lock_timeout command?
Quiesce will block other sessions from doing anything, so that you can get your lock. The ddl_lock_tineout is kind of the reverse: you give up trying to get your lock, because you are blocked by other sessions. The real problem with using the tineout is that is if A gets a lock, then B gets a lock, then A releases his lock, then C gets a lock, then B releases his lock, and so on, your session will wait forever because there is always a TM lock on the table. With the quiesce, that doesn't happen. I would view them as complementary: use them together. I hadn't thought of that.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
