Q
Problem solve Get help with specific problems with your technologies, process and projects.

ORA-00054 error with shell scripts

I have written a shell script that calls a SQL. This SQL in turn truncates a temp that belongs only to that program. These shell scripts run in batches that are scheduled. At times I get the error "ORA-00054: resource busy and acquire with NOWAIT specified."

I have written a shell script that calls a SQL. This SQL in turn truncates a temp that belongs only to that program. These shell scripts run in batches that are scheduled. At times I get the error "ORA-00054: resource busy and acquire with NOWAIT specified." I get this error when I try to truncate the temp table. Since the temp belongs only to this particular program there is no chance of some other program locking this table. Since these are scheduled jobs, I am not able to check in the V$SESSION table also. This error does not occur every day but occurs quite often. Can you please tell me the actual reason for this problem?

The TRUNCATE TABLE command is a DDL command. Unlike DML commands (INSERT, UPDATE, DELETE), a DDL command will not wait for a lock to be released. Instead, the DDL command will fail and the ORA-00054 error will be raised.

Temporary tables do not belong to a program. If your TRUNCATE TABLE command is resulting in the ORA-00054 error, then some lock, even a shared lock, is causing this problem. It could be that a previous execution of your code has not released the locks, or that some other program or code has the lock. The only way to know for sure is to query V$LOCK at the time this happens. Querying V$LOCK, and subsequently V$SESSION, will show you who has locked your object. On my web site, I have a white paper titled "Solving User Problems." This paper shows you how to query V$LOCK for the information you seek.

This was last published in September 2005

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close