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.