3

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. The table previously had a large number of rows that were removed via TRUNCATE TABLE. There are no foreign keys or other referential constraints.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
TML
  • 1,374
  • 13
  • 21

1 Answers1

0

My case is similar, my talble has 3 million rows. It hangs about 2 hours and I abort it.

I found the reason is some other sessions lock this table. After I killed the sessions, It completes adding column in miliseconds.

I don't know why Oracle don't throw "resource busy" when adding column.

Neo Pham
  • 101