Error message

Does any one one has idea what this message means ?
I have a test database, I have killed all sessions in this region and right now there are no connections to it.
But still I keep getting this error message:
SQLWKS> truncate table temp
2>
truncate table temp
*
ORA-00054: resource busy and acquire with NOWAIT specified

I even tried to drop this table I can't I get the same error message.
I can do a select * from temp.

What do I do now ? Do i have to stop and start the oracle server ?

thanks

Even if you kill the session oracle takes its own time to release the lock on the objects. You can run this query to find out whether an object is locked or not.

select object_name,os_user_name from v$locked_object a,dba_objects b
where a.object_id = b.object_id

If you have kill the session & lock is still there you have to wait until it gets released, try killing it again & again from the same session . The last solutions is that you will have to restart the database. This is quickest method but has its own limitation.

OR, before using truncate, try taking that object in exclusive mode, if allows to do u can go ahead with truncate, if not , find out who has locked that object.

Take Care
GP

i HAVE FACED THIS PROBLEM MANY TIMES,

Here is the reason, The Table that you are trying to access is locked for some processing, and there may be any user who is accessing or there could be any procedure running or a job running that's accessing this table, so it will not be dropped unless the locks are released.

Once locks are released, There should not be any trouble.

Thanks...

Hi,
Though u have killed all the sessions,PMON will come in to the picture.PMON will rollback all the active transactions and release all the locks and resources the users were holding.
U have to wait for some time in order to truncate the table or use the following command
SQL>truncate table temp NOWAIT;

What NOWAIT will do is that it will not wait indefinetly for the table to be dropped.It will immediately give u an error message.
The error which was displayed to u was because some user was using the temp table,and since u killed the user session,PMON is cleaning all the locks and resources held by the user as well as it rollbacks all the active transactions which were occuring on the table.
If u get the same error after some time ,u can restart ur database.

IF any problems,please be free to ask to me at [email protected]


regards,
Rohit Nirkhe,Oracle DBA,OCP

Quote:

---

Originally posted by sonaliak
Does any one one has idea what this message means ?
I have a test database, I have killed all sessions in this region and right now there are no connections to it.
But still I keep getting this error message:
SQLWKS> truncate table temp
2>
truncate table temp
*
ORA-00054: resource busy and acquire with NOWAIT specified

I even tried to drop this table I can't I get the same error message.
I can do a select * from temp.

What do I do now ? Do i have to stop and start the oracle server ?

thanks

---

Hi sonaliak

I have ren ino this many times! and i can only agree with Oracledba8 and with rohitsn. to put it in a nutshell, when a user is doing an update (for example) on a table and no commit/rolback follows, you get this error when you try to truncate! so, if it is posible to know how/what was the process that actually locked the table, you are better of then gust kill the process that os locking the table. you can join v$session to v$locked_object and find the user/osuser/terminal that created the lock in the first place.

any way, i am against killing a process without looking into it. see what have created the problem, so it will not happen again.

Oh, Ya, I know before killing one should check what locked it and thanks, I had done that. Even without doing that I knew what did it. In my original post I did mention that this is my test database and I was testing something !!!!

Thanks to all for their suggestions. It did release locks on that table after sometime.
Few more things as rohitsn suggested I had tried to do:
SQL>truncate table temp NOWAIT;
But kept getting same error message.

Also, this query returned 0 records and still the table was locked !!
select object_name,os_user_name from v$locked_object a,dba_objects b
where a.object_id = b.object_id ;

One thing which I don't understand is when I did select * from v$session all the sessions accessing this db showed that they were killed. I was logged in as System/manager.
Also I am the only user on this db. What kept it locked even when all sessions were killed ???

Thanks again

There is a process that has a lock on that table. I recommend that you login to svrmgr and kill all active sessions that are not oracle processes. You can also use this query to see what sql is manipulating the table.

select sid, serial#, schemaname, osuser, serial#, sql_text from v$session, v$sql where v$session.sql_address = v$sql.address and v$session.status = 'ACTIVE';

Quote:

---

Originally posted by sonaliak
Does any one one has idea what this message means ?
I have a test database, I have killed all sessions in this region and right now there are no connections to it.
But still I keep getting this error message:
SQLWKS> truncate table temp
2>
truncate table temp
*
ORA-00054: resource busy and acquire with NOWAIT specified

I even tried to drop this table I can't I get the same error message.
I can do a select * from temp.

What do I do now ? Do i have to stop and start the oracle server ?

thanks

---