I am running a query against a partitioned table and at the same time, another user is trying to truncate that partition. What will happen in this scenerio? The reason I am asking this question is that while trying to truncate a partition of a table, the process was just hanging without doing any activity, and I not sure if I was truncating this table while another user was selecting data from it.
When a user issues a query, a DML lock (TM) is placed on that table for the duration of that query. Normally, this is done and over with really quick. So your TRUNCATE command should proceed right after. If you suspect a locking issue, query V$LOCK to see if your session is locking or not. The LMODE column signifies the lock mode currently held. The REQUEST signifies the request being made, but not yet granted. Your hung session should have a pending request. If not, you have other issues which are holding you up. V$SESSION_WAIT will tell you what that session is waiting on.
For More Information
- What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.