Take the 2-minute tour ×
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 10.2.0.1.0, 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
show 2 more comments

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.