You could use v$session and v$lock and others but I suggest you google how to find that session and then how to kill it.
If you have access to a SQL admin system use it to find the offending session. Or you could commit from that session once you know who has the session open. If you are developing a new system, it is likely that you or someone in your team issues the update statement and you could kill the session without much consequence. This happens when a session other than the one used to alter a table is holding a lock likely because of a DML (update/delete/insert). I have done this in 10g before as well in the past. It is in a database that is constantly inserting at a rate of several hundred inserts/second. I use this in code that runs from a job to drop partitions. This frees the locks.ĭML statements will 'wait' or as developers call it 'hang' while the table is locked. However, your session has aquired the lock. Your DDL will then run a lock with the NO WAIT. so there may be several sessions ahead of you. until the session with the open transaction, commits. LOCK TABLE 'TABLE NAME' - you will 'wait' (developers call this hanging).so yo can just lock the partition subpartition. If you are doing splitting/dropping partitions oracle just locks the partition. Issue your own lock before the DDL and leave out the 'NO WAIT'. So if another session has an open transaction you get an error.
You will see that before any DDL operation Oracle does the following: If you run a 10046 trace on your session (google this. There is a very easy work around for this problem.