Alter table add column query blocking a table or taking so much time

Question

I'm trying to add a new column to an existing table but it takes so much time or the query is blocking the table, I'm really confused about what's happening exactly.

ALTER TABLE MYTABLE1 ADD MYEXPORT9 NUMBER(38,10) DEFAULT 0;

This simple query doesn't finish yet and using the following query to see if the query is blocked or blocking I found it. Query:

SELECT
decode(L.TYPE,'TM','TABLE','TX','Record(s)') TYPE_LOCK,
decode(L.REQUEST,0,'NO','YES') WAIT,
S.OSUSER OSUSER_LOCKER,
S.PROCESS PROCESS_LOCKER,
S.USERNAME DBUSER_LOCKER,
O.OBJECT_NAME OBJECT_NAME,
O.OBJECT_TYPE OBJECT_TYPE,
s.logon_time,
p.spid,
concat(' ',s.PROGRAM) PROGRAM,
O.OWNER OWNER
FROM v$lock l,dba_objects o,v$session s, v$process p
WHERE l.ID1 = o.OBJECT_ID
AND s.SID =l.SID
AND p.addr=s.paddr
AND l.TYPE IN ('TM','TX');

And looking for in the v$session table I see the following line:

29  ANC oracle  46465270    clay925p6       sqlplus@myserveur (TNS V1-V3) SQL*Plus db file sequential read  file#   5   0000000000000005    block#  1701088 000000000019F4E0    blocks  1   0000000000000001    1740759767  8   User I/O    0   0   WAITING

And it's so annoying to see the word WAITING at the end in the column named STATE.

The other option, add a column operation takes so much time, could be possible according to the number of rows of the tables, there are more than 7 million of entries. But from now the query takes more than 3 hours.

What am I doing wrong? What's happening?

Answer 1

The table will need to be physically rewritten on disc to include the extra column. For this to happen the table has to be exclusively locked while this is happening. If the applications running on this database are not shutdown then you may be waiting sometime before this exclusive lock on the table can be given.

Given you mentioned that this table has several million rows the alter table statement is going to take quite some time (this also depends how much data is stored in each record. You will in all likelyhood need to stop all access to this table during this time (well it will be locked anyway).

Sorry if the answer is a little vague you did not really give many details about the size of the table and how much data is currently stored there.