Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Using Oracle Database 10g Enterprise Edition Release, the operations below have completely stumped me. The first locks up (table has DDL lock, finally killed query after 20+ minute wait). The second does not.

This hangs:

ALTER TABLE customer ADD primary_seg varchar2(9) NOT NULL;

This does not:

ALTER TABLE customer ADD primary_seg varchar2(9); 
ALTER TABLE customer MODIFY primary_seg NOT NULL;

There are no records in the table, there are no other users than myself on the instance, and I have no other queries executing which might cause a lock.

share|improve this question
Were there previously rows in the table? The high water mark might be very high. – Phil Sep 24 '12 at 21:04
how about related tables, foreign keys? – ik_zelf Sep 25 '12 at 5:29
@Phil: here were previously a large number of rows in the table, but these were removed by a "TRUNCATE TABLE" (which should have reset the HWM, unless I'm mistaken). – TML Sep 25 '12 at 6:47
@ik_zelf: No FKs or other referential constraints to any other tables – TML Sep 25 '12 at 6:48
Can you reproduce your finding reliably? – Vincent Malgrat Sep 25 '12 at 7:10

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.